Load Data from Multiple CSV files

[anuradhaa]

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

[Chanty4u]

did u chk disLoad only new data from multiple csv

Re: Loading different xls and CSV files

[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]

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 · Accepted solution]

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]

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.