How can I escape a double quotation mark in Marketing Cloud Imports?

Question

I'm trying to import a file into a data extension in Marketing Cloud and having some trouble with one of my fields. Sometimes, this field has double quotation marks in it, and that causes the import to error on that row (error code: InvalidFieldCount). Is there a way to escape those quotation marks? Or maybe a way to ignore that row all together? It's not even a row I'm importing into the Data Extension, but it's still throwing an error.

Answer 1

I use tab-delimited files whenever possible -- solely because of this issue.

One alternative is to import into a staging table with a single max column and then parsing out each column into a new Data Extension using a Query Activity.

Answer 2

The Respect double quotes as a text qualifier means that will parse the cell content as a whole if the cell starts with double quote ( " ).

Then, how to use inner double quotes?

If you export the file using Excel as a CSV+UTF8 file then it will do this:

"This is a link to somewhere <a href=""https://domain.com"">https://domain.com</a>"

It uses a double-double quotes escaping form. That's enough to parse the files and import them correctly into the DEs.