topic Re: Add Text Qualifier - CSV extract in App Development

Add Text Qualifier - CSV extract

vardhancse — Sat, 16 Nov 2024 21:34:49 GMT

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:

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

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

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

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

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

Re: Add Text Qualifier - CSV extract

agni_gold — Fri, 18 Jan 2019 11:40:11 GMT

Check this

https://community.qlik.com/t5/QlikView-Scripting/Semicolon-in-Data-Source-How-to-load-the-data-properly/td-p/1079645

Re: Add Text Qualifier - CSV extract

petter — Fri, 18 Jan 2019 11:53:35 GMT

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

Re: Add Text Qualifier - CSV extract

sunny_talwar — Fri, 18 Jan 2019 12:39:15 GMT

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

Re: Add Text Qualifier - CSV extract

vardhancse — Fri, 18 Jan 2019 18:06:46 GMT

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

Re: Add Text Qualifier - CSV extract

vardhancse — Tue, 22 Jan 2019 10:16:05 GMT

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

Re: Add Text Qualifier - CSV extract

sunny_talwar — Tue, 22 Jan 2019 12:18:56 GMT

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

Re: Add Text Qualifier - CSV extract

vardhancse — Mon, 04 Feb 2019 07:11:06 GMT

thanks Sunny, will check and let you know