Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
ChristopheG
Contributor II
Contributor II

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

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? 

Labels (2)
2 Solutions

Accepted Solutions
Lisa_P
Employee
Employee

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

View solution in original post

ChristopheG
Contributor II
Contributor II
Author

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

 

 

View solution in original post

2 Replies
Lisa_P
Employee
Employee

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

View solution in original post

ChristopheG
Contributor II
Contributor II
Author

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

 

 

View solution in original post