Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

jcampbell
Contributor

Parse CSV Data on Load

I need to parse data being imported from a CSV file. I have one column storing multiple values I need to break out as separate fields in QlikView. Basically the format would look like this (second column in file):

ProjectSmiley Very Happyepartment:Task

Example values: "ConsultingSmiley Frustratedales:WebEx"

I need to capture the values "Consulting", "Sales", and "WebEx" and store them in different fields for analysis in QlikView. I would prefer that this be done at the time of load. Is there any way to break out the data using ":" as a delimiter?

Thanks!

1 Solution

Accepted Solutions
jcampbell
Contributor

Re: Parse CSV Data on Load

Good suggestion...I just figured out how to use subfield with it. Here is what I have:

subfield(Project,':',1)  as Project,

subfield(Project,':',2)  as Department,

subfield(Project,':',3)  as Task,

4 Replies

Re: Parse CSV Data on Load

Hi

Try  like this

Load * from file.csv    

(txt, codepage is 1252, embedded labels, delimiter is ':', msq);

Hope that helps

jcampbell
Contributor

Re: Parse CSV Data on Load

The file is a proper comma delimited file. It's just a single value in the file that contains a ":" to separate three different data elements that I want to appear in QlikView as three distinct column values within each row of data loaded. Changing the delimiter to a ":" will cause the rest of the data to not be imported.

Re: Parse CSV Data on Load

HI

LOAD *,

     [ProjectSmiley Very Happyepartment:Task],

     SubField([ProjectSmiley Very Happyepartment:Task],':',1) AS Project,

     SubField([ProjectSmiley Very Happyepartment:Task],':',2) AS Department,

     SubField([ProjectSmiley Very Happyepartment:Task],':',3) AS Task

FROM

FileName.csv

(txt, codepage is 1252, embedded labels, delimiter is ',');

Hope it helps

jcampbell
Contributor

Re: Parse CSV Data on Load

Good suggestion...I just figured out how to use subfield with it. Here is what I have:

subfield(Project,':',1)  as Project,

subfield(Project,':',2)  as Department,

subfield(Project,':',3)  as Task,