Loading from Multiple excel files

[#1]

Dear Experts,

         I would like to ask you for multiple loading excel files. I have daily downloaded data and I would like to load these data by each date by writing scripts. While I am using filename() funtion and it shows the error.   Here is my script and error message in attached file.I am looking forward your help.

Daily_Tr:

LOAD

     filename() as FileName,

     date(date#(mid(filename(), 12, 8), 'YYYYMMDD'), 'DD MM YYYY') as Tr_Date,

     [Sr No],

     [Ac No],

     [Old Ac No],

     [Opening Balance],

     Debit,

     Credit,

     [Closing Balance]

FROM

(ooxml, embedded labels, table is [20150601]);

Warm Regards,

Kyaw Myo Tun

[Siva_Sankar]

In File DailyTrans_01062015.xlsx, the  [Sr No] column is missing... check and fix the excel file

[#3]

Hi,

As per the screen shot the column Sr No is missing in the excel.

[jagan · Accepted solution]

Hi,

In one of the file [Sr No]  column is missing, to skip this error load the files like below

Daily_Tr:

LOAD

     filename() as FileName,

     date(date#(mid(filename(), 12, 8), 'YYYYMMDD'), 'DD MM YYYY') as Tr_Date,

    *

FROM

(ooxml, embedded labels, table is [20150601]);

Hope this helps you.

Regards,

Jagan.

[Siva_Sankar]

Kyaw,

Open the file DailyTrans_01062015.xlsx and see the Sr No column is there or not or check if there is any typo.

[#6]

Hi Jagan,

           Thanks for your help.The error is fixed but it only show a date that I loaded recently. I would like to load further 02062015,03062015 etc. what I've downloaded daily.

Regards,

Kyaw Myo Tun

[#7]

Hi Jemimah,

      I've already checked my excel file and it seems correct. if I take out that [Sr No] and the error show [Ac No] is not found.

Regards,

Kyaw Myo Tun

[jagan]

Hi,

You are loading only one sheet , refer sheet name highlighted in Bold letters.

Daily_Tr:

LOAD

     filename() as FileName,

     date(date#(mid(filename(), 12, 8), 'YYYYMMDD'), 'DD MM YYYY') as Tr_Date,

    *

FROM

(ooxml, embedded labels, table is [20150601]);

To load multiple sheets you need to look through all the sheets, please refer sample script in the below link

Load Multiple excel sheets using For loop

Regards,

jagan.

[senpradip007]

You can indeed use ODBC CONNECT 32 to load the table names using SQLTABLES, but I don't think you will be able to wildcard load, rather loop over the files using For Each vFile in FileList(vPath).

Something like:

ForEach vFile in FileList('$(vPath)')

     ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];

     Sheets:

     SQLTABLES;

     DISCONNECT;

     For i = 0 To NoOfRow('Sheets')

          Let zSheet = Peek('TABLE_NAME, i, 'Sheets');

          ... do something with the file and sheet name, eg

          LOAD ....

          From [$(vFile)]

          (ooxml, no labels, table is [$(zSheet)]);

     Next

Next

[jagan]

HI,

If you got the answer close this thread by giving Correct Answer to the post which helps you in finding the solution.

Regards,

Jagan.