Extracting data from multiline Spreadsheet

[Craig]

I have a couple of spreadsheets that use multiple lines for each record.
Sample follows:

Name Address City State Zip
Contact
John Citizen 111 Some Streeet Boston MA 00000
(e-mail address removed)

555-123-4567h

555-987-6543c
Jane Doe 222 Another Street Boston MA 00000
555-555-6666h

(e-mail address removed)

There is no order to the contact data.

I need to pull them into a database somehow.

Is there some way to "fill in" the data, programatically, from the above
lines. (i am looking at about 5000 records)
I know it would repeat the information but at least i could then I can split
the data into separate tables and combine it back with separte fields.

Thanks
Craig

 

[Guest]

You can't do it with a TransferSpreadsheet
You can't do it with a TransferText
You can write a lot of Excel automation code to read the spreadsheet cell by
cell and write it to a table.
Actually, the fastest way would be to flatten the excel spreadsheet into one
row per record and just import it using TransferSpreadsheet.

 

[Guest]

According to your posted sample to only data out of place is SSAN and e-mail.
If this is true then my approach would be in Excel add a new column to the
left of the now column A. Use autofill with -Row() then copy and Paste
Special - Values.
Copy the worksheet to another - just in case.
Sort and delete all blank rows. Create new column names for SSAN and e-mail.
Add new column to right of A.
Autofill with =Find("@", C2) and then sort on B.
Cut all e-mail and paste in e-mail column.
Autofill with =Find("-", C2) and then sort on B.
Cut all SSAN and paste in SSAN column.
Repeat for where e-mail and SSAN is out of place.
Keep saving and occasionaly copy to new worksheet for safety.

 

[Kelvin Beaton]

I've used Monarch from http://www.datawatch.com/ for UNIX spooler files and
it looks like they have integration with Excel too.

Might be another option...

Kelvin