topic Re: Using a LOOP to load multiple .dbf files in QlikView

Using a LOOP to load multiple .dbf files

Anonymous — Fri, 01 Jan 2016 14:27:59 GMT

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

Re: Using a LOOP to load multiple .dbf files

afurtado — Fri, 01 Jan 2016 19:43:24 GMT

Do you have an ODBC driver ?

Re: Using a LOOP to load multiple .dbf files

Oleg_Troyansky — Fri, 01 Jan 2016 22:10:26 GMT

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!

Re: Using a LOOP to load multiple .dbf files

Anonymous — Sat, 02 Jan 2016 11:14:28 GMT

Yes - thanks - see following.

Re: Using a LOOP to load multiple .dbf files

Anonymous — Sat, 02 Jan 2016 11:17:21 GMT

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......

Re: Using a LOOP to load multiple .dbf files

Peter_Cammaert — Sat, 02 Jan 2016 11:52:13 GMT

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.

Re: Using a LOOP to load multiple .dbf files

Anonymous — Sat, 02 Jan 2016 12:56:20 GMT

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

Re: Using a LOOP to load multiple .dbf files

Oleg_Troyansky — Sat, 02 Jan 2016 16:47:49 GMT

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!

Re: Using a LOOP to load multiple .dbf files

manishchandra_j — Sat, 02 Jan 2016 18:26:52 GMT

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

Re: Using a LOOP to load multiple .dbf files

Peter_Cammaert — Sat, 02 Jan 2016 19:37:21 GMT

dBase files contain only one table.

Re: Using a LOOP to load multiple .dbf files

Peter_Cammaert — Sat, 02 Jan 2016 20:01:16 GMT

[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

Re: Using a LOOP to load multiple .dbf files

MarcoWedel — Sat, 02 Jan 2016 23:58:38 GMT

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;

using this System-DSN:

hope this helps

regards

Marco

Re: Using a LOOP to load multiple .dbf files

Peter_Cammaert — Sun, 03 Jan 2016 00:05:04 GMT

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

Re: Using a LOOP to load multiple .dbf files

MarcoWedel — Sun, 03 Jan 2016 00:21:42 GMT

Hi,

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

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

Re: Using a LOOP to load multiple .dbf files

Peter_Cammaert — Sun, 03 Jan 2016 00:28:28 GMT

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

Re: Using a LOOP to load multiple .dbf files

MarcoWedel — Sun, 03 Jan 2016 00:33:24 GMT

you're welcome.

Goodnight

Marco

Re: Using a LOOP to load multiple .dbf files

Anonymous — Sun, 03 Jan 2016 10:11:31 GMT

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

Re: Using a LOOP to load multiple .dbf files

Anonymous — Sun, 03 Jan 2016 10:11:40 GMT

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

Regards to all,

Steven

Re: Using a LOOP to load multiple .dbf files

Anonymous — Sun, 03 Jan 2016 10:12:03 GMT

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

Regards to all,

Steven

Re: Using a LOOP to load multiple .dbf files

Peter_Cammaert — Sun, 03 Jan 2016 14:13:00 GMT

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