Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
hopkinsc
Valued Contributor II

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
Valued Contributor II

Re: Reading from .DBF file

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

17 Replies
hopkinsc
Valued Contributor II

Re: Reading from .DBF file

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

MVP
MVP

Re: Reading from .DBF file

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

hopkinsc
Valued Contributor II

Re: Reading from .DBF file

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
Valued Contributor II

Re: Reading from .DBF file

What's the error message you are getting?

hopkinsc
Valued Contributor II

Re: Reading from .DBF file

Hi Flipside,

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

Standard.JPG

Re: Reading from .DBF file

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
Valued Contributor II

Re: Reading from .DBF file

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
Valued Contributor II

Re: Reading from .DBF file

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
Valued Contributor II

Re: Reading from .DBF file

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

Community Browser