Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

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

Avatar

Avatar
Level 1
WastonAdobe
Level 1

Likes

0 likes

Total Posts

1 post

Correct Reply

0 solutions
View profile

Avatar
Level 1
WastonAdobe
Level 1

Likes

0 likes

Total Posts

1 post

Correct Reply

0 solutions
View profile
WastonAdobe
Level 1

20-05-2021

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.

Accepted Solutions (0)

Answers (2)

Answers (2)

Avatar

Avatar
Validate 1
MVP
Darren_Bowers
MVP

Likes

78 likes

Total Posts

263 posts

Correct Reply

85 solutions
Top badges earned
Validate 1
Affirm 50
Establish
Give Back 3
Applaud 25
View profile

Avatar
Validate 1
MVP
Darren_Bowers
MVP

Likes

78 likes

Total Posts

263 posts

Correct Reply

85 solutions
Top badges earned
Validate 1
Affirm 50
Establish
Give Back 3
Applaud 25
View profile
Darren_Bowers
MVP

20-05-2021

@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

Avatar

Avatar
Validate 1000
Employee
Sukrity_Wadhwa
Employee

Likes

141 likes

Total Posts

862 posts

Correct Reply

35 solutions
Top badges earned
Validate 1000
Validate 500
Validate 250
Validate 100
Validate 25
View profile

Avatar
Validate 1000
Employee
Sukrity_Wadhwa
Employee

Likes

141 likes

Total Posts

862 posts

Correct Reply

35 solutions
Top badges earned
Validate 1000
Validate 500
Validate 250
Validate 100
Validate 25
View profile
Sukrity_Wadhwa
Employee

02-06-2021

Hi @WastonAdobe,

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

Thanks!