Visual Fox Pro

[pgalvezt]

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, 

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

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, 

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

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.

[rbecher]

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.

[pgalvezt]

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

[rbecher]

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

[pgalvezt]

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!

[rbecher]

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

[flipside]

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

[rbecher]

Is this working with QlikView 11?

[flipside]

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

[rbecher]

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

[flipside]

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)?