topic Re: Using FROM_CSV giving unexpected results in Data Engineering

Using FROM_CSV giving unexpected results

seanstachff — Wed, 04 Dec 2024 06:02:22 GMT

Hello, I am trying to use from_csv in the sql warehouse, but I am getting unexpected results:

As a small example I am running:

WITH your_table AS ( SELECT 'a,b,c\n1,"hello, world",3.14\n2,"goodbye, world",2.71' AS csv_column ) SELECT from_csv(csv_column, 'a INT, b STRING, c DOUBLE', map('header', 'true','multiLine','true')) AS parsed_csv FROM your_table

which is returning one row with the object:

object a: null b: "b" c: null

It looks like it's still reading the header and not understanding the linebreak correctly. I would expect it to return an array of objects with two rows following the schema. I've tried a bunch of different combinations of mapping with linesep, header, etc. But I just can not get it to work how I would expect.

Does anyone have any advice on this?

Re: Using FROM_CSV giving unexpected results

Takuya-Omi — Wed, 04 Dec 2024 07:47:53 GMT

Hi, @seanstachff

Is the following result what you are expecting?

parsed_csv
{"a":1,"b":"hello, world","c":3.14}
{"a":2,"b":"goodbye, world","c":2.71}

If so, you can achieve it with the SQL below.

%sql WITH your_table AS ( SELECT 'a,b,c\n1,"hello, world",3.14\n2,"goodbye, world",2.71' AS csv_column ) SELECT from_csv( row, 'a INT, b STRING, c DOUBLE', map('header', 'false', 'multiLine', 'false') ) AS parsed_csv FROM your_table LATERAL VIEW explode(slice(split(csv_column, '\n'), 2, size(split(csv_column, '\n')) - 1)) AS row;

If you’re working with table-formatted data where each column exists as a separate field, there are other methods available as well.

Re: Using FROM_CSV giving unexpected results

seanstachff — Wed, 04 Dec 2024 21:59:22 GMT

Thanks, I didn't read the docs clear enough and see that csv_data is for one row. I'm a big confused as to why it has a header then. My only issue with this is how could you handle the possibility of it having a linebreak in a string value on the csv row

Re: Using FROM_CSV giving unexpected results

Takuya-Omi — Thu, 05 Dec 2024 01:46:35 GMT

Is this the kind of result you are expecting?

The data is interpreted as multiple rows.
The CSV contains multiple data rows (records), each split according to the schema.
Even when line breaks are part of a column value, they are not mistaken as row delimiters and are handled correctly as part of the dataset.

Is it something like the result shown in the image?

Re: Using FROM_CSV giving unexpected results

seanstachff — Fri, 06 Dec 2024 03:22:08 GMT

Yes, you are correct. That is the result I want. The above I showed is just an example however my real source table is a list of files with one column value being the entire contents of a CSV.

Re: Using FROM_CSV giving unexpected results

Takuya-Omi — Fri, 06 Dec 2024 04:26:50 GMT

@seanstachff

Here is the code I used to produce the results shown in the image I shared earlier. It's a bit verbose, so I’m not entirely satisfied with it, but I hope it might provide some helpful insights for you.

%sql WITH your_table AS ( -- Example CSV data SELECT 'a,b,c\n1,"hello, world",3.14\n2,"goodbye, world",2.71' AS csv_column ), split_lines AS ( -- Split data into lines SELECT explode(split(csv_column, '\n')) AS line, row_number() OVER (ORDER BY csv_column) AS row_num FROM your_table ), data_without_header AS ( -- Exclude header row SELECT line FROM split_lines WHERE row_num > 1 ), parsed_data AS ( -- Parse CSV format (considering newlines and double quotes) SELECT from_csv( line, 'a INT, b STRING, c DOUBLE', map('header', 'false', 'multiLine', 'true', 'quote', '"', 'delimiter', ',') ) AS parsed_row FROM data_without_header ) SELECT parsed_row.* FROM parsed_data;