Forum Discussion
csv file column count parameter issue with tab / semicolon separator
Hi Goodkat ,
The question about your files and when you remove the column numbers is related with the first row of your file.
In the TAB one you have the 2025 being in the end of the line with a tabe for each of the columns. Not sure if it's visible on this image but you can check the indentation on the first line:
So it does not matter if you have the number of columns or not because there are the correct number of separators in the first line. If you delete all the tabs in the first row you will see that the final result will be a single column:
When you get to the semicolon this is not what is happening:
You can see that there is only a semicolon in the first row so it splits by 2 when you remove the column number.
In both cases if you want to make sure you have the correct number of columns you must:
- Connect to the CSV
- Select a separator that is not on the file
- Remove the top 2 rows
- Split columns by delimiter
- Promote headers
Be aware that this option will also hard code the number of columns because of the column names that will be hard coded into that specific function. You can try and write some specific function to get a dynamic number of columns based on the number of semicolon or tab or whatever other separator.
When Power Query uses Csv.Document, it infers the structure based on delimiter and encoding. If it detects the delimiter incorrectly or encounters encoding issues (like mismatched quotes or BOM), it may parse fewer columns than exist.
In your case, the semicolon-delimited file likely contains quoted fields, and Power Query is not recognizing the delimiter properly because of how Csv.Document is being called.
You can explicitly define the delimiter, encoding, and quote style using a custom Csv.Document call without hardcoding the columns.
Here’s a clean, dynamic solution:
let
Source = File.Contents("C:\YourPath\semicolon_file.csv"),
Csv = Csv.Document(Source,
[Delimiter=";", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
PromoteHeaders = Table.PromoteHeaders(Csv, [IgnoreErrors=true])
in
PromoteHeaders
Dear Omid,
thank you for your reply. But in your clean dynamic solution the columns are hardcoded via 'Columns=11' or do I oversee something in that regard?
So it does not solve the issue.
Best regards, Andreas