Power Query -- Csv.Document weirdness

[kkardynalski]

A coworker alerted me to a problem he was having combining some csv files. I had a look and found the problem but my solution is clunky so I am asking this question to get a more elegant solution.

First the problem:

note: I am not using parameters in these queries, the <filepath> and <filename> are actual text of the respective folder and file(s).

I have four csv files from an internet source, loaded to a network drive. When I open any and all of them with this code they display as expected, showing me 92 columns with headers in the first three rows and data starting on row 4:

Source = Csv.Document(File.Contents(" <filepath\filename.CSV> "),[Delimiter=",", Columns=92, Encoding=1252, QuoteStyle=QuoteStyle.None])

However, opening from folder I get different results: only one column with text in the first three rows and Errors for the rest with the message: "DataFormat Error: there were more columns in the result than expected. Details: Count = 1"

The code for that is:

Source = Folder.Files(" <filepath> "),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Csv.Document([Content])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1"}, {"Custom.Column1"})



If I run this query on another folder with different csv files from the same internet source, I get exactly the results I expected.


Then the investigation (really just to verify what I already suspected):

I opened the csv files in Notepad++. The first three lines in the ones that work have "heading,,,,,,," etc. and the ones that don't work have just " heading " .

Now two questions:

1. Obviously I can create a query for each file in the directory and then merge them if needed. But given that these files are downloaded from an internet site, and some files from that site work and some don't, how can I write a query that gives me all the data without a lot of headaches and error checking?

2. Why do the files with the single column header rows open perfectly when the query is "From File"?

Thanks for your suggestions!

Kathy

 

[ImkeF]

It looks as if you're missing out the parameters for the Csv.Document: [Delimiter=",", Columns=92, Encoding=1252, QuoteStyle=QuoteStyle.None])

 

[kkardynalski]

Imke,

Thank you. I had tried that earlier and had trouble, but now it works. Could be I typed something wrong before. So I went in and worked with this folder again, trying to understand what is happening.

All of the files, when queried separately show the parameters above. How do I know what these parameters are when querying "From Folder"? It seems counterproductive to have to run a separate query on each file to find the parameters.

I tried adding a column for Table.ColumnCount(Csv.Document([Content])). This shows 92 columns in half the files and 1 column in the other half. Is there a way to get the max count of this column to feed into the parameter?

And why?!?! do the files that show one column come in with 92 columns when queried "From Csv", but one column with errors even if they are the only file queried "From Folder"??? There has to be some logic here, right?

Kathy

 

[ImkeF]

You can omit the number of columns, but not the other parameters here.