MS Access text delimiters - Quotations issue

Question

I have a problem with a .csv file I am trying to load into Access. There are thousands of threads on this topic, and I have tried to find what I am looking for, but no luck.

Any help would be much appreciated.

I am receiving a system-generated csv file, which has 2.5m rows, hence I cant load into Excel, so am using Access. The file is pipe-delimited, with quotation marks as text identifiers.

My question is this - some fields in the csv are blank, which is expected. However, Access doesn't seem to like the resulting double-quotation marks.

Example - row which exports correctly (headings and data):

BALANCE_CLASS|BUSINESS_DATE|BIC_CODE|COUNTRY_CODE
"Gross"|"31-Mar-15"|"51"|"LU"

Example - row which gets "Unparsable record" error:

BALANCE_CLASS|BUSINESS_DATE|BIC_CODE|COUNTRY_CODE
"Gross"|"31-Mar-15"|""|"GB"

This is an entry where the BIC_CODE is blank in the source system database.

Answer 1

Use null instead of simply using ""