Importing data from a text file

[jbaranski]

I have to import raw data from a text file that Excel cannot break into
columns, and the import is unsuccessful. would VBA be a better choice,
having it specifically search for data? there are 3 specific types of
files and all the files (of the same type) are formatted exactly the
same.

thanks in advance

 

[Pete_UK]

Why can't Excel break the data into columns? Please give an example of
the first few lines of data (open the text file in Notepad and
copy/paste to here).

Pete

 

[jbaranski]

i can't directly post the info since it's work related, but it's kind o
like this:


From: (e-mail address removed)
Sent: Friday, July 07, 2006 5:24 PM
To: MyLastName, MyFirstName
Subject: File Type confirmation for Here

System processed a file on 07/07/2006.
This file was from the File Type.
Total number of XXXXXX processed : 5435
Total number of YYYYYY loaded : 4521

I'm only interested in the last 4 lines, specifically the date, fil
type, the number of XXXXXX and the number of YYYYYY

 

[Pete_UK]

Presumably you would have some letters after File Type in the 3rd line
from the bottom?

If it is always going to be the last 4 lines that you are interested
in, then here is a technique that might work for you. Assuming the text
file has an extension of .txt, then you can rename the file(s) and
change the extension to .xyz (or any other combination which is not
used by any of your other applications). Then you can double-click one
of these files - after a short delay Windows will tell you it does not
recognise the file type and will pop up a dialogue box inviting you to
specify the application with which to open the file (choose Excel) and
tick the box "Always use this application to open the file", and click
OK.

In future, when you double-click the file icon it will automatically be
brought into Excel and all the text will appear in column A.

It is relatively straightforward then (maybe recording a macrowith
relative address mode selected while you do it), to delete all rows
except for the last four and then to introduce these formulae in B1 to
B4:

=VALUE(RIGHT(A1,10)) to get the date (not sure if the full stop is
meant)
=RIGHT(A2,4) to get the file type (assuming last 4
characters)
=VALUE(RIGHT(A3,5)) to get the XXXXXX value
=VALUE(RIGHT(A4,5)) to get the YYYYYY value

The values can be fixed and then column A deleted. If your macro is
stored in personal.xls, maybe with a shortcut key of CTRL-SHIFT-P, then
it will just be a matter of renaming the file to .xyz, double-clicking
the icon and then CTRL-SHIFT-P to run the macro each time you want to
do this.

Hope this helps.

Pete