Forum Discussion
CSV web query returns one huge row
I fixed it!
My steps were:
1) Change the source to just be a text file instead of a csv. This resulted in one column with one row. So one cell containing ALL my data.
2) Split the single cell based on the delimiter ";". This resulted in heaps of columns containing a bunch of information including commas
3) Transpose the data so that columns became rows. So a single column with a bunch of rows
4) Split the column again on the delimiter "," and hey presto! Done!
This forum post set me in the right direction.
If your query code uses the Csv.Document function, then you can try ";" as the third argument (which is the delimiter).
If no luck, then please share your query code.
Hi Marcel. Thanks for helping!
I'm a complete noob to this so I'd like to apologise if I'm getting obvious things wrong :smileyhappy:
This is my query:
= Csv.Document(Web.Contents("http://pvoutput.org/service/r2/getstatus.jsp" & "?d=yyyymmdd" & "&h=1" & "&sid=49100" & "&key=<MyKeyGoesHere>"),[Delimiter=",", Columns=301, Encoding=1252, QuoteStyle=QuoteStyle.None])
You'll see that there is already a Delimiter argument. That splits the data into columns as you'd expect.
So if I was to add in ";" as the 3rd argument, then I'd put it between "Encoding=1252" and "QuoteStyle" right? But how to put it in? Just Encoding=1252, ";", QuoteStyle=QuoteStyle.None
Hi Ross,
Trying, but also learning from this one...
I see you don't have a third argument, but instead you have a record as 2nd argument (the part between and inluding the square brackets [...] ), which is perfectly fine.
So my suggestion then would be to try and adjust the part Delimiter = "," to Delimiter = ";"
= Csv.Document(Web.Contents("http://pvoutput.org/service/r2/getstatus.jsp" & "?d=yyyymmdd" & "&h=1" & "&sid=49100" & "&key=<MyKeyGoesHere>"),[Delimiter=";", Columns=301, Encoding=1252, QuoteStyle=QuoteStyle.None])
If that doesn't help, you can also provide a list of delimiters, like: Delimiter={",",";"}
= Csv.Document(Web.Contents("http://pvoutput.org/service/r2/getstatus.jsp" & "?d=yyyymmdd" & "&h=1" & "&sid=49100" & "&key=<MyKeyGoesHere>"),[Delimiter={",",";"}, Columns=301, Encoding=1252, QuoteStyle=QuoteStyle.None])
Hope this helps, otherwise I would be running out of suggestions. :smileyembarrassed:
Ok so suggestion 1, changing the delimiter to ";" still gave me 1 row with a bajillion columns. But each column contained lots of data with commas in it. So it literally split it into columns separated at the ';'...
Suggestion 2 was this:
[Delimiter={",",";"}, Columns=301, Encoding=1252, QuoteStyle=QuoteStyle.None]
This produced an error :(