Help importing text file into access

  • Thread starter Thread starter Zubin
  • Start date Start date
Z

Zubin

Hi All,
I'm trying to import a text file into access or excel, whichever might
be possible. The problem is that the file is not well formatted. When
i try using the import wizard in access, its hard to seperate the data
into fields because of no fixed width or delimeters. The text is
simply seperated by spaces. Differnet lines have different formatting.
a simplified version looks like:
Company XYZ
Program: hgjtkj XYZ ABC xys
Part Number: vbyjb 10/1 10/2 10/3 10/4 ....
gkfygvj 1 3 6 *

A similar pattern keeps reperating. The file is huge, 30k lines avg so
its not practical to format it manually. Is there any easy work
around.
Thanks in advance.
 
Hi Zubin,

There's no easy way.

Assuming the file is laid out regularly like the printout of a report,
consider software such as Monarch, which is designed to "read" output
like that and convert it into usable formats.

Otherwise, it's necessary to either

-write VBA code running in Access (or, if you prefer, Excel) that reads
the file line by line, parses each line into its parts, and puts them in
the appropriate records and fields in one or more tables;

-write code in your favourite language that does a similar task but
produces one or more regular text files that Access can import.
 
(e-mail address removed) (Zubin) wrote ...
I'm trying to import a text file into access or excel, whichever might
be possible. The problem is that the file is not well formatted. When
i try using the import wizard in access, its hard to seperate the data
into fields because of no fixed width or delimeters. The text is
simply seperated by spaces. Differnet lines have different formatting.
a simplified version looks like:
Company XYZ
Program: hgjtkj XYZ ABC xys
Part Number: vbyjb 10/1 10/2 10/3 10/4 ....
gkfygvj 1 3 6 *
Click to expand...

Excel's import functionality has a 'Treat consecutive delimiters as
one' property that Jet (and MS Access?) lacks, meaning multiple space
characters can be used as a delimiter. The data has to be suitable
i.e. data items and nulls must not utilize the space character.
Unfortunately, you data is not suitable for this :-(

Might be worth checking that the data does not contain tab characters
(ascii code 9) that may be used as delimiters.

Jamie.

--
 
Thanks guys for the response. John I took your advice and wrote some
vba code to extract only relevant data. Jamie I did try checking the
treat consecutive delimeters as one option but like you said it didn't
work with my data, I wish the file was a tab delimited file, would
have made my life a lot easier, but nothing in life is ever simple and
easy to have to do it the hard way. Thanks once again for your
responses.
Zubin
 
Back
Top