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

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

ChristopheG — Sat, 16 Nov 2024 04:32:29 GMT

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?

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

Lisa_P — Mon, 30 Sep 2019 00:09:51 GMT

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

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

ChristopheG — Tue, 01 Oct 2019 15:01:51 GMT

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);