Loading multiple excel files with similar structure but minor differences

[Anonymous]

All,

I am trying to load a set of excel files with the exact same field heading structure but with a key difference between two sets of files

All files are in CSV format. For simplicity assume the column headings are, CustomerID, Date, Sales amount, Address, phone, email

1. Some have  '~' as a separator and i use  FROM [lib://path/*.csv] (txt, codepage is 28591, embedded labels, delimiter is '~', msq) to load these

2. For some files however the 'text to column' has allready been done and therefore i cannot use the above 'FROM' statement (i.e. the data is allready in tabular format in the CSV file!)

A. My questions is, can i adapt the script in #1 above o accomodate #2....

B. If A is not possible, is there a way to write two seprate scripts but still get the data into one table

C. IF a and B do not work, can i load into two separate tables and later combine these tables into a single one? How can i do this wihtou generating a synthetic key?

Thanks in advance

[andrey_krylov]

Hi, Avantha. If you know which files with which delimiters to load you can try  this script

for each vFoundFile in filelist( 'lib://path/*.csv')

    vDelimiter = If( SomeCondition, '~' , ' ');

    [Table]: LOAD [List of fields] from [$(vFoundFile)](txt, codepage is 28591, embedded labels, delimiter is '$(vDelimiter)', msq);

next

[Anonymous]

Thanks 'some condition' depended on the filename. How would i write? The file name comes in the fomrat will be Some prefix_2015.csv

If the file has 2013 or 2014 at the end of it it should be ' ' for delimiter other wise '~'

I cant seem to extract the 2013 or 2014 from the the files in the code.

[andrey_krylov]

Ok. Then try this

for each vFoundFile in filelist( 'lib://path/*.csv')

    vDelimiter = If( WildMatch(vFoundFile,  '*_2013.csv', '*_2014.csv'), ' ' , '~');

    [Table]: LOAD [List of fields] from [$(vFoundFile)](txt, codepage is 28591, embedded labels, delimiter is '$(vDelimiter)', msq);

next