Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
hopkinsc
Partner
Partner

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

1 Solution

Accepted Solutions
flipside
Specialist II
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

View solution in original post

17 Replies
hopkinsc
Partner
Partner
Author

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.

Thanks

tresesco
MVP
MVP

Try making a connection OLEDB/ODBC, check this thread: http://community.qlik.com/message/436157#436157

hopkinsc
Partner
Partner
Author

Hi tresesco,

I have just tried that but it has the same issue as the standard driver where it cannot read from files over 8 characters long

flipside
Specialist II
Specialist II

What's the error message you are getting?

hopkinsc
Partner
Partner
Author

Hi Flipside,

This is the error i get no matter which driver i use. (for file names longer than 8 characters)

Standard.JPG

marcus_sommer
MVP & Luminary
MVP & Luminary

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

- Marcus

hopkinsc
Partner
Partner
Author

Hi Marcus,

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.

Thanks

flipside
Specialist II
Specialist II

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

`2014 0~1`


h t t p :// support .microsoft .com/kb/867190

h t t p : // support2. microsoft. com/default.aspx?scid=kb;en-us;q209685

flipside

flipside
Specialist II
Specialist II

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.

flipside