How do you separate multiple lines from Excel into Access

[CAD Fiend]

Hello,

I am importing data from an excel spreadsheet, which is fielded this
way:

Parcel_ID, Owner_First_Name, Owner_Last_Name, etc...

The first column, the Parcel ID, could look like this:

Parcel_ID
7-5-029-001

But sometimes, because the SAME owner may have more than one parcel
(under the SAME name), some of the fields can have up to three entries
in the same cell, which are separated by a new line.

Which could look like this:

Parcel_ID
7-5-030-001
7-5-031-001
7-5-032-001

When I bring in the spreadsheet into Access, using File/Get External
Data/Import, the access table NOW has all three Parcel_ID's in the same
field, separated by the little square (which is the new line character,
I believe), which I will use a ^ to substitute it for this discussion.

The new access field now looks like this:

7-5-030-001^7-5-031-001^7-5-032-001

HERE IS MY QUESTION:

Using a query (I guess), I would need to get Access to read the
Parcel_ID, find the separator, extract the next number series, then make
a new record, with the SAME Owner_First_Name, Owner_Last_Name, etc...,
with the different Parcel_ID?

Thanks in advance for your reply (ies).

Phil.

<If you want to respond directly just remove the caps from the email
address shown>

 

[John Nurick]

Hi Phil,

I'd do it as follows:

1) import or link the data as is to a temporary table, so you have up to
three entries in the Parcel_ID field.

2) build a union query that calls the ParseItems() function below I've
pasted at the end of this message to extract individual items from
Parcel_ID. It will look something like this and should deliver one
record per parcelID per owner:

SELECT
ParseItems(Parcel_ID, 0, Chr(10)) AS fParcel_ID,
Owner_First_Name, Owner_Last_Name, ...
FROM TempTable
UNION
SELECT
ParseItems(Parcel_ID, 1, Chr(10)) AS fParcel_ID,
Owner_First_Name, Owner_Last_Name, ...
FROM TempTable
UNION
SELECT
ParseItems(Parcel_ID, 2, Chr(10)) AS fParcel_ID,
Owner_First_Name, Owner_Last_Name, ...
FROM TempTable
WHERE fParcel_ID IS NOT NULL
;

3) create a new, empty table with the fields you need.

4) When the union query is working properly, use it as the source of an
append query to move the data into the new table.





'CODE STARTS ----------------------

Public Function ParseItems( _
List As Variant, _
Item As Long, _
Optional Separator As String = " " _
) As Variant

'Returns the specified item from a list of "words" separated
'by a space (or items separated by Separator).
'Counting starts at zero.
'Returns Null if the list is too short.
'By John Nurick, 2004

Dim arWords As Variant

If IsNull(List) Then
ParseItems = Null
Exit Function
End If

arWords = Split(CStr(List), " ", Item + 2)
If UBound(arWords) < Item Then
ParseItems = Null
Else
ParseItems = arWords(Item)
End If
End Function
'CODE ENDS-----------------------------

 

[John Nurick]

There's an error in the ParseItems code I posted; should be

'CODE STARTS ----------------------

Public Function ParseItems( _
List As Variant, _
Item As Long, _
Optional Separator As String = " " _
) As Variant

'Returns the specified item from a list of "words" separated
'by a space (or items separated by Separator).
'Counting starts at zero.
'Returns Null if the list is too short.
'By John Nurick, 2004

Dim arWords As Variant

If IsNull(List) Then
ParseItems = Null
Exit Function
End If

arWords = Split(CStr(List), Separator, Item + 2)
If UBound(arWords) < Item Then
ParseItems = Null
Else
ParseItems = arWords(Item)
End If
End Function
'CODE ENDS-----------------------------

 

[CAD Fiend]

OK. That explains why it wasn't working on my end. I will go back and
check it and re-run it.

Thank you John.