Forum Discussion
Imported CSV only read the first line
Hello everyone,
I'm currently working on importing EPSS data into my PowerBI dashboard. The data, which is in CSV format within a gzip file from a web source, needs to be decompressed before loading. I've encountered an issue where, during the import process, only the first line of the CSV file is read. This line contains a comment with metadata about the date and model version, and the rest of the data below it is ignored.
However, when I manually decompress the file first and then load it as a file, the entire document reads successfully.
Please help. Thank you.
After conducting a thorough debugging process, it has been determined that the error is not caused by the decompression process in Power Query. It seems likely that the issue may stem from a different format or setting used during the initial compression with gzip, leading to an EOF reading error. However, re-compressing and decompressing the CSV file with 7zip did not result in any issues, whether the file was decompress using file/web.content.
I've switched to using the API method, thank you for your assistance.
7 Replies
Looking purely at your Power Query code, you are not using the same method in both queries.
In the query with the decompress, you are using the function Csv.Document() on its own. In the method without the decompress you are using Csv.Document() that calls File.Contents(). Have you tried adding File.Contents around the Source entry on that #"Imported CSV" line of code?
i.e.
Csv.Document(File.Contents(Source),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None])Hi Ross,
I'm reaching out to extract the CSV from the web content, which is why I used the first query. Here's how it looks:
let Source = Binary.Decompress(Web.Contents("https://epss.cyentia.com/epss_scores-current.csv.gz"), Compression.GZip), #"Imported CSV" = Csv.Document(Source, [Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.Csv]) in #"Imported CSV"The purpose of the second query, which calls a local file, is to demonstrate the difference in results between using a file and web content, even though it's the same file. However, following your suggestion led to an expression error: We cannot convert a value of type Binary to type Text.
Thanks for the suggestion.
Yeah you are right. The File.Contents needs a path, not a binary to function. Its output is binary.
Binary.Decompress accepts a Nullable Binary and returns a Nullable Binary.
My next thought would be to try this function:
Lines.FromBinary - PowerQuery M | Microsoft Learn