CSV does not load the record correctly when I have single quotes in separate fields

[ChristopheG]

Not all fields are correctly loaded when I have single quotes in 2 separate column fields in the source CSV file

Example:

Name;Type;Price;Description;Quantity
't Apple; Fruit; 2,00;I have to do's;10,00

The result will be that everything between the single quotes is loaded in the Name field, so ignoring the delimiter.

Name                                                            Type   Price   Description Quantity
't Apple; Fruit; 2,00;I have to do's      10,00 Null     Null                 Null

Note: Doing the same in SQL Server works perfectly

How can I resolve this without changing the source file? 

[Lisa_P · Accepted solution]

One way to do this is to load this in as one field, then split it out using ; as delimiter in the data manager

[ChristopheG · Accepted solution]

Thanks Lisa,

I was able to clean the source file without the single quotes in our source system before it got exported.

I have also tried your idea and that should work as well I believe, but to avoid any errors we managed to clean the source.

I was doing the below script to load everything in a single line, but again I have stopped because we were able to clean the source:

CSV:
LOAD
Chr(39) & LEFT([@1:n],Index([@1:n],';',1)) & chr(39)
& Chr(39) & Mid([@1:n],Index([@1:n],';',1)+1,Index([@1:n],';',2)- Index([@1:n],';',1)) & Chr(39)
& Chr(39) & Mid([@1:n],Index([@1:n],';',2)+1,Index([@1:n],';',3)- Index([@1:n],';',2)) & Chr(39)
& Chr(39) & Mid([@1:n],Index([@1:n],';',3)+1,Index([@1:n],';',4)- Index([@1:n],';',3)) & Chr(39)
 As Line

FROM [lib://temp.csv]
(txt,fix, utf8);