Importing large text file to version 2003 versus 1997

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

A majority of our databases are created by importing existing data in either
txt or csv file formats. When using Access 97 I never had a problem
importing these files. Since we have converted to 2003 I am unable to import
large files and have some trouble with even smaller files. When attempting
to import it will either time out and throw me into a not responding mode or
it shows the text as jibberish, but the text is just fine when opening it in
either wordpad / excel etc. So we had a spare computer set up with the 97
version so that we can import these files. Is there perhaps something we
need to do differently, something we need to add? Please advise - your help
would be most appreciated. Thanks Anna
 
Hi Anna,

The only difference I can think of between versions is that Access 2003
is much fussier about the file format than Access 97. For example,
Access 2003 seems to require that records (lines) are separated by
proper Windows textfile line breaks, i.e. CR+LF, Chr(13) & Chr(10),
while the older version is less demanding.

This can really hit you if your files are coming from mainframe or Unix
systems. If so, maybe the sysadmins there can modify their scripts to
give you Windows-style files. Otherwise, there are lots of little
conversion utilities availabe: look for "Unix to DOS" or "Unix to
Windows".
 
This may help, but I for example will download the mainframe report and run
it through Docuanalyzer. There I create a model against the report and it
puts the data in an excel style format. Depending on the size it is saved as
either xls or csv. That is why I dont think your recommendation would help
in my case though it may in others. All my importing files are saved this
way and it only seems to have a difficulty when very large. Any other
suggestions would be greatly appreciated.

Thanks again
 
I don't know Docuanalyzer or its quirks, so can't make any suggestions
there.

In my experience (which isn't vast), problems importing big text files
have always come down to either something in the file structure or
hitting the maximum size of the mdb file. (This assumes that your Office
2003 installation is up to date with service packs and that there's no
shortage of disk space or virtual memory.)

I'm not sure what you mean by "throw me into a not responding mode". Big
files can take a long time to import, especially if there are indexes on
the table and you're working across a network. Have you tried copying
the text file and mdb file onto a local drive before importing? It can
also help if you remove indexes before importing a very big text file
and rebuild them afterwards.

"Very large" is of course a relative term: what do you mean in terms of
megabytes?

One thing that's often worth trying is to split the file into smaller
pieces and import them individually. If they all import OK, then I'd
suspect file size, disk space or virtual memory. If one of them won't
import but others the same size do, the odds are strongly on the trouble
being caused by something in the data or structure (e.g. an un-doubled
quote within a quoted field). To split the files I use the Gnu 'split'
utility (in the Windows version downloadable from
http://unxutils.sourceforge.net/ ).
 
Back
Top