topic Re: Parse CSV Data on Load in QlikView

Parse CSV Data on Load

jcampbell — Mon, 17 Sep 2012 15:36:31 GMT

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!

Re: Parse CSV Data on Load

MayilVahanan — Mon, 17 Sep 2012 15:42:30 GMT

Try like this

Load * from file.csv

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

Hope that helps

Re: Parse CSV Data on Load

jcampbell — Mon, 17 Sep 2012 17:50:03 GMT

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

MayilVahanan — Mon, 17 Sep 2012 18:12:10 GMT

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

Re: Parse CSV Data on Load

jcampbell — Mon, 17 Sep 2012 18:18:21 GMT

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,