Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jcampbell
Partner - Creator
Partner - Creator

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
jcampbell
Partner - Creator
Partner - Creator
Author

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
MayilVahanan

Hi

Try  like this

Load * from file.csv    

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

Hope that helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
jcampbell
Partner - Creator
Partner - Creator
Author

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.

MayilVahanan

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
jcampbell
Partner - Creator
Partner - Creator
Author

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,