How to handle file import if the content of a column contains the string delimiter?

[WastonAdobe]

Hi,

We have configured the string delimiter as double quote in the Data loading activity.

However, we found that the content of a column contains the double quote which crashes the data import and that row till be skipped.

How should we handle this case so that the row of data can still be imported?

Thanks.

[Darren_Bowers · Accepted solution]

@WastonAdobe- this is a common problem, but doesn't have any really nice fixes if you are using the built-in functionality to import the incoming files.

Most of the fixes would require upstream fixes: e.g.

1. Change your string delimiter to not include use the problem character (using uncommon characters like pipe "|" can help)
2. Parse your source data to change the delimiter character before or during export (i.e. single quote to double quote or visa-versa)
3. Pre-parse the file before import using sed/awk/perl/JS to apply some regex functions to the string (e.g. if the delimiter character does not have a comma before or after it then swap it out with a different quote character - not perfect but might help)
4. Remove the string delimiter altogether (provided your strings don't contain the column delimiter)

The best method is usually 1 and/or 2 as whoever/whatever is creating the data file should have the responsibility to provide you with correctly formed data.

Cheers

Darren

[CedricRey]

Hello, I'm not an expert, but I remember that Excel has a specific process for this case : it double the double quote : for example 'This IS a " quote' will be 'This IS a "" quote'. ACC doesn't not handle this ?

[Sukrity_Wadhwa]

Hi @WastonAdobe,

Were you able to resolve this query or do you still need more help here? Do let us know.

Thanks!