topic Re: Semicolon in Data Source - How to load the data properly? in QlikView

Semicolon in Data Source - How to load the data properly?

Wed, 27 Jan 2016 08:34:00 GMT

Hi guys,

I need your help, if there is anything possible.

The situation is as follows:

I have a csv file as data source which gets updated multiple times a day. The csv file is semicolon seperated. It always contains a fixed set of columns (20 columns). One column is a description. Like twice a week or so it happens that in 2-5 rows the description contains a semicolon in the text.

As I want to automate the whole data loading process you see the point, right? Is there any way or possibility to do not get an error when this happens, caused by the fact that the rows get displaced due to the semicolon in the text. My first idea was to just replace the semicolon seperator with something else when the source exports the csv file, but this is unfortunately not possible.

Is there any way to overcome this?

Thanks guys!

Greetings,

Seb

Re: Semicolon in Data Source - How to load the data properly?

asgardd2 — Wed, 27 Jan 2016 10:01:46 GMT

You can use the function Subfield() to manually parse the field from your file with the delimiter.

For example,if you know that next field is e-mail address,therefore the next field should be checked for the presence of the char '@' .

if(subfield(@1,';',5) like '*@*',subfield(@1,';',4),

if(subfield(@1,';',6) like '*@*',subfield(@1,';',4) & subfield(@1,';',5)... as description

if(subfield(@1,';',5) like '*@*',subfield(@1,';',5),

if(subfield(@1,';',6) like '*@*',subfield(@1,';',6) ... as e_mail

It works fine if your field 'description' is the one of the last fields in your input file,

otherwise, all fields after 'description' mustbe processing through 'if' statement.

Re: Semicolon in Data Source - How to load the data properly?

Wed, 27 Jan 2016 11:28:55 GMT

Unfortunately it is one of the first fields in the file.

Mhh.. is there any other way to overcome it?

Re: Semicolon in Data Source - How to load the data properly?

swuehl — Wed, 27 Jan 2016 11:38:06 GMT

Can you use quoting when you export the data to the csv-file? Like embedding each field value into double quotes:

"10"; "20"; "Text; with semi;colon"

Re: Semicolon in Data Source - How to load the data properly?

Wed, 27 Jan 2016 11:50:00 GMT

No, I can't change the source file. I have to use it as it is. Unfortunately..

Re: Semicolon in Data Source - How to load the data properly?

sasiparupudi1 — Wed, 27 Jan 2016 12:59:50 GMT

Probably skip those line using a where condition

where SubStringCount(yourTextLine,';')>20

and you can write the lines where count of semicolon in the text is more than 20, into a different table or a log file for possible analysis and rectification.

Best solution would be to change the delimiter in your source file

HTH

Sasi

Re: Semicolon in Data Source - How to load the data properly?

Wed, 27 Jan 2016 14:45:47 GMT

Hi Sasi,

how can I reference a whole textline in a CSV for a where clause like in your example as Qlikview already seperates the line of text by semicolon while loading. I'm not sure yet how to use it properly, you understand? But I understand your idea.

I know that to change the delimiter is the best solution, this was also my first idea, but not possible in this case.

Kind regards,

Seb

Re: Semicolon in Data Source - How to load the data properly?

sasiparupudi1 — Wed, 27 Jan 2016 18:50:18 GMT

Try like in the attachement.

SET vDel=';';

Temp:

LOAD @1

FROM

(txt, codepage is 1252, no labels, delimiter is '@', msq)

where SubStringCount(@1,'$(vDel)')<=20;

FinalTable:

Load

subfield(@1,'$(vDel)',1) as Column1,

subfield(@1,'$(vDel)',2) as Column2,

subfield(@1,'$(vDel)',3) as Column3

Resident Temp;

drop Table Temp;

hth

Sasi

Re: Semicolon in Data Source - How to load the data properly?

MarcoWedel — Wed, 27 Jan 2016 22:17:15 GMT

Hi,

one solution might be:

using this csv file:

table1:
LOAD Left([@1:n],Index([@1:n],';',2))&'"'&Mid([@1:n],Index([@1:n],';',2)+1,Index([@1:n],';',SubStringCount([@1:n],';')-16)-Index([@1:n],';',2)-1)&'"'&Mid([@1:n],Index([@1:n],';',SubStringCount([@1:n],';')-16)) as line
FROM QlikCommunity_Thread_202456.csv (fix, codepage is 1252)
Where RecNo()>1;

table2:
LOAD *
From_Field (table1, line) (txt, codepage is 1252, no labels, delimiter is ';', msq);

tabFieldNames:
CrossTable (oldFieldName,newFieldName)
LOAD 1,*
FROM QlikCommunity_Thread_202456.csv (txt, codepage is 1252, no labels, delimiter is ';', msq)
Where RecNo()=1;

mapFieldNames:
Mapping LOAD oldFieldName, newFieldName
Resident tabFieldNames;

DROP Tables table1, tabFieldNames;

RENAME Fields using mapFieldNames;

hope this helps

regards

Marco

Re: Semicolon in Data Source - How to load the data properly?

Peter_Cammaert — Wed, 27 Jan 2016 22:41:57 GMT

Very nice solution Marco, your fixing bad source file formatting on the fly. I think it will be extremely difficult to find a simpler solution.

Peter

Re: Semicolon in Data Source - How to load the data properly?

MarcoWedel — Wed, 27 Jan 2016 23:11:48 GMT

Thanks.

I first tried to LOAD * From_Field with embedded labels, but failed doing so.

That's why I had to rename the fields in the end.

Nevertheless, I'm glad it worked.

good night

Marco

Re: Semicolon in Data Source - How to load the data properly?

rwunderlich — Thu, 28 Jan 2016 00:09:34 GMT

Marco, it's acceptable to provide comments in your script

-Rob

Re: Semicolon in Data Source - How to load the data properly?

Thu, 28 Jan 2016 07:32:30 GMT

Hi Marco,

the outcome looks exactly like that what I need.

It would be nice to add comments in the script or to explain me what the script is actually doing as I cant figure it out properly yet.

But yeah this looks like what I need.

Thanks a lot for your help already!

EDIT: I tested your QVW-File with mine csv. It does not work properly, but probably because my description column is the 5th and not the 3rd column. How can I fix that?

And I saw that it generates a synthetic key. Is that correct?

I would just store table 2 then in a qvd to use it somewhere else right?

And another point is that it does not display the letters ö, ä, ü properly. I already tried to replaced codepage 1252 by utf8 or ansi (which is the same as 1252) but this was not the root cause or I took the wrong charset. Notepad++ says the file is Ansi encoded.

Re: Semicolon in Data Source - How to load the data properly?

swuehl — Thu, 28 Jan 2016 18:30:41 GMT

In the first table load, Marco is reading your input file in with out parsing the columns, so each line goes into a single field 'line'.

Left([@1:n],Index([@1:n],';',2))&'"'&Mid([@1:n],Index([@1:n],';',2)+1,Index([@1:n],';',SubStringCount([@1:n],';')-16)-Index([@1:n],';',2)-1)&'"'&Mid([@1:n],Index([@1:n],';',SubStringCount([@1:n],';')-16))

He is adding double quotes " around the part in question, to find the correct positions for the double quotes, he is looking for the 2 semicolon counted from the beginning, he knows this will start the third column in the file, the text description. But he does not know how many semicolons are contained in the text. So for the end of the column, he is looking for the 16th semicolon counted from the end.

[edit: see below for the implementation shortcut. Marco is counting the semicolons from the left, and counting 'Total number of semicolons - expected semicolons after column 3' instead of just counting from the end of the line]

Left([@1:n],Index([@1:n],';',2)) // Find second semicolon, end of second column in line

&'"'&Mid([@1:n],Index([@1:n],';',2)+1,Index([@1:n],';',SubStringCount([@1:n],';')-16)-Index([@1:n],';',2)-1)&'"' // enclose the third column with double quotes

Mid([@1:n],Index([@1:n],';',SubStringCount([@1:n],';')-16)) // add columns 4-20

Once the columns with text is quoted, you can use MSQ style int format specifier to read it in without taking semicolons as separators, separating the line into fields in the data model.

The rest of the script is then mapping the column names to the names used in the file.

If you want to parse your text field located in the 5th column, you need to replace 2 by 4 and 16 by 14 in above expression.

With regard to your issue displaying the character correctly, could you upload a small sample csv file?

Re: Semicolon in Data Source - How to load the data properly?

swuehl — Thu, 28 Jan 2016 18:43:46 GMT

And since Index() does also takes a negative position index, you can simplify the line manipulation to

Left([@1:n],Index([@1:n],';',2))&'"'&Mid([@1:n],Index([@1:n],';',2)+1,Index([@1:n],';',-17)-Index([@1:n],';',2)-1)&'"'&Mid([@1:n],Index([@1:n],';',-17))

Re: Semicolon in Data Source - How to load the data properly?

MarcoWedel — Thu, 28 Jan 2016 22:24:21 GMT

Hi,

another version that includes an ID field to be able to load files with double rows.

(and some comments, Rob 😉

// defining the number of the column that might contain semicolons
LET vColumnWithSemicolon = 3;
LET vColumns = 20;

// load whole lines of csv file without headers and adding double quotes around values of above specified column
// quoted semicolons will not be treated as delimiters. Append Record numbers as additional column to let lines be distinct
table1:
LOAD Left([@1:n],Index([@1:n],';',$(vColumnWithSemicolon)-1))&'"'&Mid([@1:n],Index([@1:n],';',$(vColumnWithSemicolon)-1)+1,Index([@1:n],';',SubStringCount([@1:n],';')+$(vColumnWithSemicolon)-$(vColumns)+1)-Index([@1:n],';',$(vColumnWithSemicolon)-1)-1)&'"'&Mid([@1:n],Index([@1:n],';',SubStringCount([@1:n],';')+$(vColumnWithSemicolon)-$(vColumns)+1))&';'&(RecNo()-1) as line
FROM QlikCommunity_Thread_202456_v2.csv (fix, codepage is 1252)
Where RecNo()>1;

// load separate fields from previously loaded field "line"
// This load creates field names @1 to @n (n being the ID/RecNo field)
table2:
LOAD *
From_Field (table1, line) (txt, codepage is 1252, no labels, delimiter is ';', msq);

// calculate fieldnumber of ID field
LET vIDColumn = $(vColumns)+1;

// create temporary table having columns for old and new field names (header row of csv file + ID field)
tabFieldNames:
CrossTable (oldFieldName,newFieldName)
LOAD 1,*,'ID' as @$(vIDColumn)
FROM QlikCommunity_Thread_202456_v2.csv (txt, codepage is 1252, no labels, delimiter is ';', msq)
Where RecNo()=1;

// create mapping table to translate default field names (@1 to @n) to column headers of csv file
mapFieldNames:
Mapping LOAD oldFieldName, newFieldName
Resident tabFieldNames;

// drop temporary tables
DROP Tables table1, tabFieldNames;

// rename fields using the previously generated mapping table
RENAME Fields using mapFieldNames;

hope this helps

regards

Marco

Re: Semicolon in Data Source - How to load the data properly?

Fri, 29 Jan 2016 15:48:42 GMT

Hi thank you for the explanation!

I attached a sample file for the encoding issue.

Re: Semicolon in Data Source - How to load the data properly?

MarcoWedel — Fri, 29 Jan 2016 23:16:03 GMT

Hallo Sebastian,

utf8 in the from_field load seems to work with umlauts:

// defining the number of the column that might contain semicolons
LET vColumnWithSemicolon = 5;
LET vColumns = 20;

// load whole lines of csv file without headers and adding double quotes around values of above specified column
// quoted semicolons will not be treated as delimiters. Append Record numbers as additional column to let lines be distinct
table1:
LOAD Left([@1:n],Index([@1:n],';',$(vColumnWithSemicolon)-1))&'"'&Mid([@1:n],Index([@1:n],';',$(vColumnWithSemicolon)-1)+1,Index([@1:n],';',SubStringCount([@1:n],';')+$(vColumnWithSemicolon)-$(vColumns)+1)-Index([@1:n],';',$(vColumnWithSemicolon)-1)-1)&'"'&Mid([@1:n],Index([@1:n],';',SubStringCount([@1:n],';')+$(vColumnWithSemicolon)-$(vColumns)+1))&';'&(RecNo()-1) as line
FROM [https://community.qlik.com/servlet/JiveServlet/download/963714-208647/sample.csv] (fix, codepage is 1252)
Where RecNo()>1;

// load separate fields from previously loaded field "line"
// This load creates field names @1 to @n (n being the ID/RecNo field)
table2:
LOAD *
From_Field (table1, line) (txt, utf8, no labels, delimiter is ';', msq);

// calculate fieldnumber of ID field
LET vIDColumn = $(vColumns)+1;

// create temporary table having columns for old and new field names (header row of csv file + ID field)
tabFieldNames:
CrossTable (oldFieldName,newFieldName)
LOAD 1,*,'ID' as @$(vIDColumn)
FROM [https://community.qlik.com/servlet/JiveServlet/download/963714-208647/sample.csv] (txt, codepage is 1252, no labels, delimiter is ';', msq)
Where RecNo()=1;

// create mapping table to translate default field names (@1 to @n) to column headers of csv file
mapFieldNames:
Mapping LOAD oldFieldName, newFieldName
Resident tabFieldNames;

// drop temporary tables
DROP Tables table1, tabFieldNames;

// rename fields using the previously generated mapping table
RENAME Fields using mapFieldNames;

hope this helps

regards

Marco

Re: Semicolon in Data Source - How to load the data properly?

swuehl — Tue, 02 Feb 2016 15:31:40 GMT

Is the challenge created by Peter still open?

Another solution without a field name mapping could be:

// defining the number of the column that might contain semicolons
LET vColumnWithSemicolon = 5;
LET vColumns = 20;
LET vRight = vColumns - vColumnWithSemicolon; //# semicolons counted from the right 
LET vLeft = vColumnWithSemicolon-1; //# of semicolons counted from the left


// load whole lines of csv file without headers and adding double quotes around values of above specified column
// quoted semicolons will not be treated as delimiters. Append lines to single LINE
table1:
LOAD
CONCAT(line, chr(10)&chr(13) ,LineNo) as LINE;
LOAD Left([@1:n],Index([@1:n],';',$(vColumnWithSemicolon)-1))&'"'&Mid([@1:n],Index([@1:n],';',$(vColumnWithSemicolon)-1)+1,Index([@1:n],';',-$(vRight))-Index([@1:n],';',$(vLeft))-1) &'"'&Mid([@1:n],Index([@1:n],';',-$(vRight))) as line,
recno() as LineNo
FROM [https://community.qlik.com/servlet/JiveServlet/download/963714-208647/sample.csv] (fix, codepage is 1252)
//Where RecNo()>1
;

// load separate fields from previously loaded field "LINE"
// This load uses embedded field names
table2:
LOAD *
From_Field (table1, LINE) (txt, utf8, embedded labels, delimiter is ';', msq);

Re: Semicolon in Data Source - How to load the data properly?

Peter_Cammaert — Tue, 02 Feb 2016 16:10:26 GMT

Very nice. an adventurous spirit came up with a trick to treat header lines just like everything else. Why didn't I think of this?

Great improvement, Stefan.