topic Re: Visual Fox Pro in QlikView

Visual Fox Pro

pgalvezt — Mon, 10 Sep 2012 12:33:09 GMT

Hi, Currently Im loading manually dbf files. All dbf files has the same structure. I set the fields into the script according customer requirements.

So... Finally Im loading this:

//////Site 1//////
Bases:
OLEDB CONNECT TO [Provider=VFPOLEDB.1;Data Source=e:\datos externos\2011\12_diciembre;Mode=Share Deny None;Extended Properties="";User ID="";Mask Password=False;Cache Authentication=False;Encrypt Password=False;Collating Sequence=MACHINE;DSN="";DELETED=True;CODEPAGE=1252;MVCOUNT=16384;ENGINEBEHAVIOR=90;TABLEVALIDATE=3;REFRESH=5;VARCHARMAPPING=False;ANSI=True;REPROCESS=5];
LOAD *, 'Site1' as Source;
LOAD
ntarj                                                                                 as Num_Tarjeta_OP,
    num(`cod_bloq`)                                                              as Cod_Bloqueo,
    if(num(`cod_bloq`) <> 8, 'Vigente','Castigada')                    as Vigente_Castigo,

      #deudat                                                                         as Deuda_Total,#
    estado                                                                           as Estado,
     MonthName(Date(`td_corte2`,'MMM-YY'))                        as Periodo,

SQL SELECT *
FROM `btcc11_21`
Where estado =1 And deudat <> 0;

//////Site 2//////

OLEDB CONNECT TO [Provider=VFPOLEDB.1;Data Source=e:\datos externos\2012\01_Enero;Mode=Share Deny None;Extended Properties="";User ID="";Mask Password=False;Cache Authentication=False;Encrypt Password=False;Collating Sequence=MACHINE;DSN="";DELETED=True;CODEPAGE=1252;MVCOUNT=16384;ENGINEBEHAVIOR=90;TABLEVALIDATE=3;REFRESH=5;VARCHARMAPPING=False;ANSI=True;REPROCESS=5];

LOAD *, 'Site2' as Source;

LOAD

ntarj as Num_Tarjeta_OP,

num(`cod_bloq`) as Cod_Bloqueo,

if(num(`cod_bloq`) <> 8, 'Vigente','Castigada') as Vigente_Castigo,

SQL SELECT *
FROM `btcc11_40`
Where estado =1 And deudat <> 0;

//////Site 3//////

OLEDB CONNECT TO [Provider=VFPOLEDB.1;Data Source=e:\datos externos\2012\02_Febrero;Mode=Share Deny None;Extended Properties="";User ID="";Mask Password=False;Cache Authentication=False;Encrypt Password=False;Collating Sequence=MACHINE;DSN="";DELETED=True;CODEPAGE=1252;MVCOUNT=16384;ENGINEBEHAVIOR=90;TABLEVALIDATE=3;REFRESH=5;VARCHARMAPPING=False;ANSI=True;REPROCESS=5];

LOAD *, 'Site3' as Source;

LOAD

ntarj as Num_Tarjeta_OP,

num(`cod_bloq`) as Cod_Bloqueo,

if(num(`cod_bloq`) <> 8, 'Vigente','Castigada') as Vigente_Castigo,

SQL SELECT *
FROM `btcc11_40`
Where estado =1 And deudat <> 0;

And So on.... Every time when a dbf fall into the folder Im connecting to the new file and I create a new "Site". But I need left this automate. So I have this:

Sub Busqueda (Root)
     For Each Ext In 'dbf' // Búsqueda para el actula directorio
          For Each File In FileList (Root & '\*.' & Ext)
                    Files:
                     Load '$(File)' as Name,
                     FileTime('$(File)') as FileTime,
                     RangeSum(Peek('FileCount'), 1) as FileCount
                     Autogenerate 1;
          Next File
     Next Ext

     For Each Dir In DirList (Root & '\*.dbf') // Búsqueda en SubDirectorios
          Call Busqueda (Dir)
     Next Dir

End Sub

