Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
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.
Thanks
Thanks flipside,
i don't have any .CDX files, there are only .DBF files.
the files load in perfectly if i shorten the filenames so i think the easiest way is to create a loop to loop through the directory, rename the file, load in then rename it back then move to the next file and do the same.
I have asked for help on doing this though..
That's one way of doing it but it could be slow if the files are large.
If you query the directory using DIR /X, you get the 8.3 shortnames alongside the long names so it should just be a matter of outputting this to a file, something like "DIR /X {directory} > myfile.txt", then parsing that file to create a mapping table. Or this might be useful on a file by file basis ...
flipside
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
Good Morning Hopkinsc
Do you need driver for [Visual FoxPro Database].
Example:
ODBC CONNECT32 TO [Visual FoxPro Database];
My file is cargos.dbf.
Have a good day.
Hi Soporte,
i have tried that driver but it doesnt fetch all fields unfortunately.
Carga tu archivo DBF al SQL a travez de un DTS, luego realices la conexion desde el QlikView al SQL y listo. xD
thanks Flipside, that works!
Hi flipside, i am trying to add another loop in around the loop you have given, could you help?
the thread is http://community.qlik.com/message/684484#684484