Unusual data format

S

Salty

I am hoping someone has done this before.

I have a client who has been presented with several thousand records in
an unusual format. Each is formatted thus:

Name<CR>
Agency<CR>
Address<CR>
CityStateZip (all in one field)
<CR>
Phone<CR>
Email<CR>
<CR>
Name<CR>
Agency<CR>
Address<CR>
CityStateZip<CR>
Phone<CR>
Email<CR>
<CR>

and so on.

She wants to be able have a standard data file, Excel or CSV to use in
a database. Has anyone seen an automayted method to move these around
without having someone sit and do it manually for several days.

salty
 
H

Harlan Grove

Salty said:
I have a client who has been presented with several thousand records
in an unusual format. Each is formatted thus:

Name<CR>
Agency<CR>
Address<CR>
CityStateZip (all in one field)<CR>
Click to expand...

I'm assuming you didn't mean to have the said:
Phone<CR>
Email<CR>
<CR>
Name<CR>
Agency<CR>
Address<CR>
CityStateZip<CR>
Phone<CR>
Email<CR>
<CR>

and so on.

She wants to be able have a standard data file, Excel or CSV to use in a
database. Has anyone seen an automayted method to move these around without
having someone sit and do it manually for several days.
Click to expand...

This is a very standard format on Unix systems, and there are very short
perl and awk scripts that would convert it from this format into CSV format.
But if you want an Excel solution, open the file as plain text WITHOUT
parsing anything into fields. The data will be in column A.

If all records have 6 fields each on a separate line followed by a blank
line as record separator, select C2:H2, type

=INDEX($A:$A,(ROW()-2)*7+COLUMN()-2)

hold down a [Ctrl] key and press the [Enter] key. This will put the first
record in C2:H2. Fill C2:H2 down as far as needed. Then select the entire
col C to col H range, copy and paste special as values on top of itself,
delete cols A and B, and save the resulting file either as an .XLS file or a
..CSV file.
 
S

Salty

I'm assuming you didn't mean to have the <CR> on a different line.
Click to expand...

No, Harlan, it is there. I think the data came from HTML tables
originally and there may have been a <br> there for display clarity.

Checking your solution.
 
Top