Snowflake: File format with RECORD_DELIMITER with chars + new line

Question

I have a problem with creation of file formats in snowflake where I can create the file format but I can't make him do what I need. Basically I have CSV file that is coming in a weird format. mainly the record encapsulation is both the new line (\n) and a few more characters before (#@#)

So basically I'm trying to create a file format with this something like:

CREATE OR REPLACE FILE FORMAT  META.CSV_TEST
    TYPE                            = CSV
    SKIP_HEADER                     = 0
    SKIP_BLANK_LINES                = TRUE
    FIELD_DELIMITER                 = '^^{'
    RECORD_DELIMITER                = '#@#\\n'
    FIELD_OPTIONALLY_ENCLOSED_BY    = '"'
    EMPTY_FIELD_AS_NULL             = TRUE
    NULL_IF                         = ('NULL', '-')
;

But this does not do what I want and basically the \n stays in the records. I tried also '#@#\n' but no such luck.

Any idea how I could create such a file format?

According to the documentation, if I understood correctly, this should work, but maybe my error is more basic.

https://docs.snowflake.com/en/sql-reference/sql/create-file-format.html

I do not get errors when creating the file format simply in the output of the content I still see the \n

For example:

JavaScript execution error: Uncaught 
{"RECORD_CONTENT":"OBJECT_CONSTRUCT(\n 'BU_ID',

Another thing I try is just to remove the #@# and then this works fine but the #@# becomes part of my contents and I would rather avoid that. I can do some post-processing after loading the file but I would really like to do this in the file format just not being able to do it.

Thank you for any insights best regards

Answer 1

I got the same issue and found this work-around. Add the newline character by moving the closing quote to the next line in the text editor. Definitely not the ideal solution though.

For your example it will be:

CREATE OR REPLACE FILE FORMAT  META.CSV_TEST
    TYPE                            = CSV
    SKIP_HEADER                     = 0
    SKIP_BLANK_LINES                = TRUE
    FIELD_DELIMITER                 = '^^{'
    RECORD_DELIMITER                = '#@#
'
    FIELD_OPTIONALLY_ENCLOSED_BY    = '"'
    EMPTY_FIELD_AS_NULL             = TRUE
    NULL_IF                         = ('NULL', '-')
;