How do I unload a CSV file where only non-null values are wrapped in quotes, quotes are optionally enclosed, and null values are not quoted?

0
votes

(Submitting on behalf of a Snowflake User)

For example - ""NiceOne"" LLC","Robert","GoodRX",,"Maxift","Brian","P,N and B","Jane"

I have been able use create a file format that satisfies each of these conditions, but not one that satisfies all three.

I've used the following recommendation:

Your first column is malformed, missing the initial ", it should be: """NiceOne"" LLC"

After fixing that, you should be able to load your data with almost default settings,

COPY INTO my_table FROM @my_stage/my_file.csv FILE_FORMAT = (TYPE =
CSV FIELD_OPTIONALLY_ENCLOSED_BY = '"');

...but the above format returns: returns -

"""NiceOne"" LLC","Robert","GoodRX","","Maxift","Brian","P,N and B","Jane"

I don't want quotes around empty fields. I'm looking for

"""NiceOne"" LLC","Robert","GoodRX",,"Maxift","Brian","P,N and B","Jane"

Any recommendations?

1
snowflake-cloud-data-platform
This question is about writing a CSV file, not about reading it... – Hans Henrik Eriksen

1 Answers

1
votes

If you use the following you will not get quotes around NULL fields, but you will get quotes on '' (empty text). You can always concatenate the fields and format the resulting line manually if this doesn't suite you.

COPY INTO @my_stage/my_file.CSV
FROM (
  SELECT
    '"NiceOne" LLC' A, 'Robert' B, 'GoodRX' C, NULL D,
    'Maxift' E, 'Brian' F, 'P,N and B' G, 'Jane' H
)
FILE_FORMAT = (
  TYPE = CSV
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  NULL_IF = ()
  COMPRESSION = NONE
)
OVERWRITE = TRUE
SINGLE = TRUE