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]

As flipside has posted earlier, maybe use this ODBC setup (Free Table directory):

[agigliotti]

below the system DSN I'm using:

really I don't understand why it still asking me for private.dbc file !