Creating text files to import

  • Thread starter Thread starter Meyer1228
  • Start date Start date
M

Meyer1228

I'm a newbie here, and a programmer who needs to convert a few existing
reports from our application into a format that can be converted to Excel.
I tried simply outputting a report to a .txt file, but the import was so
completely random. These reports have headings and subheadings, then a body
with different types of text (numbers, dates, etc.). The reports create
spaces between each word. Example:

3/29/05 Inventory Sold Report Page 1
Item# Quantity Date Sold
9874779948 6 03/19/05
7367632737 15 03/07/05

I suppose I need to replace the spaces with tabs (not such an easy task) and
then import the file into Excel, but I never use Excel at my company so it
has been a lot of trial and error. And I've googled for days but still have
not made progress. Can someone give me some ideas? Thanks so much.
Kathy
 
Hi Kathy,

I'm assuming that the text file reports are normalised - i.e., ther space
between them is consistent, and the lines do not wrap when you try an import
them into excel.

If so, you can use the Data>Text To columns dialog, and this will separate
your data into columns using the delimiters or spacing according to your
selection.

Hope this helps

Sunil
 
Thanks, that idea helped. I tried importing again, and after tweaking the
choices, it's getting closer to what I want.
 
Meyer1228 said:
3/29/05 Inventory Sold Report Page 1
Item# Quantity Date Sold
9874779948 6 03/19/05
7367632737 15 03/07/05
Click to expand...

It seems that your problem is that the spacing is not aligned (if the
above example is accurate). When you open the file, choose Delimited,
Space, Treat Consecutive Delimiters As One. As long as the none of the
fields have spaces in them (your example doesn't), it will import fine.


If your fields have spaces in them, then there is no way to get it to
import automatically because it is irreversible.
 
Kathy,

It sounds as if your file is really a print file. My first suggestion is to
make absolutely sure that the application from which you're getting the data
cannot give you some kind of database file (comma-separated, or something).

For the print file, if the spaces are consistent for each column in the
detail area (once you're past the headings), you could use the Text Import
Wizard, using the Fixed Width option, setting the character count to match
the print colums. You'd get gibberish for the headings -- every line of the
file will be read into a row in the worksheet, including the headings, which
won't likely line up with the detail rows. The headings are likely
consistent, so a certain number of rows rows could be deleted.

If you're absolutely committed to using the print layout file, I have a
program I wrote many years ago that will wind its way through the headings
(you must specify things in advance to tell it what it's looking for), and
produce a comma-delimited file of the detail rows, which Excel can easily
read. At least I think I still have it. Also, I seem to recall someone
mentioning something commercially available for this kind of application.
Search for stuff like "data from print file." Or perhaps someone will come
forth.
 
Earl said:
Kathy,

It sounds as if your file is really a print file. My first suggestion is to
make absolutely sure that the application from which you're getting the data
cannot give you some kind of database file (comma-separated, or something).

For the print file, if the spaces are consistent for each column in the
detail area (once you're past the headings), you could use the Text Import
Wizard, using the Fixed Width option, setting the character count to match
the print colums. You'd get gibberish for the headings -- every line of the
file will be read into a row in the worksheet, including the headings, which
won't likely line up with the detail rows. The headings are likely
consistent, so a certain number of rows rows could be deleted.

If you're absolutely committed to using the print layout file, I have a
program I wrote many years ago that will wind its way through the headings
(you must specify things in advance to tell it what it's looking for), and
produce a comma-delimited file of the detail rows, which Excel can easily
read. At least I think I still have it. Also, I seem to recall someone
mentioning something commercially available for this kind of application.
Search for stuff like "data from print file." Or perhaps someone will come
forth.
Click to expand...
 
Sorry...my first post. Perhaps the program Karl is talking about is a
utility called MONARCH. I use it to turned print files from our company
vax into live data. I don't know if it can be manipulated with VBA. I'm
hopeful.

Andy Fox,
Newbie
 
Back
Top