topic Re: Automate dbf files in QlikView

Automate dbf files

pgalvezt — Wed, 05 Sep 2012 20:00:35 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: Automate dbf files

pgalvezt — Wed, 05 Sep 2012 21:00:55 GMT

Ideas For this?

Re: Automate dbf files

pgalvezt — Thu, 06 Sep 2012 12:23:25 GMT

Any Help?

Thanks.

Re: Automate dbf files

Thu, 06 Sep 2012 13:34:42 GMT

Check this:

for each vFile in filelist ('e:\datos externos\2012\02_Febrero\*.dbf')

let vTableName= right (vFile, 13); //here extracting name of table from full path with file name

TableContent:

SQL SELECT *

FROM `$(vTableName)`

Where estado =1 And deudat <> 0;

next vFile

You can try connecting with dBase file from ODBC connection.

Regards

Borys

Re: Automate dbf files

pgalvezt — Thu, 06 Sep 2012 14:01:48 GMT

Thanks for your reply. I was trying to connect to ODBC but amessage appears like "You need the newest version of ODBC Connection for vfoxpro' Exploring microsfto I found this:

Visual FoxPro ODBC Driver
The VFPODBC driver is no longer supported. We strongly recommend using the Visual FoxPro OLE DB provider as a replacement. Please refer to the following article for more information and related links to issues when using the VFPODBC driver: http://support.microsoft.com/kb/277772.

Do you have the driver connection?

Thanks

Re: Automate dbf files

pgalvezt — Thu, 06 Sep 2012 15:07:23 GMT

Anyone who has worked with vfoxpro can help me with this?

Thanks

Re: Automate dbf files

pgalvezt — Thu, 06 Sep 2012 15:25:41 GMT

Borys, I found the ODBC controller

http://fox.wikis.com/wc.dll?Wiki~VFPODBCDriver

But when Im going to chosse the path the selection can't allow me select all dbf files.

Re: Automate dbf files

pgalvezt — Thu, 06 Sep 2012 16:49:05 GMT

Any Suggestions?

Re: Automate dbf files

Thu, 06 Sep 2012 22:16:09 GMT

I used to connection VFP dbf files driver installed with MS Visual FoxPro. However I didn't used other drivers. To connection to dBase dbf tables I used OLE DB driver Microsoft.Jet.OLEDB.4.0 (Extended Properties=DBASE III) or ODBC driver (MSDASQL).

Borys

Re: Automate dbf files

pgalvezt — Fri, 07 Sep 2012 14:42:56 GMT

How do you do for take all dbf files? When I try to take all files QlikView shows me de 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!