Does anyone know how to read from a .DBF file that has a file name of over 8 characters? anything smaller than this works fine, but i get an error when trying to read from a file that has a name over 8 characters.
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).
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)
if fsoFile is not nothing then
strShortName = fsoFile.ShortPath
GetShortName = strShortName
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);
Let f = chr(96) & '$(Dir)' & chr(96) & '\' & chr(96) & Subfield('$(file)','\',-1) & chr(96);
set errormode = 0;
SQL SELECT *
Let err = scripterror & ';' & scripterrordetails;
'$(file)' as loadFile,
'$(f)' as loadFileNameUsed,
'$(err)' as loadError
set errormode = 1;
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.
Hi, can anyone help with this?
I have tried using the Microsoft dBase Driver (*.dbf) which only works for files up to 8 characters long
I have also tried the visual Fox pro driver which makes the connection but doesn't bring back all of the fields.
It's not really a solution more a workaround to check the len from the filename and if it's too long cut it bei 8 characters with a in front batch-routine triggered by a execute-statement (and maybe revoke it afterwards).
yes thats what i was going to do if there is no other solution. i need to load in multiple files so i guess i would use a loop and rename the file to a temp file at the start then rename it back once it has been loaded in, then move to the next file.
I was hoping for a better solution but it looks like thats the only way it can be done.
This may create an additional problem with "index not found" errors. To get round that you have to edit the .inf file that is created, but I cannot for the life of me find it on my system!
Try shortening your filename like this, and see if it works ...
h t t p :// support .microsoft .com/kb/867190
h t t p : // support2. microsoft. com/default.aspx?scid=kb;en-us;q209685
Looks like you might get away with just renaming the .cdx files if the index error appears. So you can specify a short name such as '2014 0~1','2014 0~2' etc to call the .dbf without having to rename them. If there's an index specified in the .CDX file (of the same name), THAT file has to be renamed to match the shortened version. If you don't have indexes then there might not be any renaming necessary. Filenames of 8 chars cannot be shortened it seems.
Hope this makes sense.
Can't quite make out how the .INF file works in the links I gave you.