Snowflake - trying to load a row of csv data into Variant - "Error parsing JSON:"

Question

I'm trying to load the entirety of each row in a csv file into a variant column.

my copy into statement fails with the below

Error parsing JSON:

Which is really odd as my data isn't JSON and I've never told it to try and validate it as json.

create or replace file format NeilTest
RECORD_DELIMITER = '0x0A'
field_delimiter = NONE
 TYPE =  CSV 
VALIDATE_UTF8 = FALSE;

with

    create table Stage_Neil_Test
(
  Data VARIANT,
  File_Name string

);

copy into Stage_Neil_Test(Data, File_Name
                                 ) 
from (select 
      s.$1,  METADATA$FILENAME
      from @Neil_Test_stage s)

How do I stop snowflake from thinking it is JSON?

Answer 1

You need to explicitly cast the text into a VARIANT type, since it cannot auto-interpret it as it would if the data were JSON.

Simply:

copy into Stage_Neil_Test(Data, File_Name
                                 ) 
from (select 
      s.$1::VARIANT,  METADATA$FILENAME
      from @Neil_Test_stage s)