Convert Address List from one column of data to many

[ChuckW]

Hi,

I have a list of addresses in Excel that are all in one
column. There is no column header or anything. It is
usually Company in the first row, then name, address1,
Address2 (some do not have this), city, state and Zip.
this is followed by a space, then the next row has the
second record of Company, name, address1, address2, city,
state and zip. Again some have two address lines and
some have one. What I want to do is to convert this to
have columns or fields with Company as field1, name as
field2, address1 as field3, address2 as field4, city as
field5, state as field6 and zip as field7. Is there a
way to write an sql statement that will do this?

Thanks,

Chuck

 

[Nikos Yannacopoulos]

Chuck,

I think I'd actually try to do it in Excel, with some VBA that would
broadly:
* put the values of 7 consecutive cells in an array
* put the array values in cells (on another sheet) horizontally,
deciding on 1 or two address cells based on whether the seventh position
in the array is blank or not.
* advance to the first cell after the blank cell, and start a new loop

Alternatively, I would save as text from Excel, and use a recordset
operation in an Access VB procedure to populate a table; again using an
array, writing a record when a zero length line is read and deciding on
the fields' position based on the same criterion as above.

HTH,
Nikos