I’m importing a .csv from a HTTP request and parse it through a Text parser to remove the first 2 lines of the .csv.
I’m getting this error from the parse csv modul: Number of columns on line 1 does not match header.
The output is also just 1 Bundle and should be way more, since the headers are 142 columns alone.
I think it must be how the data is formated, but cant get a grip on it.
Any help is welcome.
Thanks
Hi @ViolentNomad78 ,
Could you add more information:
- an example of you file
- some screenshots
- any information about the error
It will be useful for a deeper investigation.
2 Likes
Why are you using parse text module. I belive yoh need to use parse csv module.
Confirmed solution can be provided by checking your scenario screenshot or csv results.
Princy Jain
Make consultant,
For consulting support: Follow up Automated
2 Likes
@ViolentNomad78 Post a sample of the file that you are trying to parse, make sure it’s from the original source of what creates the CSV. It will help folks determine if maybe it is a carriage return issue like I had in my file.
2 Likes
Thanks for all the replies.
Here is sample copied from excel:
| Market:;XFRA | |||
|---|---|---|---|
| Date Last Update:;22.04.2024 | |||
| Product Status;Instrument Status;Instrument;ISIN;Product ID;Instrument ID;WKN;Mnemonic;MIC Code;CCP eligible Code;Trading Model Type;Product Assignment Group;Product Assignment Group Description;Designated Sponsor Member ID;Designated Sponsor;Price Range Value;Price Range Percentage;Minimum Quote Size;Instrument Type;Tick Size 1;Upper Price Limit Max;Tick Size 2;Upper Price Limit 2;Tick Size 3;Upper Price Limit 3;Tick Size 4;Upper Price Limit 4;Tick Size 5;Upper Price Limit 5;Tick Size 6;Upper Price Limit 6;Tick Size 7;Upper Price Limit 7;Tick Size 8;Upper Price Limit 8;Tick Size 9;Upper Price Limit 9;Tick Size 10;Upper Price Limit 10;Tick Size 11;Upper Price Limit 11;Tick Size 12;Upper Price Limit 12;Tick Size 13;Upper Price Limit 13;Tick Size 14;Upper Price Limit 14;Tick Size 15;Upper Price Limit 15;Tick Size 16;Upper Price Limit 16;Tick Size 17;Upper Price Limit 17;Tick Size 18;Upper Price Limit 18;Tick Size 19;Upper Price Limit 19;Tick Size 20;Upper Price Limit 20;Number of Decimal Digits;Unit of Quotation;Market Segment;Market Segment Supplement;Clearing Location;Primary Market MIC Code;Reporting Market;Settlement Period;Settlement Currency;Closed Book Indicator;Market Imbalance Indicator;CUM/EX Indicator;Minimum Iceberg Total Volume;Minimum Iceberg Display Volume;EMDI Incremental A - Unnetted;EMDI Incremental A - Unnetted Port;EMDI Incremental B - Unnetted;EMDI Incremental B - Unnetted Port;EMDI Snapshot A - Unnetted;EMDI Snapshot A - Unnetted Port;EMDI Snapshot B - Unnetted;EMDI Snapshot B - Unnetted Port;EMDI Market Depth - Unnetted;EMDI Snapshot Recovery Time Interval - Unnetted;MDI Address A - Netted;MDI Port A - Netted;MDI Address B - Netted;MDI Port B - Netted;MDI Market Depth - Netted;MDI Market Depth Time Interval - Netted;MDI Recovery Time Interval - Netted;EOBI Incremental A;EOBI Incremental Port A;EOBI Incremental B;EOBI Incremental Port B;EOBI Snapshot A;EOBI Snapshot Port A;EOBI Snapshot B;EOBI Snapshot Port B;Market Maker Member ID;Market Maker;Regulatory Liquid Instrument;Pre-trade LIS Value;Partition ID;Multi CCP-eligible;Tick Size Band;Security Sub Type;Issue Date;Underlying;Maturity Date;Flat Indicator;Coupon Rate;Previous Coupon Payment Date;Next Coupon Payment Date;Pool Factor;Indexation Coefficient;Accrued Interest Calculation Method;Country Of Issue;Minimum Tradable Unit;In-Subscription;Strike Price;Minimum Order Quantity;Off-Book Reporting Market;Instrument Auction Type;Specialist Member ID;Specialist;Liquidity Provider User Group;Specialist User Group;Quoting Period Start;Quoting Period End;Currency;Warrant Type;First Trading Date;Last Trading Date;Deposit Type;Single Sided Quote Support;Liquidity Class;Cover Indicator;VolatilityCorridorOpeningAuction;VolatilityCorridorIntradayAuction;VolatilityCorridorClosingAuction;VolatilityCorridorContinuous;DisableOnBookTrading | |||
| Active;Active;RAIF.BK INT. 18/25FLR MTN;AT000B014345;13;10583;000A1915H;;XFRA;N;ContinuousAuctionSpecialist;PAG_BON;BOERSE FRANKFURT BONDS;;;;;1;BOND;0.001;9999999999.9999;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;5;Percent;085;;BOFRA;WBAH;FRAB;2;EUR;1;0;;;;224.0.160.109;56001;224.0.162.109;56001;224.0.160.108;56000;224.0.162.108;56000;1;500000;224.0.161.46;56000;224.0.163.46;56000;1;3500;300000;224.0.160.237;56001;224.0.162.237;56001;224.0.160.236;56000;224.0.162.236;56000;BALFR;BAADER BANK AG;N;1500000.00000000;2;N;PST_BF2;;2018-06-25;;2025-06-25;1;2.5;2024-03-25;2024-06-25;1.000000000;1.0000000;6;;100000;N;;100000;FRAV;SingleAuction;BALFR;BAADER BANK AG;QPF;VRR;11:00:00;13:00:00;EUR;;2018-07-23;2025-06-20;GIROSAMMELVERWAHRUNG;SSQ_SUPPORTED;;;;;;;N | |||
| Active;Active;UNICR.BK AUS. 15-25 MTN;AT000B049572;13;10797;000A1ZXF2;E7AL;XFRA;N;ContinuousAuctionSpecialist;PAG_BON;BOERSE FRANKFURT BONDS;;;;;1;BOND;0.001;9999999999.9999;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;5;Percent;085;;BOFRA;WBAH;FRAB;2;EUR;1;0;;;;224.0.160.109;56001;224.0.162.109;56001;224.0.160.108;56000;224.0.162.108;56000;1;500000;224.0.161.46;56000;224.0.163.46;56000;1;3500;300000;224.0.160.237;56001;224.0.162.237;56001;224.0.160.236;56000;224.0.162.236;56000;BALFR;BAADER BANK AG;N;250000.00000000;2;N;PST_BF2;;2015-02-25;;2025-02-25;1;0.75;2024-02-25;2025-02-25;1.000000000;1.0000000;9;;100000;N;;100000;FRAV;Default;BALFR;BAADER BANK AG;QPF;VRR;;;EUR;;2015-02-20;2025-02-20;GIROSAMMELVERWAHRUNG;SSQ_SUPPORTED;;;;;;;N | |||
| Active;Active;UNICR.BK AU. 19/26 MTN;AT000B049739;13;10805;000A2RWB7;;XFRA;N;ContinuousAuctionSpecialist;PAG_BON;BOERSE FRANKFURT BONDS;;;;;1;BOND;0.001;9999999999.9999;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;5;Percent;085;;BOFRA;WBAH;FRAB;2;EUR;1;0;;;;224.0.160.109;56001;224.0.162.109;56001;224.0.160.108;56000;224.0.162.108;56000;1;500000;224.0.161.46;56000;224.0.163.46;56000;1;3500;300000;224.0.160.237;56001;224.0.162.237;56001;224.0.160.236;56000;224.0.162.236;56000;BALFR;BAADER BANK AG;N;250000.00000000;2;N;PST_BF2;;2019-01-16;;2026-01-16;1;0.625;2024-01-16;2025-01-16;1.000000000;1.0000000;9;;100000;N;;100000;FRAV;Default;BALFR;BAADER BANK AG;QPF;VRR;;;EUR;;2019-01-11;2026-01-13;GIROSAMMELVERWAHRUNG;SSQ_SUPPORTED;;;;;;;N |
I’m using a Text parser to get ride of the first two lines in the csv. Maybe there is another solution.
This is a screenshot of the csv error.
Thanks Fred_S.
Your post was the one that gave me a Idee in the direction the error may come from.
1 Like
So it looks like the carriage return is CRLF. Thats what Notepad++ is telling me. Make should handle this right?
Any chance you can share the link of the CSV file so that we can try importing it ourselves?
good morning,
here you go.
It is a big file.
Welcome to the Make community!
Yes, that is possible. You’ll need a minimum of two modules:
You were likely incorrectly removing the first two lines.
Here is the new regex
^.+[\n\r]+.+[\n\r]+
Proof: https://regex101.com/r/qJ9x2C
Give it a go and let us know if you have any issues!
2 Likes
Module Export
You can copy and paste this module export into your scenario. This will paste the modules shown in my screenshots above.
-
Copy the JSON code below by clicking the copy button when you mouseover the top-right of the code block
-
Enter your scenario editor. Press ESC to close any dialogs. Press CTRLV (paste keyboard shortcut for Windows) to paste directly in the canvas.
-
Click on each imported module and save it for validation. You may be prompted to remap some variables and connections.
JSON
{
"subflows": [
{
"flow": [
{
"id": 28,
"module": "http:ActionGetFile",
"version": 3,
"parameters": {
"handleErrors": true
},
"mapper": {
"url": "https://www.xetra.com/resource/blob/2289108/84852f9daceeda044e99d09596081d85/data/t7-xfra-BF-allTradableInstruments.csv",
"serializeUrl": false,
"method": "get",
"shareCookies": false
},
"metadata": {
"designer": {
"x": -935,
"y": -857
},
"restore": {},
"parameters": [
{
"name": "handleErrors",
"type": "boolean",
"label": "Evaluate all states as errors (except for 2xx and 3xx )",
"required": true
}
],
"expect": [
{
"name": "url",
"type": "url",
"label": "URL",
"required": true
},
{
"name": "serializeUrl",
"type": "boolean",
"label": "Serialize URL",
"required": true
},
{
"name": "method",
"type": "hidden",
"label": "Method"
},
{
"name": "shareCookies",
"type": "boolean",
"label": "Share cookies with other HTTP modules",
"required": true
}
]
}
},
{
"id": 29,
"module": "csv:ParseCSV",
"version": 1,
"parameters": {
"colCount": 142,
"csvContainsHeaders": true,
"delimiterType": "other",
"relax": false,
"delimiter": ";"
},
"mapper": {
"csv": "{{replace(toString(28.data); \"/^.+[\\n\\r]+.+[\\n\\r]+/\"; emptystring)}}"
},
"metadata": {
"designer": {
"x": -692,
"y": -858,
"messages": [
{
"category": "last",
"severity": "warning",
"message": "A transformer should not be the last module in the route."
}
]
},
"restore": {
"parameters": {
"delimiterType": {
"label": "Other"
}
}
},
"parameters": [
{
"name": "colCount",
"type": "number",
"label": "Number of columns",
"required": true
},
{
"name": "csvContainsHeaders",
"type": "boolean",
"label": "CSV contains headers",
"required": true
},
{
"name": "delimiterType",
"type": "select",
"label": "Delimiter",
"required": true,
"validate": {
"enum": [
",",
"\t",
"other"
]
}
},
{
"name": "relax",
"type": "boolean",
"label": "Preserve quotes inside unquoted field",
"required": true
},
{
"name": "delimiter",
"type": "text",
"label": "Delimiter character",
"validate": {
"max": 1,
"min": 1
},
"required": true
}
],
"expect": [
{
"name": "csv",
"type": "text",
"label": "CSV",
"required": true
}
]
}
}
]
}
],
"metadata": {
"version": 1
}
}
3 Likes
@samliew works like a charm. Thanks alot. Greatly appreciated
@samliew maybe you can push me in the right direction here. I want to update a google sheet with the csv data, but only want to get certain rows matching a criteria. Do i have to place a module before the sheets update row module or can i do this within using the mapping of the columns with a filter? Thanks
@samliew sorry for all the questions but it just came to mind that the file I pull from the link I send you gets generated each day, which also means that the link most likely be a different one each day right? Is there a workaround to make the Link dynamic? So I dont have to manually copy the new link each week when I want to import the data. Thanks again for all the help
No problem, glad I could help!
1. If you have a new question in the future, please start a new thread. This makes it easier for others with the same problem to search for the answers to specific questions, and you are more likely to receive help since newer questions are monitored closely.
2. The Make Community guidelines encourages users to try to mark helpful replies as solutions to help keep the Community organized.
This marks the topic as solved, so that:
others can save time when catching up with the latest activity here, and
- allows others to quickly jump to the solution if they come across the same problem
To do this, simply click the checkbox at the bottom of the post that answers your question:
3. Don’t forget to like and bookmark this topic so you can get back to it easily in future!
You can fetch the web page and extract the new URL. Where can the URL be found? Please include this information in your new question.
1 Like
@samliew here is the new thread.