Qlik Community

Qlik Sense App Development

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

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist III
Specialist 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
Highlighted
Highlighted
MVP
MVP

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

Highlighted

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

Highlighted
Specialist III
Specialist III

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

 

Highlighted
Specialist III
Specialist III

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

Highlighted

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);
Highlighted
Specialist III
Specialist III

thanks Sunny, will check and let you know