Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
anuradhaa
Partner - Creator II
Partner - Creator II

Load Data from Multiple CSV files

I'm Using below scrip to load data ,

[aaa_Temp]:

NoConcatenate

LOAD If(Len(Trim(Deadline)) = 0, Alt(Peek('Flag'), 1), RangeSum(Peek('Flag'), 1)) as Flag, 

     Id,     

     Title,

     Labels

   

FROM

[aaa_*.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

[aaa]:

NoConcatenate

LOAD

  Id,

     Title,

     Labels,   

  If(Len(Trim(Deadline)) = 0, Peek('Deadline'), Deadline) as Deadline

Resident aaa_Temp

Order By Flag desc, Deadline;

DROP Table aaa_Temp;

Here I have multiple CSV files as aaa_1,aa_2 etc.

So when i load data it create multiple aaa_temp table.

How can i load multiple files and create single aaa_temp table

1 Solution

Accepted Solutions
sunny_talwar

Try this:

[aaa_Temp]:

LOAD If(Len(Trim(Deadline)) = 0, Alt(Peek('Flag'), 1), RangeSum(Peek('Flag'), 1)) as Flag,

     Id,   

     Title,

     Labels

FROM

[aaa_*.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

[aaa]:

NoConcatenate

LOAD

  Id,

     Title,

     Labels, 

  If(Len(Trim(Deadline)) = 0, Peek('Deadline'), Deadline) as Deadline

Resident aaa_Temp

Order By Flag desc, Deadline;

DROP Table aaa_Temp;

[bbb_Temp]:

LOAD If(Len(Trim(Deadline)) = 0, Alt(Peek('Flag'), 1), RangeSum(Peek('Flag'), 1)) as Flag,

     Id,   

     Title,

     Labels

FROM

[bbb_*.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

[bbb]:

NoConcatenate

LOAD

  Id,

     Title,

     Labels, 

  If(Len(Trim(Deadline)) = 0, Peek('Deadline'), Deadline) as Deadline

Resident aaa_Temp

Order By Flag desc, Deadline;

DROP Table bbb_Temp;

or use Loop as mentioned here:

Loading Multiple Excel Sheets Dynamically along with file name and sheet name

Load multiple sheets and excel files

View solution in original post

5 Replies
sunny_talwar

Remove NoConcatenate to create just one table:

[aaa_Temp]:

NoConcatenate

LOAD If(Len(Trim(Deadline)) = 0, Alt(Peek('Flag'), 1), RangeSum(Peek('Flag'), 1)) as Flag,

     Id,   

     Title,

     Labels

FROM

[aaa_*.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

anuradhaa
Partner - Creator II
Partner - Creator II
Author

I have it to load multiple csvs like

[aaa_Temp]:

NoConcatenate

LOAD If(Len(Trim(Deadline)) = 0, Alt(Peek('Flag'), 1), RangeSum(Peek('Flag'), 1)) as Flag,

     Id,    

     Title,

     Labels

  

FROM

[aaa_*.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

[aaa]:

NoConcatenate

LOAD

  Id,

     Title,

     Labels,  

  If(Len(Trim(Deadline)) = 0, Peek('Deadline'), Deadline) as Deadline

Resident aaa_Temp

Order By Flag desc, Deadline;

DROP Table aaa_Temp;

[bbb_Temp]:

NoConcatenate

LOAD If(Len(Trim(Deadline)) = 0, Alt(Peek('Flag'), 1), RangeSum(Peek('Flag'), 1)) as Flag,

     Id,    

     Title,

     Labels

  

FROM

[bbb_*.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

[bbb]:

NoConcatenate

LOAD

  Id,

     Title,

     Labels,  

  If(Len(Trim(Deadline)) = 0, Peek('Deadline'), Deadline) as Deadline

Resident aaa_Temp

Order By Flag desc, Deadline;

DROP Table bbb_Temp;

sunny_talwar

Try this:

[aaa_Temp]:

LOAD If(Len(Trim(Deadline)) = 0, Alt(Peek('Flag'), 1), RangeSum(Peek('Flag'), 1)) as Flag,

     Id,   

     Title,

     Labels

FROM

[aaa_*.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

[aaa]:

NoConcatenate

LOAD

  Id,

     Title,

     Labels, 

  If(Len(Trim(Deadline)) = 0, Peek('Deadline'), Deadline) as Deadline

Resident aaa_Temp

Order By Flag desc, Deadline;

DROP Table aaa_Temp;

[bbb_Temp]:

LOAD If(Len(Trim(Deadline)) = 0, Alt(Peek('Flag'), 1), RangeSum(Peek('Flag'), 1)) as Flag,

     Id,   

     Title,

     Labels

FROM

[bbb_*.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

[bbb]:

NoConcatenate

LOAD

  Id,

     Title,

     Labels, 

  If(Len(Trim(Deadline)) = 0, Peek('Deadline'), Deadline) as Deadline

Resident aaa_Temp

Order By Flag desc, Deadline;

DROP Table bbb_Temp;

or use Loop as mentioned here:

Loading Multiple Excel Sheets Dynamically along with file name and sheet name

Load multiple sheets and excel files

swuehl
MVP
MVP

As Sunny said, the NOCONCATENATE is forcing QV to create separate tables.

Any reason why you are using it here? If you have a table create earlier with the exact same four fields, you can try

[aaa_Temp]:

LOAD If(Len(Trim(Deadline)) = 0, Alt(Peek('Flag'), 1), RangeSum(Peek('Flag'), 1)) as Flag,

     Id,   

     Title,

     Labels,

     1 as aaa_Temp 

FROM

[aaa_*.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

[aaa]:

NoConcatenate

LOAD

  Id,

     Title,

     Labels, 

  If(Len(Trim(Deadline)) = 0, Peek('Deadline'), Deadline) as Deadline

Resident aaa_Temp

Order By Flag desc, Deadline;

DROP Table aaa_Temp;

i.e. remove the NOCONCATENATE and create a field that makes this table unique in field names.

Not really sure why you are not loading all *_Temp tables into one and then make a single final resident load though.