Problem extracting header as date from .dbf file

Tue, 26 Feb 2013 09:07:01 GMT

Hi all,

I have to extract data from .dbf files, files that are stored on 44 distinct servers.

On each server, the tables are like "Detaliutichete12009" or "detaliutichete102009". What i need to do is to extract the header of the table and transform it into a date format that will be put into a new table as column "Date".

I have written a script that does this correct for some Excel files that I have on my local computer. The issue is that when I try to execute the script for the .dbf files that are stored on the server, it doesn't load me anything.

Instead, I receive an error message saying "bad zip file", even though there is no zip file in that location.

The code I'm trying to use to extract the date from the .dbf file is the following:

For each vFile in FileList('y:\CONTABILITATE\Detaliutichete*.dbf')

Load *,

if(left(right(filebasename(),6),1), 1, 0) as first digit,

left(right(filebasename(),5),1) as second digit,

date(date#(right(filebasename(),4), 'YYYY'),'YYYY') as year

from

(ooxml, embedded labels, table is Sheet1);

NEXT vFile

Any ideas?

Thanks,

Razvan

Re: Problem extracting header as date from .dbf file

Tue, 26 Feb 2013 12:45:55 GMT

try like this:

For each vFile in FileList('y:\CONTABILITATE\Detaliutichete*.dbf')

Load

date(date#(num(right(FileBaseName(), len(FileBaseName())-14),'000000'),'MMDDYY')) as NewDate

FROM

[$(vFile)]

(ooxml, embedded labels, table is Sheet1);

NEXT vFile

topic Re: Problem extracting header as date from .dbf file in QlikView

Problem extracting header as date from .dbf file

Re: Problem extracting header as date from .dbf file