Qlik Community

Qlik Sense App Development

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

vardhancse
Valued Contributor III

Add Text Qualifier - CSV extract

Hi,

I have one csv with delimiter';', used below script to load

-----------------------------------------------------------------------------------------

Sample:

Load 

ID,

EMP_Name,

Location,

LM,

Company

FROM

[\\path\sample.csv]

(txt, utf8, embedded labels, delimiter is ';', no quotes);

--------------------------------------------------------------------------------------

as mentioned above file delimiter is ';'. But issue is :

--------------------------------------------------------------------------------------

Field Name: Location

Data: Asia;china;Shanghai

Expected output:

IDEMP_NameLocationLMCompany
1abcAsia;China;ShanghaiLM_1Lenovo
2bcdAsia;Australia;MelbourneLM_2HP

 

------------------------------------------------------------------------------------------------------

But result post reloading my script was:2 new additional fields were creating and moreover next fields like "LM" & "Company" were getting impacted

IDEMP_NameLocationLMCompany  
1abcAsiaChinaShanghaiLM_1Lenovo
2bcdAsiaAustraliaMelbourneLM_2HP

 

can any one let me know do we have option like add text qualifier like e.g; "Asia;China;Shanghai"

7 Replies
agni_gold
Valued Contributor II

Re: Add Text Qualifier - CSV extract

MVP
MVP

Re: Add Text Qualifier - CSV extract

If you have the semicolon both as a column/field delimiter and it serves as a delimiter within a column/field you really have an inconsistent file. However if the last two columns/fields always has values you could load the file without any delimiter at all and then in your load statement split up each field by using subfield:

TEST:
LOAD
  SubField(@1,';',1) AS ID,
  SubField(@1,';',2) AS EMP_Name,
  TextBetween( @1 , SubField(@1,';',1) & ';' & SubField(@1,';',2) & ';' , ';' &   SubField(@1,';',-2) & ';' & SubField(@1,';',-1)) AS Location,
  SubField(@1,';',-2) AS LM,
  SubField(@1,';',-1) AS Company

INLINE [
  ID;EMP_Name;Location;LM;Company
  1;abc;Asia;China;Shanghai;LM_1;Lenovo
  2;bcd;Asia;Australia;Melbourne;LM_2;HP
] (header is 1 line, no labels);

 

2019-01-18 12_51_02-QlikView x64 Personal Edition - [C__Users_psk_Documents_CRS.qvw_].png

Re: Add Text Qualifier - CSV extract

Would you be able to share your csv file with us here? with 2 rows of data should be enough to test

Highlighted
vardhancse
Valued Contributor III

Re: Add Text Qualifier - CSV extract

Hi Sunny,

unfortunately I was unable to attach CSV/txt file due to community limitation.

Can paste below data into notepad, save to .txt and then save it to .csv (encoding - UTF -8)

ID;EMP_Name;Location;LM;Company
1;abc;Asia;China;Shanghai;LM_1;Lenovo
2;bcd;Asia;Australia;Melbourne;LM_2;HP

 

vardhancse
Valued Contributor III

Re: Add Text Qualifier - CSV extract

@sunny_talwar Can please let me know did you got a chance to look into my issue

Re: Add Text Qualifier - CSV extract

This might be the best way to do it... but here is one way you can achieve it

Table:
LOAD ID, 
     EMP_Name, 
     Location&';'&LM&';'&Company as Location
FROM
[Add Text Qualifier - CSV extract.csv]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);


Left Join (Table)
LOAD SubField([ID;EMP_Name;Location;LM;Company], ';', 1) as ID,
	 SubField([ID;EMP_Name;Location;LM;Company], ';', -1) as Company,
	 SubField([ID;EMP_Name;Location;LM;Company], ';', -2) as LM
FROM
[Add Text Qualifier - CSV extract.csv]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
vardhancse
Valued Contributor III

Re: Add Text Qualifier - CSV extract

thanks Sunny, will check and let you know