Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
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