Help formatting rows that are copied/pasted

[GD]

Can someone help me get started on a form & query?

I need to design a form that allows multiple rows of data to be pasted in
from a wIntegrate screen (basically, a non-MS, DOS-based app). Potentially,
there could be as many as 100 rows of data resembling:

Rec# MFG Invoice No.... Rec Date Pay Date Receipt Amt Invoice Amt Frt
Amt
1. 110945 06/13/08 07/03/08 72,849.00 67,506.79
0.00
111370 12/04/08 2,329.36
0.00
CMCN34462 07/03/08 -1,084.95
0.00
CMCN34588 08/28/08 -31.50
0.00
RPYCMCN34462 07/10/08 1,084.95
0.00
2. 115666 09/19/08 10/23/08 8,111.53 8,177.21
0.00
3. 116302 10/10/08 10/23/08 1,594.67 1,607.54
0.00

As you can see, these would be pasted in as long text strings. The next
step would be to design a query that would separate these into fields in a
table. What the best way to accomplish this?

Any thoughts?

Thanks!!!

 

[Dorian]

It sounds like you should IMPORT the data as a space-delimited file. This
will put the data in a table and separate the fields for you. The only
problem is if you have fields with embedded spaces you will need to enclose
them in quotes.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[GD]

Thanks for the response, Dorian!

Not too practical, though, because that would entail pasting and saving the
rows to an MS app first, then importing the file. The time involved with
that , combined with the impatience of the users involved, doesn't make it
worth while to even attempt a solution of this kind.

Background: We are currently copying/pasting this information to an Excel
spreadsheet and manually formatting it (text to columns to separate into
columns, copying and adjusting formula rows for totals, disregarding any data
that was entered after a specific date, etc.). To me, Access could do a much
better and quicker job of this. But I am stuck before I've gotten started.

The #1 question I have is: How can I copy and paste rows into a form (10 or
so at a time), but have each row recognized as a separate entry by a query?

 

[Philip Herlihy]

Thanks for the response, Dorian!

Not too practical, though, because that would entail pasting and saving the
rows to an MS app first, then importing the file. The time involved with
that , combined with the impatience of the users involved, doesn't make it
worth while to even attempt a solution of this kind.

Background: We are currently copying/pasting this information to an Excel
spreadsheet and manually formatting it (text to columns to separate into
columns, copying and adjusting formula rows for totals, disregarding any data
that was entered after a specific date, etc.). To me, Access could do a much
better and quicker job of this. But I am stuck before I've gotten started.

The #1 question I have is: How can I copy and paste rows into a form (10 or
so at a time), but have each row recognized as a separate entry by a query?

[Most recent post before this reply is at the top of this thread]

The success of pasting will depend more on the structure of what you're
copying than on any difference between Excel and Access, although in the
latter case you'd have to design a form to receive the paste (I think!)
which you wouldn't have to do in Excel. It's going to be pretty
hit-and-miss whatever you do.

What would be better would be to save the data, if the application
allows that. How does it store it? (you may find there's a plain text
file sitting there somewhere waiting to be imported). Once you have
your file you can assess how "regular" it is - are there delimiters
(e.g. tab, or comma) which DON'T appear in the actual data? If so, it's
a breeze to import this sort of material into Access (or Excel). If
your data is "irregular", then it can be worth processing it (I'd use
Unix-based scripting tools, but there are plenty of Windows options,
including Powershell) to "tidy" up the data so that it is regular.

Failing that, can you print it? If you can print it to any electronic
format (e.g. a PDF converter such as TinyPDF or PDF995, both free) you
may be able to run it through OCR software such as Omnipage, which can
save as an Excel-compatible format which you may be able to import
directly into Access, or fiddle with in Excel. OCR can sometimes be
startlingly accurate with everyday fonts.

Phil, London