Using a LOOP to load multiple .dbf files

[Anonymous]

Hi - I'm new to this, but have been able to load multiple Excel files using a LOOP, and this works well.

However, when I try to apply the same format of loop to using 'SQL SELECT *' from multiple .dbf files, I just can't get it to work.  All files are in the same directory.

Any advice would be appreciated.

Thanks,

Steven

[afurtado]

Do you have an ODBC driver ?

[Oleg_Troyansky]

In a nutshell, you need to loop through the file names in the folder and then use the loop variable (File name) within your CONNECT commands. Once you connect to the desired dbf file, you can use the same SELECT statement to load the data from that database.

cheers,

Oleg Troyansky

Take your Qlik Skills to the next level at the Masters Summit for Qlik - now with new and redesigned materials!

[Anonymous]

Yes - thanks - see following.

[Anonymous]

Many thanks - I think that's what I'm trying with this script, but get the below error message:

****

DIRECTORY C:\USERS\STEVEN\DOCUMENTS\QLIK\QLIKVIEW DEMO\FARSDBF;

FOR Each vFile in FileList ('*.dbf');

ODBC CONNECT32 TO FARS;

SQL SELECT *

FROM $(vFile);

Next vFile;

****

SQL##f - SqlState: 37000, ErrorCode: 4294963790, ErrorMsg: [Microsoft][ODBC dBase Driver] Syntax error in FROM clause.

SQL SELECT *

FROM C:\USERS\STEVEN\DOCUMENTS\QLIK\QLIKVIEW DEMO\FARSDBF\acc_1975.dbf

Thanks again......

[Peter_Cammaert]

A SQL SELECT * does not read from multiple database files. It pulls data from an active database connection. A single one at any moment. To read from multiple databases, you need to redirect your connection from the previous to the next database before trying to read from it. Put your CONNECT statement in the LOOP body, as Oleg already pointed out.

If we are missing the point, please do provide some more details about what exactly doesn't work. Do you get any error messages? What does your current script look like?

Thanks, and a happy New Year to everone.

[Anonymous]

Hi - yes, thanks, here's the script and the error message:

****

DIRECTORY C:\USERS\STEVEN\DOCUMENTS\QLIK\QLIKVIEW DEMO\FARSDBF;

FOR Each vFile in FileList ('*.dbf');

ODBC CONNECT32 TO FARS;

SQL SELECT *

FROM $(vFile);

Next vFile;

****

SQL##f - SqlState: 37000, ErrorCode: 4294963790, ErrorMsg: [Microsoft][ODBC dBase Driver] Syntax error in FROM clause.

SQL SELECT *

FROM C:\USERS\STEVEN\DOCUMENTS\QLIK\QLIKVIEW DEMO\FARSDBF\acc_1975.dbf

[Oleg_Troyansky]

It's hard to guess what's wrong without having the database and the drivers... Here are a couple of pointers:

1. Try dropping the path and the extension ".dbf" - it looks like DBase ODBC driver assumes the DBF extension automatically.

2. Create the same CONNECT and SELECT statements manually and see how they look like. Then, try to mimic the same structure in your automated loop.

3. Try using ODBC command SQLTABLES and then loop through the list of tables instead of the list of files - it might work smoother (assuming the DBase ODBC supports SQLTABLES). You can find advanced examples of using SQLTABLES in my new book QlikView Your Business.

cheers,

Oleg Troyansky

Take your Qlik Skills to the next level at the Masters Summit for Qlik - now with new and redesigned materials!

[manishchandra_j]

How does your script know that which table it has to read?

[Peter_Cammaert]

dBase files contain only one table.