topic Re: Reading from .DBF file in QlikView

Reading from .DBF file

hopkinsc — Thu, 18 Dec 2014 16:25:12 GMT

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

Re: Reading from .DBF file

hopkinsc — Fri, 19 Dec 2014 08:57:07 GMT

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

Re: Reading from .DBF file

tresB — Fri, 19 Dec 2014 09:05:40 GMT

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

Re: Reading from .DBF file

hopkinsc — Fri, 19 Dec 2014 09:18:21 GMT

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

Re: Reading from .DBF file

flipside — Fri, 19 Dec 2014 09:54:29 GMT

What's the error message you are getting?

Re: Reading from .DBF file

hopkinsc — Fri, 19 Dec 2014 10:05:17 GMT

Hi Flipside,

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

Re: Reading from .DBF file

marcus_sommer — Fri, 19 Dec 2014 10:29:02 GMT

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

Re: Reading from .DBF file

hopkinsc — Fri, 19 Dec 2014 10:44:14 GMT

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

Re: Reading from .DBF file

flipside — Fri, 19 Dec 2014 10:48:54 GMT

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

Re: Reading from .DBF file

flipside — Fri, 19 Dec 2014 12:50:19 GMT

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

Re: Reading from .DBF file

hopkinsc — Fri, 19 Dec 2014 12:56:49 GMT

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

Re: Reading from .DBF file

flipside — Fri, 19 Dec 2014 14:09:48 GMT

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-using-cmd-exe

flipside

Re: Reading from .DBF file

flipside — Fri, 19 Dec 2014 16:02:46 GMT

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

Re: Reading from .DBF file

Fri, 19 Dec 2014 16:40:28 GMT

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.

Re: Reading from .DBF file

hopkinsc — Fri, 19 Dec 2014 16:49:21 GMT

Hi Soporte,

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

Re: Reading from .DBF file

fabio182 — Fri, 19 Dec 2014 16:56:48 GMT

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

Re: Reading from .DBF file

hopkinsc — Mon, 22 Dec 2014 10:01:12 GMT

thanks Flipside, that works!

Re: Reading from .DBF file

hopkinsc — Mon, 22 Dec 2014 13:24:46 GMT

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