Single quote inside columns

[pauldamen]

Hi all,

I have a CSV file which contains data in columns which start with a single quote. Looks like this:

ID          Description          Type          Name

1            '17 name               Main          Name1

2            '17 name               Main          Name2

3            '17 name               Main          Name3

4            '17 name               Main          Name4

For some reason because of the quote it creates 1 field and my Description field looks like: '17 name;Main;Name1. My other fields move a column so the type field suddenly has the Name value in it.

I now it is because of the quotes because when I removed them from the data everything went fine. I tried to remove the quote with the Purgechar or replace and tried to tweak the CSV load with the msq or no quote value. Unfortunatly nothing seems to help.

Hope you guys have an idea how to fix this. Unfortunatly because of confidential data I can't attach the example

Regards, Paul

[pauldamen]

I got a little bit further! With the subfield and the concatenate I got the 6 fields into there own column.

1 problem remaining; behind the 6 columns which are placed in one field I got 3 other columns which also have wrong values because they moved. This is not in the subfield, how can I get the correct values in these columns?

[marcus_sommer · Accepted solution]

Please provide a csv with a few example-records - both correct ones and the faulty ones and also an example how the faulty records should look like if they are corrected.

Beside them it could be more appropriate to correct these issues within the source respectively to adjust the export into the csv-files. Another approach might be to flag those invalid records and to ignore them per filter or selections within the further evaluations (not each data-quality issue could be solved respectively is it worth to build heavy workarounds and/or to adjust them manually).

- Marcus

[sasikanth]

HI,

Try below

replace(Text(Description), chr(39), '') as Description

[pauldamen]

Hi Marcus,

Your answer got me thinking. Also in the meanwhile I found out that there are many scenario's based on the number of quotes in the field but also in the fields after (because if there were quotes there it also changed the length of the string). So I was scripting to many scenario's.

What I decided doing is just to save my CSV as an Excel. This is an extra step but then I can just load the file without having to worry about quotes etc.

Thanks for the help!