Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
you can try this il your fields have the same fields
load * from C:\Temp\*.DBF
hi, unfortunately that doesnt work as the filenames are over 8 characters long so the ODBC driver doesnt even read them.
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,
B,
C,
D,
E,
F
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; !