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: 
jleefjcapital
Creator II
Creator II

Loading a pipe-delimited text file with a separate data file via Qlik Sense

I need to build a relational database using a schema file and the actual data file that are both pipe delimited text files.  Does anyone know how I can go about doing this?

Thank you,

Jessica

1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

This should work for you:

SchemaFile:

MAPPING LOAD

    '@' & FieldNo AS DataFileField,

    FieldName AS FinalFieldName

// FROM SchemaFile.TXT (delimiter is '|');

INLINE [

FieldNo|FieldName

1|TableName

2|Attribute

3|Value

4|Category

] (delimiter is '|');

DataFile:

LOAD

    *

// FROM DataFile.TXT (delimiter is '|' , no labels);

INLINE [

T1|color|red|a

T1|diameter|10 cm|b

T1|weight|100 g|c

T2|Color|black|d

T2|height|16 cm|e

T3|length|20 cm|f

T3|weight|500 g|g

T3|width|10 cm|h

] (delimiter is '|', no labels);

RENAME FIELDS USING SchemaFile;

2015-05-21 #1.PNG

View solution in original post

21 Replies
petter
Partner - Champion III
Partner - Champion III

‌Could you please explain what you mean by "Relational Database" .... Is it an associative database you are thinking about?

jleefjcapital
Creator II
Creator II
Author

I think so.  I'm thinking of a standard, column and row format.  Is that associative? 

JonnyPoole
Employee
Employee

sure , when you go to load the text file, just switch the delimitter. A pipe is #127 in the ascii list of characters:

Untitled.png

petter
Partner - Champion III
Partner - Champion III

So you have the column names in the schema file then and only the values for each column in the actual data file right?

petter
Partner - Champion III
Partner - Champion III

This should work for you:

SchemaFile:

MAPPING LOAD

    '@' & FieldNo AS DataFileField,

    FieldName AS FinalFieldName

// FROM SchemaFile.TXT (delimiter is '|');

INLINE [

FieldNo|FieldName

1|TableName

2|Attribute

3|Value

4|Category

] (delimiter is '|');

DataFile:

LOAD

    *

// FROM DataFile.TXT (delimiter is '|' , no labels);

INLINE [

T1|color|red|a

T1|diameter|10 cm|b

T1|weight|100 g|c

T2|Color|black|d

T2|height|16 cm|e

T3|length|20 cm|f

T3|weight|500 g|g

T3|width|10 cm|h

] (delimiter is '|', no labels);

RENAME FIELDS USING SchemaFile;

2015-05-21 #1.PNG

petter
Partner - Champion III
Partner - Champion III

I didn't explain much - just ask if anything is unclear...

jleefjcapital
Creator II
Creator II
Author

Thank you.  I will try this and let you know if I get stuck and if I'm successful!

petter
Partner - Champion III
Partner - Champion III

The comments // is what you should remove so you load from your files instead and then comment out lines 7-13 and 20-29 ...

jleefjcapital
Creator II
Creator II
Author

Oh, I don't have to manually input the schema?   Whew.