temporary renaming file names

[hopkinsc]

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?

[syukyo_zhu]

you can try this il your fields have the same  fields

load  * from C:\Temp\*.DBF

[hopkinsc]

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

[Greg_Hood]

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; !