Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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

Project:Department:Task

Example values: "Consulting:Sales: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
Highlighted
Partner
Partner

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,

View solution in original post

4 Replies
Highlighted

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

Highlighted
Partner
Partner

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.

Highlighted

Re: Parse CSV Data on Load

HI

LOAD *,

     [Project:Department:Task],

     SubField([Project:Department:Task],':',1) AS Project,

     SubField([Project:Department:Task],':',2) AS Department,

     SubField([Project:Department:Task],':',3) AS Task

FROM

FileName.csv

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

Hope it helps

Highlighted
Partner
Partner

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,

View solution in original post