Snowflake Fixed-Width File Loading Options and Examples

  • Post author:
  • Post last modified:February 4, 2020
  • Post category:Snowflake
  • Reading time:5 mins read

Fixed width text files are special cases of text files where the format is specified by column widths, pad character and left or right alignment. Many telecom companies use fixed-width file to store call detail records (CDR) data. In this format, column width are in terms of units of characters. In this article, we will learn about Snowflake Fixed-Width file loading options and examples.

Snowflake Fixed-Width File Loading Options and Examples

Snowflake Fixed-Width File Loading

The fixed-width data files have uniform lengths for each column of data. Each field in a fixed-width data file has exactly the same length and position.

In fixed width files, there are no field or date delimiters, and there may or may not be an end-of-record delimiter associated with a file. As the file is fixed-length and does not require delimiter. You have to use explicit type casting to get required formats.

Steps to Load Fixed-Width File into Snowflake Table

Snowflake does not support loading fixed-width file using the COPY command. But, you can always use a workaround to parse fixed-width file.

Following are the steps that you can use to load fixed-width file into Snowflake table.

  • Add Local file to Stage Area
  • Load Fixed-width file into Single Column Table
  • Use SUBSTRING function to get required Fields

Now, we can check these steps in brief.

Add Local file to Stage Area

This step is applicable only if you have a source file in your local system. Another option would be to copy fixed width file to S3 location.

From the SnowSQL command line interface, execute following command to add fixed width file to stage path.

put file:///home/snowflake/Sample_fixedWidth_file.dat @~/staged;

The command will add the gzipped source file to stage location.

Load Fixed-width file into a Single Column Table

As the file is fixed-length file, you can create Snowflake table with single column which has fixed length.

For example, create single column table

>create table sample_test_single_col (single_col varchar(2000));
+----------------------------------------------------+
| status                                             |
|----------------------------------------------------|
| Table SAMPLE_TEST_SINGLE_COL successfully created. |
+----------------------------------------------------+
Use COPY Command to Load File from Stage to Snowflake Table

Now, the table is available in the database. Execute COPY command to load staged file to single column table.

For example, consider following COPY command.

>COPY INTO sample_test_single_col FROM '@~/staged/Sample_fixedWidth_file.dat.gz' FILE_FORMAT = ( COMPRESSION = GZIP);
Use Substring to Extract Require Fields

Now, entire fixed-width table data is available in Snowflake. Simply use Substring sting function to extract required fields from the fixed width column.

For example, 

> select  SUBSTR(SINGLE_COL, 0, 10) as mobile, substr(SINGLE_COL, 12, 5) as device_id, substr(SINGLE_COL, 18, 5) as area_code from SAMPLE_TEST_SINGLE_COL;
+------------+-----------+-----------+
| MOBILE     | DEVICE_ID | AREA_CODE |
|------------+-----------+-----------|
| 1234567890 | aaa12     | 32114     |
| 0987654123 | bbb12     | 45789     |
| 1122445522 | ccc12     | 75896     |
| 7845951656 | ddd13     | 45795     |
| 5645798965 | xyz45     | 45564     |
| 4547858554 | vvd12     | 84654     |
+------------+-----------+-----------+

Now you can insert it into target table.

Related Articles,

Hope this helps 🙂

Tags: