0

I've got some data in a string column that is in a strange csv format. I can write a file format that correctly interprets it. How do I use my file format against data that has already been imported?

create table test_table
(
    my_csv_column string
)

How do I split/flatten this column with:

create or replace file format my_csv_file_format
type = 'CSV'
RECORD_DELIMITER = '0x0A'
field_delimiter = ' '
FIELD_OPTIONALLY_ENCLOSED_BY = '"'
VALIDATE_UTF8 = FALSE

Please assume that I cannot use split, as I want to use the rich functionality of the file format (optional escape characters, date recognition etc.).

What I'm trying to achieve is something like the below (but I cannot find how to do it)

copy into destination_Table 
from 
(select

s.$1 
,s.$2 
,s.$3 
,s.$4 
from test_table s
file_format = (column_name ='my_csv_column' , format_name = 'my_csv_file_format'))
Neil P
  • 2,920
  • 5
  • 33
  • 64
  • It's not exactly clear what you're trying to achieve, please provide a more concrete example. – Marcin Zukowski Jan 10 '19 at 19:54
  • @MarcinZukowski I've re-written the question, I hope the examples help – Neil P Jan 11 '19 at 09:14
  • 1
    Interesting. I don't think Snowflake today supports using a file format on strings. That would be an interesting functionality! However, since we don't have it, you can COPY a table into a stage using a simple textual output, and then use the file format to scan the generated files. – Marcin Zukowski Jan 13 '19 at 00:04

0 Answers0