Extracting data from multiline Spreadsheet

  • Thread starter Thread starter Craig
  • Start date Start date
C

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
 
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.
 
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.
 
Back
Top