Loading data from Blob to Snowflake table

Question

I have a .txt file and 1st row in the file is column name. I want to load this data to Snowflake table.

1st think How can run a select Statement to see all the columns from file using *. I don't want write t.$1, t.$2, .... ect.

Something Similar to SELECT t. FROM '@azure_blob_stage_poc/Dim_Date.txt' AS t ORDER BY 1;*

Also When Loading data to Table I have to Ignore 1st Row from file as It contains Column Names. I need Snow flake script similar to COPY INTO POC.Dim_Date FROM '@azure_blob_stage_poc/Dim_Date.txt';

If I don;t ignore 1st Row and try to load getting error message: "Field delimiter ',' found while expecting record delimiter '\n' File 'Dim_Date.txt', line 2, character 547 Row 2, column "DIM_DATE"["LOAD_DT":55] If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client."

Hans Henrik Eriksen Hans Henrik Eriksen · Accepted Answer · 2019-11-21T07:42:40

Please read the documentation on the COPY INTO <table> command.

The CSV section has a parameter called SKIP_HEADER that can be used to skip the header line.

Your "question" contains less of an actual question, but there is a "How" in there somewhere related to text file discovery. Normally this is what an ETL/integration tool does for you, but the obvious thing is to look at the file in an text editor.

I'll get the list of columns by reading a complete record as a single field and split using SPLIT_TO_TABLE()::

CREATE OR REPLACE STAGE my_stage URL = 's3://<bucket>[/<path>/' CREDENTIALS = ( ... );
CREATE OR REPLACE FILE FORMAT TEST_TXT TYPE = CSV FIELD_DELIMITER = NONE;

SELECT
  LISTAGG('$'||INDEX||' "'||TRIM(VALUE, '"')||'"', ', ') WITHIN GROUP (ORDER BY INDEX) COLS
FROM '@my_stage/my_file' (FILE_FORMAT => 'TEST_TXT') x
CROSS JOIN LATERAL SPLIT_TO_TABLE(x.$1, ',') s
GROUP BY SEQ HAVING SEQ = 1;
    =>
$1 "Order date", $2 "Item code", $3 "Quantity"

Then I just copy the result COLS into a new SELECT using a new FILE FORMAT:

CREATE OR REPLACE FILE FORMAT TEST_TXT2
    TYPE = CSV SKIP_HEADER = 1 FIELD_OPTIONALLY_ENCLOSED_BY = '"';

SELECT $1 "Order date", $2 "Item code", $3 "Quantity"
FROM '@my_stage/my_file' (FILE_FORMAT => 'TEST_TXT2') x;

The special SQL construct *for column names only works for named record sets. There is no way to convert data content to SQL column names.

Loading data from Blob to Snowflake table

1 Answers