Forum Discussion
The Csv.Document parameter 'Columns' is invalid.
Hi everyone,
I am having the following issue on a couple reports I have built. So far the only options on the community forums is to either change from a personal gateway to an enterprise one (This isnt an option for me), the other is to change the CSV to a txt file. I have done this where I can however the report I am having issues with pulls CSV files from the new get data from a folder option. There doesnt appear to be a function within this to change the type to TXT.
Has anyone got a fix for this?
Thanks,
My IT department managed to fix the issue. We went through the advanced editor in query mode and manually changed the coding to get it to work.
This is how we did it:
This line of text is incorrect:
Imported CSV" = Csv.Document(#"Combined Binaries",[Delimiter=","columns=10, Encoding=1252]),
The columns=10 was counting the columns in the document (10 is correct) but something was going wrong. We deleted this text but lef tin the commas and republished the file. It then failed again due to this error:
The Csv.Document parameter 'QuoteStyle' is invalid. From memory the advanced editor text looked like this:
Imported CSV" = Csv.Document(#"Combined Binaries",[Delimiter=","columns=10, Encoding=1252]), QuoteStyle=QuoteStyle""
We deleted the QuoteStyle text and republished. The dashboard now refreshes using the automated refresh feature in Pro.
Thanks,
Giles
18 Replies
We found a solution by modifying the Csv.Document step as explained below. We encountered this same error in one of our solutions built in Power Query when a client updgraded from Excel 2013 to Excel 2016. The query was importing a CSV and was returning this error on the step that uses Csv.Document. It was and is still working correctly in Excel 2013, but was not working in Excel 2016.
The code that was returning the error was:
Csv.Document([Content],[Delimiter=",", Columns=40, Encoding=1252, QuoteStyle=QuoteStyle.None])We changed it to the following:
Csv.Document([Content],[Delimiter=",", Column=40, Encoding=1252])There were 2 changes, highlighted in red. Columns becomes Column (i.e. singular). This then no longer returns the parameter 'Columns' is invalid error message, but then returns an error message related to QuoteStyle. We then removed the QuoteStyle part. This then worked in Excel 2016. This code also works in Excel 2013 and in Power BI Desktop.
Hi Lyndon,
Thank you for your return !
How the quotations of a .CSV can be ignore as the "QuoteStyle=QuoteStyle.None" no longer works ?
For instance if I have the following csv file :
First name, gender, address
John, male, "1 Fox Street"
And I use : Csv.Document([Content],[Delimiter=",", Column=3, Encoding=1252])
The result looks like:
First name | gender | adress |
John, male, "1 Fox Street"| | |
Without the quotation
First name, gender, address
John, male, 1 Fox Street
The result looks like:
First name | gender | adress |
John |male |1 Fox Street|
The quotations affect the result and there is no way to avoid them?
Best,
Kelian
Can you post a sample of your CSV file that you are having trouble with, particular the column names and a row or two of data?
Here you go:
Consignment Docket Date Loaded Depot Wagon Product Gross Tare Net Overcarry RA17221 RA00318461 12/04/2016 5:04 010Y THFY015-N 13068 66.8 20.65 46.15 1.65 RA17221 RA00318462 12/04/2016 5:03 010Y THFY012-J 13068 66.9 20.5 46.4 1.35 RA17221 RA00318463 12/04/2016 5:09 010Y THFY002 13068 67 19.65 47.35 0 All the CSV files are the same, they have roughly 20 rows but the columns will never change. I have done a couple formatting changes to the files such as changing the date loaded column to be split into two columns date and time. The Wagon column is used to created a custom column which removes all the characters apart from the numbers.
Thanks,
Giles
GilesWalker - Hmmm, I can't replicate it. I took your data and copied and pasted it into a file, wagons_tab.csv and then also created a true comma-separated file, wagons.csv, removed the tabs and replaced with commas. Both imported no problem using CSV as data source. Wasn't expecting that.
Um, what version of Power BI Desktop are you running?
For anyone else who might come across this, I suspect that at least I ran into this issue because I created my query in a newer version of Power Query (Excel M365) and tried to run it in an older version of Power Query (Excel 2016). There was also at least one other function I had to remove parameters from, including Promote Headers.
