I think I have a solution. The method is to check the length of the filename and if over 12 (8chars + .DBF), then go and get the shortname using a macro function (Current Local Security needs to be Allow System Access).
Macro function:
function GetShortName (file)
set fso = CreateObject("Scripting.FileSystemObject")
strShortName = "Invalid File/Folder - (" & file & ")"
Set fsoFile = Nothing
On Error Resume Next
Set fsoFile = fso.GetFile(file)
if Err.number <> 0 then
Set fsoFile = fso.GetFolder(file)
end if
if fsoFile is not nothing then
strShortName = fsoFile.ShortPath
end if
GetShortName = strShortName
end function
In the load script, use it like this ...
Let Dir = 'C:\TestData';
ODBC CONNECT32 TO [dBASE Files];
For each file in FileList('$(Dir)\*.DBF')
if len(subfield('$(file)','\',-1))>12 then
Let f = chr(96) & '$(Dir)' & chr(96) & '\' & chr(96) & Subfield(GetShortName('$(file)'),'\',-1) & chr(96);
else
Let f = chr(96) & '$(Dir)' & chr(96) & '\' & chr(96) & Subfield('$(file)','\',-1) & chr(96);
endif;
set errormode = 0;
Data:
SQL SELECT *
FROM $(f);
Let err = scripterror & ';' & scripterrordetails;
LoadLog:
Load
'$(file)' as loadFile,
'$(f)' as loadFileNameUsed,
'$(err)' as loadError
autogenerate 1;
set errormode = 1;
next file;
DISCONNECT;
This example is loading variously named files with same data structure into one table AND logging errors to a log table. No renaming of files required.
flipside
