Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jleefjcapital
Contributor

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
MVP
MVP

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

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

21 Replies
MVP
MVP

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

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

jleefjcapital
Contributor

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

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

Employee
Employee

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

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

MVP
MVP

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

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

MVP
MVP

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

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

MVP
MVP

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

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

jleefjcapital
Contributor

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

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

MVP
MVP

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

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
Contributor

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

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

Community Browser