topic Re: temporary renaming file names in QlikView

temporary renaming file names

hopkinsc — Fri, 19 Dec 2014 12:48:32 GMT

Hi all,

I have a directory full of .DBF files, all with quite long names that i can't get an ODBC driver to read (because the filenames are over 8 characters).

as a work around i want to temporary shorten the file name, read it in then rename it back.

This will need to be done within a loop but i am unsure how to do this.

Can anyone help?

e.g.

in my directory i have..

20141201 - file1.dbf

20141201 - file2.dbf

20141201 - file3.dbf

i have the following in my script so far..

LET vFolder='C:\Temp';

LET vPartFileName = '\*.DBF';

FOR EACH File IN filelist ('$(vFolder)' & '$(vPartFileName)')

LET vFilename = subfield(subfield('$(File)', '\', -1),'.',1);

LET vFullFilename = '$(File)';

LET vTempFilename = 'Temp';

which i think is correct so far, what i now want to do is to find the first file, rename the vFilename part to vTempFilename. read it in then rename it back to vFilename then move to the next file.

does that make sense?

Re: temporary renaming file names

syukyo_zhu — Fri, 19 Dec 2014 12:52:54 GMT

you can try this il your fields have the same fields

load * from C:\Temp\*.DBF

Re: temporary renaming file names

hopkinsc — Fri, 19 Dec 2014 12:56:29 GMT

hi, unfortunately that doesnt work as the filenames are over 8 characters long so the ODBC driver doesnt even read them.

Re: temporary renaming file names

Greg_Hood — Fri, 19 Dec 2014 14:57:32 GMT

Hi, you could try something like this. This will execute a commandline to rename the files, load from the files then name it back again. Beware you will need to allow execution of external programs in the script [settings tab at the bottom of the script editor]. This works for me as a small test using xlsx files, but substitute dbf and it should work, note place the .qvw in the same folder as the dbf files for this simple example to work.

sub DoDir2

for each Ext in 'xlsx' // add more file ext here ,'qvd' , 'xls'

for each File in Filelist ('*'& Ext)

//rename the file using an external commandline

Execute cmd.exe /C rename $(File) temp.xlsx;

sleep 500; //sleep to give the I/O time to rename the file, 1000=1s

//do your reload here

LOAD A,

FROM

(ooxml, no labels, table is Sheet1);

sleep 500;

//rename the file back to the old name

LET sV = mid('$(File)',index('$(File)','\',-1)+1); //for some reason the file name must be a name only and not full path

Execute cmd.exe /C rename temp.xlsx $(sV);

//Execute cmd.exe /C rename temp.xlsx 20141201-Filex.xlsx;

next File

next Ext

end sub

call DoDir2; !