|
Home > Archive > MS Access data conversion > June 2005 > How do you separate multiple lines from Excel into Access
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How do you separate multiple lines from Excel into Access
|
|
| CAD Fiend 2005-06-21, 1:24 pm |
| 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 2005-06-21, 8:25 pm |
| 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-----------------------------
On Tue, 21 Jun 2005 10:36:40 -0700, CAD Fiend
< pelampeREMOVETHECAPS
@cox.net> wrote:
>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 [Microsoft Access MVP]
Please respond in the newgroup and not by email.
| |
| John Nurick 2005-06-23, 3:24 am |
| 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-----------------------------
On Tue, 21 Jun 2005 22:02:26 +0100, John Nurick
<j.mapSoN.nurick@dial.pipex.com> wrote:
>'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 [Microsoft Access MVP]
Please respond in the newgroup and not by email.
| |
| CAD Fiend 2005-06-25, 3:24 am |
| 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.
John Nurick wrote:
> 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-----------------------------
>
> On Tue, 21 Jun 2005 22:02:26 +0100, John Nurick
> <j.mapSoN.nurick@dial.pipex.com> wrote:
>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
|
|
|
|
|