Import Wizard Freezes on large CSV files

[kilauea2]

I am trying to import some rather large CSV files into Access 97 (about
40-50mb). I open the Import Wizard, select the file, click ok, and
then the program just sits. The Windows Task Manager claims that
Access is busy, but I have let it sit for about an hour and still
nothing. I have to Ctrl+Alt+Delete to get out of Access.

I have tried importing a really small CSV file (only a couple of rows),
and that worked fine.

tried importing the larger file at home with Access 2003 with no
problems (but of course, I can't open the 2003 file with 97). I have
also tried reregistering all the DLL files, including mstext35.dll and
accwiz.dll, but that didn't work either.

Any assistance on this issue would be greatly appreciated.

 

[John Nurick]

Let me get this clear. You say you open the import wizard, select the
file, click OK and then nothing happens. The usual sequence of events is

1) Select the File|Get External Data|Import menu command...

2) which brings up an ordinary Windows file dialog where you can select
the file to import from. If you select a text file and click OK, you
get...

3) the Import Text Wizard with a series of pages where you specify what
sort of a text file it is, the name of the table you want to import it
to, and so on. Clicking the wizard's FInish button launches the actual
import.

Is Access 97 locking up after (2) or after (3)?

Other questions:

a) Is there plenty of disk space available?

b) How big is the mdb file you're trying to import to? The limit on an
Access 97 file is 1 GB, and a fair amount of "headroom" is needed when
importing. So if the file is in the 800 MB range already you may be
hitting that limit.

c) If you're importing to an existing table, try removing any indexes
before import, then re-creating them afterwards.

d) How long is the longest record in the CSV file? Access 97 has a limit
of 2000 characters per record (excluding memo, hyperlink and OLE
fields), so if there are several fields in the text file with lengths
that hit >300 characters the import could be hitting that limit. (With
European languages, the practical limit is greater in Access 2000 and
later, a side-effect of Unicode compression.)

Often it helps to split a big file into several smaller ones. If one
fails to import, at least you know where the problem is. There are lots
of splitting utilities on the internet; I use split.exe, which is part
of the Gnu utilities that can be downloaded from
http://unxutils.sourceforge.net/

 

[kilauea2]

Access 97 is locking up after (2).

a) plenty of disk space is available, over 50gb
b) mdb file importing to currently has no other data, thus is very
small.
c) n/a, since I am creating a new table
d) field text and record characters are way under the 300 & 2000,
respectively.

Thanks!
Daniel

 

[John Nurick]

Interesting. This suggests that Access 97 is falling down at the stage
when the Import Text Wizard is scanning (usually just part) of the file
to make its guess about the sort of file it is and to display this guess
in the box on its first page.

Normally I'd assume that this indicates some glitch in the structure of
the CSV file: but this seems less likely in this case because Access
2003 can import the file, and normally the later versions are more picky
about text files than the earlier ones.

You mentioned that you had more than one of these big files. Have you
tried importing the others, and do they all fail in the same way?
Obviously, if some import OK and others do not, the assumption is that
the problem is in the file. (In my limited expererience, most problems
importing large text files ultimately stem from glitches in the files
themselves.)

One thing I would do is to open the registry editor and find the key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Text . If
MaxScanRows is set to 0, reset it to the default value of 25
(0x00000019). For why, see the Access 97 help topic "Initializing the
Text Data Source Driver", which is in the Jet SQL reference section.

Also - have the Office 97 service releases been installed on your
computer? You should be on SR-2 or later.

Anyway, if none of the above fix the problem, I'd start splitting the
big files into manageable chunks and importing these one at a time.

Access 97 is locking up after (2).

a) plenty of disk space is available, over 50gb
b) mdb file importing to currently has no other data, thus is very
small.
c) n/a, since I am creating a new table
d) field text and record characters are way under the 300 & 2000,
respectively.

Thanks!
Daniel

Let me get this clear. You say you open the import wizard, select the
file, click OK and then nothing happens. The usual sequence of events is

1) Select the File|Get External Data|Import menu command...

2) which brings up an ordinary Windows file dialog where you can select
the file to import from. If you select a text file and click OK, you
get...

3) the Import Text Wizard with a series of pages where you specify what
sort of a text file it is, the name of the table you want to import it
to, and so on. Clicking the wizard's FInish button launches the actual
import.

Is Access 97 locking up after (2) or after (3)?

Other questions:

a) Is there plenty of disk space available?

b) How big is the mdb file you're trying to import to? The limit on an
Access 97 file is 1 GB, and a fair amount of "headroom" is needed when
importing. So if the file is in the 800 MB range already you may be
hitting that limit.

c) If you're importing to an existing table, try removing any indexes
before import, then re-creating them afterwards.

d) How long is the longest record in the CSV file? Access 97 has a limit
of 2000 characters per record (excluding memo, hyperlink and OLE
fields), so if there are several fields in the text file with lengths
that hit >300 characters the import could be hitting that limit. (With
European languages, the practical limit is greater in Access 2000 and
later, a side-effect of Unicode compression.)

Often it helps to split a big file into several smaller ones. If one
fails to import, at least you know where the problem is. There are lots
of splitting utilities on the internet; I use split.exe, which is part
of the Gnu utilities that can be downloaded from
http://unxutils.sourceforge.net/
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.