Re: Visual Fox Pro - Page 3 - Qlik Community

pgalvezt · ‎2012-09-10

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 · ‎2012-09-10

FlipSide Finally I could get the connection. But I can`t see the tables. Any Advice?

flipside · ‎2012-09-10

Is it the loop script that's failing or is it that you cannot see any tables listed after you have connected and pressed select?

When setting up the DSN, when you navigate to the folder where the .DBFs are, you should see the .DBFs in the left hand pane ...

... try selecting one of the DBFs before clicking OK.

This script works for me although it is creating separate tables because my DBFs are not identical format...

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;

pgalvezt · ‎2012-09-11

Hi, I cannot see any tables listed after I have connected and pressed select.

pgalvezt · ‎2012-09-11

Any Help?

Thanks

flipside · ‎2012-09-11

When you first connect to the data source, do you get a successful Connection Test?

Have you tried both System and User DSNs?

Are you on a 32-bit or 64-bit system?

This is the info from the Support (Soporte) button that I get if it is of any use ...

SQL_SERVER_NAME

SQL_DBMS_NAME Visual FoxPro

SQL_DBMS_VER 03.00.0000

SQL_DRIVER_NAME VFPODBC.DLL

SQL_DRIVER_VER 06.86.0001

SQL_DRIVER_ODBC_VER 02.50

SQL_ODBC_VER 03.52.0000

Other than that, I doubt I'll be able to help much more.

rbecher · ‎2012-09-11

What if you just use the filename without extension as table name in the SELECT query?

Data & AI Engineer at Orionbelt.ai - a GenAI Semantic Layer Venture, Inventor of Astrato Engine

pgalvezt · ‎2012-09-11

Ok the reason that I can't see the tables is that I was trying to get the connection thrught the 2012 folder. Into my 2012 folder I have Jan Feb Mar etc. I need to load all the folders. So when I go to the last folder Jan fro example, QlikView Shows me the dbf files. But I cannot take all the dbf Files. (one thing my dfbs are identical Format). The Final idea is automate my model already created.

pgalvezt · ‎2012-09-11

Any Help Please Im really stuck with this.

Thanks

rbecher · ‎2012-09-11

Just use two nested loops. One over the years including one over the month.

Data & AI Engineer at Orionbelt.ai - a GenAI Semantic Layer Venture, Inventor of Astrato Engine

pgalvezt · ‎2012-09-11

Ok thank you both of you. Now the files are loading but QlikView is reading a few data. Not the complete base I don't know why. But separately the bases do 1 million. When I use the code automate QlikView read 40,000.