Creating text files to import

[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

[Sunil Jayakumar]

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


"Meyer1228" <[email protected]> wrote in message
news:Sme2e.20739$jt6.8739@trndny07...
> 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
>
>
>

[Meyer1228]

Thanks, that idea helped. I tried importing again, and after tweaking the
choices, it's getting closer to what I want.


"Sunil Jayakumar" <sunil.jayakumar[at]gmail.com> wrote in message
news:[email protected]...
> 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
>
>
> "Meyer1228" <[email protected]> wrote in message
> news:Sme2e.20739$jt6.8739@trndny07...
> > 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
> >
> >
> >
>
>

[Bucky]

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

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.

[Earl Kiosterud]

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 Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Meyer1228" <[email protected]> wrote in message
news:Sme2e.20739$jt6.8739@trndny07...
> 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
>
>
>

[green fox]

Earl Kiosterud wrote:
> 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 Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "Meyer1228" <[email protected]> wrote in message
> news:Sme2e.20739$jt6.8739@trndny07...
> > 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
> >
> >
> >

[green fox]

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
Earl Kiosterud wrote:
> 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 Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "Meyer1228" <[email protected]> wrote in message
> news:Sme2e.20739$jt6.8739@trndny07...
> > 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
> >
> >
> >