Convert text into tables

  • Thread starter Thread starter Lafeyette
  • Start date Start date
L

Lafeyette

A memo field in my table contains a list of products, with one product
occupying a row in the field (not an observation in the table). Now I want to
remodel this table so that one product actually becomes an observation. In
other words, I need to converts the text in the memo field into tables, using
the paragraph mark as the delimiter. Below illustrates the issue:

Original table
The desired table
Supplier Product List (memo) Supplier Product
List (text)
---------- ------------------------- ----------
-------------------------
A Product 1 A
Product 1
Product 2 ----------
-------------------------
---------- ------------------------- A
Product 2
B Product 3 ----------
-------------------------
Product 4 B
Product 3
---------- ------------------------- ----------
-------------------------
… …

Is that doable? Many thanks.

Lafayett
 
Sorry, the example didn't display properly in the main entry. Here is another
try:

Original table
Supplier Product List (memo)
---------- -------------------------
A Product 1
Product 2
---------- -------------------------
B Product 3
Product 4

---------- -------------------------
…

The desired table
Supplier Product List (text)
---------- -------------------------
A Product 1
---------- -------------------------
A Product 2
---------- -------------------------
B Product 3
---------- -------------------------
…

Look forward to your suggestion. Thanks a lot.

Lafayette
 
Sorry, the example didn't display properly in the main entry. Here is another
try:

Original table
Supplier Product List (memo)
---------- -------------------------
A Product 1
Product 2
---------- -------------------------
B Product 3
Product 4

---------- -------------------------
…

The desired table
Supplier Product List (text)
---------- -------------------------
A Product 1
---------- -------------------------
A Product 2
---------- -------------------------
B Product 3
---------- -------------------------
…

Look forward to your suggestion. Thanks a lot.
Click to expand...

That's going to require some VBA code to parse through the text, looking for
vbCrLf (new line) delimiters, and parsing each row into fields: i.e. nothing
automatic and probably not trivial. I wonder if a "cheat" might work - you
could copy and paste the entire memo field into an Excel spreadsheet, clean up
the separator hyphens, and extract the rows and columns back into Access.
 
Thanks for the answer. Confirm my bad feeling, though. Going to be a labor
intensive work :-(
 
Back
Top