Call Busqueda ('E:\Datos externos\2012\'); // Starting point

MostRecentFileName:
//FIRST 10
Load Name AS MostRecentFileName
Resident Files
Order by FileTime Desc;

Let vFileToLoad = FieldValueCount('MostRecentFileName');

For each file in FieldValueList('MostRecentFileName')

MostRecentdata:
OLEDB CONNECT TO [Provider=VFPOLEDB.1;Data Source=e:\datos externos\2012\;Mode=Share Deny None;Extended Properties="";User ID="";Mask Password=False;Cache Authentication=False;Encrypt Password=False;Collating Sequence=MACHINE;DSN="";DELETED=True;CODEPAGE=1252;MVCOUNT=16384;ENGINEBEHAVIOR=90;TABLEVALIDATE=3;REFRESH=5;VARCHARMAPPING=False;ANSI=True;REPROCESS=5];

SELECT *
FROM $(file);

I`ve eliminated 02_Febrero From in the string of connection (e:\datos externos\2012\02_Febrero) so I have this now e:\datos externos\2012\ But doesn't work.

If you need more details let me know please.

Re: Visual Fox Pro

pgalvezt — Mon, 10 Sep 2012 13:41:02 GMT

Any Help?

Thanks

Re: Visual Fox Pro

flipside — Mon, 10 Sep 2012 13:52:28 GMT

I cannot help with OLEDB as I don't have the drivers installed, but I have connected to VFP files previously using ODBC drivers using a System DSN which was pretty easy. Just point the driver to one of the files in the directory and you can select any other file in that folder as though it was a database table.

If I recall I had to have both the DBF and CDX files (and for some tables the FPT file).

Could be worth trying ODBC to see if it's an OLEDB issue.

flipside

Re: Visual Fox Pro

pgalvezt — Mon, 10 Sep 2012 14:46:34 GMT

Hi,

How do you do for take all dbf files? (I can not take all dbf files with just one selection) When I try to take all files QlikView shows me the complete directory but when I put Select Qlikview just let me choose just one base for each connection. Could you please advice me about this type of connections?

Thank you!

Re: Visual Fox Pro

pgalvezt — Mon, 10 Sep 2012 15:32:52 GMT

Any Help?

Re: Visual Fox Pro

pgalvezt — Mon, 10 Sep 2012 17:37:28 GMT

Anyone?

Thanks

Re: Visual Fox Pro

rbecher — Mon, 10 Sep 2012 18:01:54 GMT

Hi,

you need to move the SELECT inside the For Each File in FILELIST loop.

There is no FieldValueList. It wouldn't needed furthermore..

- Ralf

Re: Visual Fox Pro

pgalvezt — Mon, 10 Sep 2012 18:25:36 GMT

Could you provide more specific details first time that I working with VFP. I put an example test.

Thanks.

Re: Visual Fox Pro

rbecher — Mon, 10 Sep 2012 18:44:43 GMT

//something like this:

ODBC CONNECT TO [Visual FoxPro Tables];

For Each File In FileList ('E:\Modelos\Solventa\Tarjetas de Credito\Datos externos\Bases_TC\2012\*.dbf')

Let vTableName = subfield(mid('$(File)', index('$(File)', '\',-1) +1), '.dbf', 1);

TableContent:

SELECT *

FROM $(vTableName);

Next File

Re: Visual Fox Pro

pgalvezt — Mon, 10 Sep 2012 19:15:55 GMT

I realized the changes into my Qvw Example and Appears This: See the attached file please.

Re: Visual Fox Pro

rbecher — Mon, 10 Sep 2012 19:28:58 GMT

The connect is the problem. Maybe it's the wrong source path in your ODBC connection definition.

However I never got this ODBC/OLE DB to work woth FoxPro only with our JDBC connector and a dbf driver.

Re: Visual Fox Pro

pgalvezt — Mon, 10 Sep 2012 19:33:49 GMT

Where can I find these connector that you talking about or dbf driver?

Re: Visual Fox Pro

rbecher — Mon, 10 Sep 2012 19:40:56 GMT

QlikView JDBC Connector from TIQ Solutions:

http://www.jdbcconnector.com

JDBC DBF drivers (tested and very stable):

http://www.csv-jdbc.com/stels_dbf_jdbc.htm

http://www.hxtt.com/dbf.html

- Ralf

Re: Visual Fox Pro

pgalvezt — Mon, 10 Sep 2012 20:05:48 GMT

wow! Is the only possibility maybe a free connector for this. I think the other option is talking with TI service and change the format from dbf to txt.

Thanks!

Re: Visual Fox Pro

rbecher — Mon, 10 Sep 2012 20:11:05 GMT

Yea, export to txt format would be the "free" option..

Re: Visual Fox Pro

flipside — Mon, 10 Sep 2012 20:17:05 GMT

I did manage to get ODBC working using a DSN with Microsoft FoxPro VFP driver (had to search a bit for that) ...

... then just browse to the data folder location.

My script is then ...

ODBC CONNECT TO [DSN name here];

// Mapping Tables
userMap:
mapping load
initials,name;
SQL
SELECT initials, name FROM users;

typeMap:
mapping load
type,desc;
SQL
SELECT type,desc FROM type;

It shouldn't be too difficult to turn this into a loop. However, you DO have to have not only the .dbf files but the .cdx file (and for some tables I also needed the .fpt file). I've never used FoxPro so don't know what these files are - I was just given these files as is. In the example above your files will be ...

users.dbf,

users.cdx,

type.dbf &

type.cdx

- maybe also users.fpt & type.fpt.

flipside

Re: Visual Fox Pro

rbecher — Mon, 10 Sep 2012 20:19:35 GMT

Is this working with QlikView 11?

Re: Visual Fox Pro

flipside — Mon, 10 Sep 2012 20:28:28 GMT

Yes. Developed in QV10 and ran a small test okay through QV11.

Re: Visual Fox Pro

rbecher — Mon, 10 Sep 2012 20:31:37 GMT

Hm, it's crashing on my site with QV 11 x64..

Re: Visual Fox Pro

flipside — Mon, 10 Sep 2012 20:55:40 GMT

All tested in 32-bit only, the project never got deployed to the servers. I guess this might be an issue - does any of this help (http://saltydogllc.com/?p=356)?