CODEWITHSUNDEEP
snowflake-cloud-data-platform
Alexandr Kapshuk
Alexandr Kapshuk

Reputation: 1471

CSV format of Snowflake UI Download Results doesn't enclose values with newline character into quotation marks

I have a table that contains string values with new-line character.

id    value
0       a
1       b
        c
2       d

In my example, the second row holds value b\nc.

When I query the data from a Worksheet, the data is displayed correctly. However when I'm downloading the results in a CSV format using Download or View Results button, the values that contain the new-line character aren't enclosed in quotation marks.

What I expect:

id, value
0, a
1, "b
c"
2, d

and this would be read by Excel correctly. What I get is

0,a
1,b
c
2,d

Is there a way to make Snowflake enclose fields containing \n into quotes?

Upvotes: 1

Views: 1527

Answers (1)

Simon D
Simon D

Reputation: 6229

Just did a test and it works as you are expecting - Snowflake adds the value with the newline inside quotes when you download as a CSV. If you are testing this by opening the file in Excel then that's probably what's causing your issue.

Never check what a CSV file looks like by opening it in Excel, always open it in a text editor. Excel does not accurately represent the contents of a file. Excel does things like remove quotes etc. Also, if you then save the file in Excel, it actually changes the file's contents which confuses people even more when they re-open it in a text editor.

Here is my test:

-- Create a sample table with two columns
create or replace table test_db.public.test_table (col1 number, col2 varchar);

-- Insert three rows into the table. The middle one has a \n
insert overwrite into test_db.public.test_table values (0, 'a'), (1, 'b 
c'), (2, 'd');

-- Select all from the table and then Download the results as a CSV
select * from test_db.public.test_table;

If I go to where the file downloads to and check its contents (by opening it with Sublime Text instead of Excel) it looks like this:

COL1,COL2
0,a
1,"b 
c"
2,d

You can see that the second row contains the quoted characters.

Upvotes: 2

Related Questions