Need help in separating 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>

*BTW-sorry for cross-posting this, but I am not sure which NG I should
put this into*

 

[Klatuu]

Yes, that is what you will have to do. Here is something that may help,
assuming you will be using vba:

First, you need to know exactly what the value of that mark is. You can get
it by using the Asc() function to find the value. Looking at your post it
looks like it is in position 12, so:

x = Asc(mid(MyString),12,1))

Then use the Split() Function to separate the numbers:
(I use Chr(13) here, because my guess is that would be the value of x from
the code above. If it is not, substitute whatever it is for the 13)

MyArray = Split(MyString, Chr(13))

Now you have an array of all the numbers in that cell. Even if you have
only one number in the cell, it will still work. The array will have the
same number of elements as numbers in the cell. Now to process these numbers
and assuming you are using Option Base 0:

For i = 0 to Ubound(MyArray)
NxtNum = MyArray(i)
'Process each number here.
Next i

 

[CAD Fiend]

Hi Klatuu,

Please see my replies in italics.

Thanks.

Yes, that is what you will have to do. Here is something that may help,
assuming you will be using vba:

I don't know VBA very well. If it is the only way to do this, then I'll have to
work it out. But isn't there a way to do it inside of the Access query dialog
in design view?

First, you need to know exactly what the value of that mark is. You can get
it by using the Asc() function to find the value. Looking at your post it
looks like it is in position 12, so:

x = Asc(mid(MyString),12,1))

Then use the Split() Function to separate the numbers:
(I use Chr(13) here, because my guess is that would be the value of x from
the code above. If it is not, substitute whatever it is for the 13)

MyArray = Split(MyString, Chr(13))

Now you have an array of all the numbers in that cell. Even if you have
only one number in the cell, it will still work. The array will have the
same number of elements as numbers in the cell. Now to process these numbers
and assuming you are using Option Base 0:

For i = 0 to Ubound(MyArray)
NxtNum = MyArray(i)
'Process each number here.
Next i

Is this all of the code? Would it be possible for you to (PLEASE) write this up
so I can see ALL of the code, and also, so I can create just one VBA app inside
Access.

 

[Klatuu]

First, Italics don't come across, but I did see your response.

No, it is too complex for a query because it has to break down the rows
where there are multiple parcel id's.

No, it is not all the code. It will take a little work to do it all for
you. I don't mind doing it, but since I don't have the data I won't be able
to test it. Send my the table layout of the table you want to put the data
in, the layout of the excel sheet that has the data in it. Also, Is the data
in the spreadsheet always new data or can it be updating existing data? What
is the rule if data in the spreadsheet is the same as the data already in the
table?

 

[John Nurick]

See my answer in the .conversions newsgroup - and next time please don't
post the same question separately to multiple newsgroups.

 

[CAD Fiend]

OK. Sorry.

See my answer in the .conversions newsgroup - and next time please don't
post the same question separately to multiple newsgroups.