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.

[pgalvezt]

FlipSide,

ODBC CONNECT TO [Visual FoxPro Tables];

For Each File In FileList ('your filepath\*.dbf')

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

Qualify *;
$(vTableName):
SQL SELECT * FROM $(vTableName);
Unqualify *;

Next File;

Do you have a code that read subdirectories?

Thanks!

[flipside]

This is some code to get you started, it will iterate down through directories but you can also limit the depth you want to inspect.  Obviously you won't need the msgbox statements, this is just to show what is going on ...

Let MaxFolderDepth = 2; //set limit to number of iterations the check will run if needed

Sub InspectFolders(Directory)
For Each Folder In DirList ('$(Directory)')
  x = MsgBox('$(Folder)','FOLDER FOUND:',1); // 1 adds a Cancel button to allow skip out of loop routine
  if x = 2 then
   exit for;
  end if;

If substringcount ( '$(Folder)', '\' ) - substringcount ( '$(StartFolder)', '\' ) <= $(MaxFolderDepth) then
  CALL InspectFiles('$(Folder)')
End If;

Next Folder;
End Sub

Sub InspectFiles(Folder)
For each File In FileList ('$(Folder)\*.*')
  y = MsgBox('$(File)','FILE FOUND:',1); // 1 adds a Cancel button to allow skip out of loop routine
  if y = 2 then
   exit for;
  end if;
Next File;

For each Folder in DirList ('$(Folder)')
  CALL InspectFolders('$(Folder)\*')
Next Folder
End Sub

Let StartFolder = 'starting directory \*';

CALL InspectFolders('$(StartFolder)');

flipside

[pgalvezt]

Thank you for your reply FlipSide. I just reach the connection maybe Im doing something wrong. I comment you that I have folder by year. This is my directory

E:\Modelos\Nueva carpeta

Inside of the Nueva Carpeta I have 2011 2012

Inside of the 2011 I have all months, the same thing in 2012.

For example Inside all months, I have January And Inside January I have all  the dbf Bases. And I need to load everything.

2012 And 2011

How should I do it?

Thank You!

[pgalvezt]

Any Help?

Thanks!

[rbecher]

Just start programming based on FlipSide's code sample. It's everything there..

[flipside]

Just a note that could be helpful.  Once you have your DSN setup, if you connect in Qlikview and then select a table, you only see the .dbfs in that folder.  The .dbfs in sub-folders are not available through that method.

In the script however you do seem to be able to reference sub-folder .dbfs using a single DSN (although I only did a short test of this), eg ...

ODBC CONNECT32 TO vfptest;

SQL SELECT *
FROM file2011;//591

SQL SELECT *
FROM Data2011/file2011; //Data2011 is the folder name

SQL SELECT *
FROM Data2012/file2012; //Data2012 is the folder name and file2012 is the .dbf name

You should have everything here to get this working.  If it was me, I would take this approach ...

1) Make a table of all the paths required using the iterative Subroutines shown earlier.

2) Create the connection.

3) Loop over all the SQL SELECT commands using the paths in the table.

flipside

[agigliotti]

I have the same problem I get connected but I can't see any table.

Did you find a solution ?

Can you help me?

Best Regards

[agigliotti]

Currently I got the below error message:

I don't have any .DBC file but only .DBF, .CDX and .FPT !!!

Can you help me?

Thanks.

[rbecher]

Just use the filename as tablename in SELECT query..

[agigliotti]

Hello Ralf,

I'm on WIN 7 x64 machine with QLIK x 64

I tried to use either ODBC 32 and OLE DB 32 driver to read from VFP 9.0 table (.dbf)

Using the following script:

ODBC CONNECT32 TO [Visual FoxPro Tables];

SQL SELECT *

FROM anagrafe; (the file name is anagrafe.DBF)

BELOW THE ERROR I GOT:

Using the following script:

OLEDB CONNECT32 TO [Provider=VFPOLEDB.1;Data Source=D:\VFP\TEST;Mode=Read|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];

SQL SELECT *

FROM anagrafe; (the file name is anagrafe.DBF)

BELOW THE ERROR I GOT:

What can I do ?