Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Using a LOOP to load multiple .dbf files

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

22 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

[Edit] I removed my previous remarks. Apparently the ODBC DSN specifies the directory and the FROM clause specifies the table.

To get a single CONNECT/SELECT statement set working, you can use a different technique. Go to an empty part of your script and first click the Connect button at the bottom, enter the requested info, and next click the Select button. The script editor will use the open connection to talk to the driver to figure out what can be fetched from this connection. In the dialog that opens next, you'll be able to see and select from the tables and columns that are available.

You can repeat this step for a number of tables and then wrap them into a loop.

BTW you can specify the from clause like:

:

FROM "$(vFile)";

to compensate for spaces and other problematic characters in file paths (for example between QLIKVIEW and DEMO).

Best,

Peter

MarcoWedel

Hi,

some similar code seems to work for me:

ODBC CONNECT32 TO DBF_ODBC_DSN;

FOR Each vDBFfile in filelist('*.dbf')

    Trace Loading File: $(vDBFfile);

    $(vDBFfile):
    SQL Select * From $(vDBFfile);

NEXT vDBFfile;

QlikCommunity_Thread_198126_Pic1.JPG

QlikCommunity_Thread_198126_Pic2.JPG

using this System-DSN:

QlikCommunity_Thread_198126_Pic3.JPG

QlikCommunity_Thread_198126_Pic4.JPG

hope this helps

regards

Marco

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Marco,

can you do one additional test with the same set of DBF files but this time placed in a path that contains spaces (and without quoting $(vDBFfile))? Thanks

MarcoWedel

Hi,

you're right, it's throwing a similar message:

QlikCommunity_Thread_198126_Pic5.JPG

ODBC CONNECT32 TO DBF_ODBC_DSN2;

FOR Each vDBFfile in filelist('*.dbf')

    Trace Loading File: $(vDBFfile);

    [$(vDBFfile)]:
SQL Select * From $(vDBFfile);

NEXT vDBFfile;

Steven's folder "QLIKVIEW DEMO" might cause the issue.

regards

Marco

Peter_Cammaert
Partner - Champion III
Partner - Champion III

So using double quotes around your variable expansion will probably fix the issue, and Steven's as well.

Big thanks for doing this test. I was still fighting the ODBC configuration of dBase files. Awful, but simple now that I know how to do this.

Goodnight,

Peter

MarcoWedel

you're welcome.

Goodnight

Marco

Anonymous
Not applicable
Author

Oleg, Peter, Marco - many, many thanks for your engagement and support on this.  It may reveal the early stage of my learning on this to realsie that the solution to my problem was to change my folder name from 'QLIKVIEW DEMO'  to 'QLIKVIEWDEMO' with no space.  Having said this, the discussion and the insights you have all provided has taught me much and has significantly advanced my learning.  THanks again for this.

Oleg - I just downloaded the Kindle version of your book and I fully plan to work through it.

Regards to all,

Steven

Anonymous
Not applicable
Author

Oleg, Peter, Marco - many, many thanks for your engagement and support on this.  It may reveal the early stage of my learning on this to realsie that the solution to my problem was to change my folder name from 'QLIKVIEW DEMO'  to 'QLIKVIEWDEMO' with no space.  Having said this, the discussion and the insights you have all provided has taught me much and has significantly advanced my learning.  THanks again for this.

Oleg - I just downloaded the Kindle version of your book and I fully plan to work through it.

Regards to all,

Steven

Anonymous
Not applicable
Author

Oleg, Peter, Marco - many, many thanks for your engagement and support on this.  It may reveal the early stage of my learning on this to realsie that the solution to my problem was to change my folder name from 'QLIKVIEW DEMO'  to 'QLIKVIEWDEMO' with no space.  Having said this, the discussion and the insights you have all provided has taught me much and has significantly advanced my learning.  THanks again for this.

Oleg - I just downloaded the Kindle version of your book and I fully plan to work through it.

Regards to all,

Steven

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Well the actual solution is hiding in the fact that you shouldn't actually change your environment to match the behavior of your script. Your script should be able to handle any DBF-file in any location. Imagine that someone in the future changes the DBF location back to a path with spaces that is already commonly accepted in Windows since Win9x?

I would suggest to add the double quotes. Makes your document future-proof. Another solution could be to drop the path from the FROM clause altogehter by splitting off the path from the filename before performing the LOAD. But again, a file name may also contain spaces these days, so you'll never be sure that your script won't have to deal with spaces in the FROM clause.

In any case, good luck and enjoy QlikView and the Qlik Community