Expand my Community achievements bar.

SOLVED

Separate with multiple characters in data loading

Avatar

Level 2

Hello, we are going to receive a new file to adobe campaign. But when I try to prepare the data loading I have difficulties on how to separate the columns as there are two different characters separating those.

It looks like this raw:

MartinViking_1-1656423794982.png

 

And I add this to the node and it looks fine in the node.

MartinViking_2-1656423817060.png

 

 

But when I run the workflow I still get the semicolons in the table:

 

MartinViking_0-1656423774450.png

How can I manage this in the best way?

 

Best regards, Martin

1 Accepted Solution

Avatar

Correct answer by
Employee Advisor

Hi @MartinViking 

You are probably hitting a very unusual scenario on that one as it's very rare to have 2 digit separators in file and the product seems to not support it very well......

You could ask the file provider to supply a file with a single digit separator. I found out that pipe | works in most cases.... If a column can potentially hold the pipe (|) character within its value, you will need to use a Double Quote as String separator, meaning that the file will look like the following

"column1"|""column2"|"column3"|"column4"Etc....

"aString"|ANumber|"AnotherString"|"AStringContainingPipe|"etc....

 

If the file provider can't change its format, you might have to use the trick already mentioned by @_Manoj_Kumar_ and you would ignore every 2nd column... You would have to extra test and I will strongly suggest to use Double Quote String delimiter to avoid any issues... and as you're at the edge of the product behaviour, you might still have hiccups from time to time... Having a single digit separator will be far more sfer for long term stable solution.

Thanks

Denis

 

View solution in original post

3 Replies

Avatar

Community Advisor

Hello @MartinViking 

 

Remove the semicolon checkbox and the add the separator in the other box like this:

Manoj_Kumar__0-1656477381427.png

 


     Manoj
     Find me on LinkedIn

Avatar

Level 2

Hi, when I do this it creates more column headers than it sould do.

 

MartinViking_0-1656487507046.png

 

Avatar

Correct answer by
Employee Advisor

Hi @MartinViking 

You are probably hitting a very unusual scenario on that one as it's very rare to have 2 digit separators in file and the product seems to not support it very well......

You could ask the file provider to supply a file with a single digit separator. I found out that pipe | works in most cases.... If a column can potentially hold the pipe (|) character within its value, you will need to use a Double Quote as String separator, meaning that the file will look like the following

"column1"|""column2"|"column3"|"column4"Etc....

"aString"|ANumber|"AnotherString"|"AStringContainingPipe|"etc....

 

If the file provider can't change its format, you might have to use the trick already mentioned by @_Manoj_Kumar_ and you would ignore every 2nd column... You would have to extra test and I will strongly suggest to use Double Quote String delimiter to avoid any issues... and as you're at the edge of the product behaviour, you might still have hiccups from time to time... Having a single digit separator will be far more sfer for long term stable solution.

Thanks

Denis