---
title: "Snowflake error: Field delimiter found while expecting record delimiter"
description: "While working on some data and importing a few files from an s3 bucket, Snowflake encountered an error that took me too long to figure out: Field delimiter ‘,’ found..."
---

[DDBM Blog ](https://www.ddbm.com/en/blog)

# [Snowflake error: Field delimiter found while expecting record delimiter](https://www.ddbm.com/en/blog/snowflake-error-field-delimiter-found-while-expecting-record-delimiter)

 Written by [Mike Droog](https://www.ddbm.com/en/blog/author/mike-droog) | 13 Apr, 2021

While working on some data and importing a few files from an s3 bucket, Snowflake encountered an error that took me too long to figure out: Field delimiter ‘,’ found while expecting record delimiter ‘\n’

To start of , I’m using a simple csv file format :

```
create or replace file format my_csv_format
  type = csv
  record_delimiter = '\n'
  field_delimiter = ','
  skip_header = 1
  null_if = ('NULL', 'null')
  empty_field_as_null = true
```

The files that I’m importing are structured like this example :

| 74942,”Trading Alliance”,1492176193,80,Cooperative,,,,1 |
| --- |

 Line example from the csv file

As you can see, the field is delimited by a ‘,’, empty fields are present and should be treated as NULL values, and from reviewing the file, I can see that every record has its own line.

With everything set , I started the imported but was quickly hit by the dreaded error :

> Field delimiter ‘,’ found while expecting record delimiter ‘\n’ File ‘factions.csv’, line 759, character 85 Row 758, column “FACTIONS”[“IS_PLAYER_FACTION”:9]

the corresponding line :

| 74927,”Strategic Wings, Order and Retaliation Division”,1475489397,80,Cooperative,,,,1 |
| --- |

 Line giving the error

We can simply read that “Snowflake is finding a field delimiter while expecting a record delimiter” but what does it mean? It simply means that after an X number of Field Delimiters, Snowflake expects the next delimiter to be a record delimiter. The X here is the number of columns in the table (which is 8).

If we count the number of “,” we can spot the error ; this line has 9 instead of 8! One of the fields contains an extra : *“Strategic Wings, Order and Retaliation Division”*. The field has a “,” in its contents but shouldn’t be used as a delimiter.

This is where a change to the file format comes in :

FIELD_OPTIONALLY_ENCLOSED_BY makes it possible to handle these kind of field values. We should let Snowflake know that the fields as a whole can be enclosed by double quotes (“).  
Our first instinct would tell us to use FIELD_OPTIONALLY_ENCLOSED_BY = ” ” ” or FIELD_OPTIONALLY_ENCLOSED_BY ‘ ” ‘ to make this happen but this is the second reason for writing this piece. Snowflake also references this in its documentation:

> Character used to enclose strings. Value can be `NONE`, single quote character (`'`), or double quote character (`"`). To use the single quote character, use the octal or hex representation (`0x27`)

The hex representation of a double quote is 0x22 , making the new csv file format :

```
create or replace file format my_csv_format
  type = csv
  record_delimiter = '\n'
  field_delimiter = ','
  skip_header = 1
  null_if = ('NULL', 'null')
  empty_field_as_null = true
  FIELD_OPTIONALLY_ENCLOSED_BY = '0x22'
```

 

[View full post](https://www.ddbm.com/en/blog/snowflake-error-field-delimiter-found-while-expecting-record-delimiter)

```json
{
  "@context" : "http://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Mike Droog"
  },
  "dateModified" : "2026-01-09T15:29:12.413Z",
  "datePublished" : "2021-04-13T21:58:42Z",
  "headline" : "Snowflake error: Field delimiter found while expecting record delimiter",
  "image" : {
    "@type" : "ImageObject",
    "height" : 1024,
    "url" : "https://139739125.fs1.hubspotusercontent-eu1.net/hubfs/139739125/AI-Generated%20Media/Images/a%20generic%20image%20that%20i%20can%20use%20for%20a%20blog%20talking%20about%20an%20error.png",
    "width" : 1536
  },
  "mainEntityOfPage" : "https://www.ddbm.com/en/blog/snowflake-error-field-delimiter-found-while-expecting-record-delimiter",
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "height" : 60,
      "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
      "width" : 60
    },
    "name" : "DDBM Blog"
  }
}
```