Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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);
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);