Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hopkinsc
Partner - Specialist III
Partner - Specialist III

Reading from .DBF file

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

17 Replies
hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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..

http://community.qlik.com/message/683594#683594

flipside
Partner - Specialist II
Partner - Specialist II

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 ...

h t t p :// stack overflow. com/ questions/10227144/convert-long-filename-to-short-filename-8-3-usin...

flipside

flipside
Partner - Specialist II
Partner - Specialist II

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

Not applicable

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.

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Hi Soporte,

i have tried that driver but it doesnt fetch all fields unfortunately.

fabio182
Creator II
Creator II

Carga tu archivo DBF al SQL a travez de un DTS, luego realices la conexion desde el QlikView al SQL y listo. xD

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

thanks Flipside, that works!

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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