Loading Multiple CSV File using for loop

[muniyandi]

Hi Team,

i have to load multiple csv file in single ORDER table .

ex:

I have 4 csv file in my order folder, i want the result in order table should have all the 4 file data.

ORD_AUG_2016.csv

ORD_SEP_2016.csv

ORD_OCT_2016.csv

ORD_NOV_2016.csv

pls suggest best option to load all the data.

Thanks,

Muni

[zhadrakas]

try

FOR EACH file in filelist('C:YOURPATH\*.csv') ;
Load * from '$(file)'   (txt, utf8, no labels, delimiter is ' ', msq);
NEXT file

[kenphamvn · Accepted solution]

Hi

Try this

ORDER:
Load *
from 'filepath\ORD_*.csv;

regards

An Pham

[muniyandi]

This script is not working

[zhadrakas]

probably you Need to edit this paramters to your needs

(txt, utf8, no labels, delimiter is ' ', msq)

but you dont really Need to do that in a Loop. Look at "An Pham's" Solution

[muniyandi]

Hi An Pham,

your code has been working , i need to add file_name column also (tracking purpose )

Thanks,

[kenphamvn]

Hi

if you want add file_name column you need using Loop condition like Tim Driller Solution

Set v_Concatenate ='';
FOR EACH file in filelist('D:\Temp\CSV\ORD_*.csv') ; 
$(v_Concatenate)  
ORDER:  
Load *, Right('$(file)',16) as file_name 
from 
'$(file)'   (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
set v_Concatenate ='Concatenate';  
NEXT file

Regards

An Pham