How to change the export format in the Snowflake

user_14463139 0
Viewed 123

Could you help me? I don't know how to get the result. I have to prepare the export file to the customer by strict structure from Snowflake. I suppose that the answer could be in the change or create new file format or change the view (cause this file generates from view)

Current export file looks like:

id,id_new,sub_building_name,building_name,building_number,price

"34106391","","FLAT THIRD FLOOR","","7","3.8963552865168741"
"34106392","","FLAT FOURTH FLOOR","","7","3.4363554835138543"

The new export file should be look like:

"id","id_new","sub_building_name","building_name","building_number","price"

34106391,,"FLAT THIRD FLOOR",,7,3.8963552865

34106392,,"FLAT FOURTH FLOOR",,7,3.4363554835

So, what changes need to do:

  1. enclosed the header to double-quotes
  2. type numeric and null values haven't enclosed double-quote (only strings should be enclosed "")
  3. change precision float values from "3.8963552865168741" (16) to 3.8963552865 (10)

Thanks

snowflake-cloud-data-platform
1 Answers

These options will get you halfway there:

copy into @stage/stacko/out1.csv 
from (
    select '1' a, '2' b, 1234.12345678901234567890 c, null d, 'a,b,c' e
)
file_format = (type = 'csv' compression=None, null_if=(), field_optionally_enclosed_by='"')
header = true
overwrite = true

"A","B","C","D","E"
"1","2",1234.1234567890123456789,,"a,b,c"

Now, you will need to cast the numbers and get the right precision in SQL before formatting them. Then things will look as you want them to:

copy into @fhoffa_lit_stage/stacko/out1.csv 
from (
    select '1'::number a, '2'::number b, 1234.12345678901234567890::number(38,5) c, null d, 'a,b,c' e
)
file_format = (type = 'csv' compression=None, null_if=(), field_optionally_enclosed_by='"')
header = true
overwrite = true

"A","B","C","D","E"
1,2,1234.12346,,"a,b,c"
user_1324380
Related