Multiple Excel sheets without ODBC/OLEDB connection

[vardhancse]

Hi,

Can any one please let me know option to load multiple excel sheets(Having multiple sheets) with out using ODBC connection

[avinashelite]

share the sample app and the excel files ...Is the app running fine in local ?

[vardhancse]

In local system while reloading its throwing an error" _xlnm#_FilterDatabase."

changed the file format to .xls and tried but no luck

[avinashelite]

Share the sample app and excel let me check once

[cwolf]

You can use a macro to get the sheet names:

VB Macro:

function GetSheetNames(pExcelFile)

    set ExApp = CreateObject("Excel.Application")

    ExApp.Visible = False

    set ExWrk = ExApp.Workbooks.Open(pExcelFile)

    s = ""

    for i = 1 to ExWrk.Sheets.Count

        if i = ExWrk.Sheets.Count then

            s = s + "'" + ExWrk.Sheets(i).Name + "'"

        else

            s = s + "'" + ExWrk.Sheets(i).Name + "',"

        end if

    next

    ExWrk.Close

    ExApp.Quit

    GetSheetNames=s

end function

Script:

FOR Each file in FileList('$(Filepath)\*.xlsx');

    let sheetNames=GetSheetNames('$(file)');

    FOR Each sheetName in $(sheetNames)

        If Wildmatch('$(sheetName)', 'Region*','Area*') Then

            Table:

            Load *

            From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

        end if

    next

next file

[vinieme12]

Read this thread

The Great ODBC Confusion

ODBC 64 giving an "architecture mismatch" error

[tamilarasu]

Hi Sasi,

Have you tried to add "IF" statement like I said yesterday? Looks like you haven't try.. Let me try for you.

**Just change the file path
FOR EACH file in FileList('Filepath\*.xlsx');
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:
SQLtables;
DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1
Let vSheetName = Replace(Peek('TABLE_NAME', $(i), 'tables'),chr(39),'');

If(WildMatch(vSheetName,'*$')) Then
LET vSheetName = Replace(Replace(Peek('TABLE_NAME', $(i), 'tables'),'$',''),chr(39),'');

If Wildmatch(vSheetName, 'Region*','Area*') Then
Table:
Load *
From $(file)(ooxml, embedded labels, table is [$(vSheetName)]);
ENDIF

ENDIF
NEXT i

Drop table tables;
Next file

[vardhancse]

Hi Nagaraj,

thank you so much using ODBC i'm looking for similar if condition as well.

But now identified one more issue is that "File extdata.cpp, Line 2291". can please let me know any reason for these kind of issues

[prieper]

Error: File extdata.cpp, Line 2903

[tamilarasu]

Sasi - Could you post your log file here?

[vardhancse]

Hi Nagaraj,

can please let me know any alternative other than using ODBC connection.

Because in our server we don't have an excel driver and so.