Splitting multiple values of a column in to new rows while importing data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

svanga - 28 Aug 2006 21:35 GMT

I am trying to import an excel spreadsheet in to MS Access.

One of the columns I am trying to import containes multiple values,
separated
by |~*~|. For each value in this column, I need to create a new row
during
import, replicating all of the values from the other columns in the
spreadsheet.

Is there a way to do this.

Thanks,
Sreekanth

Reply to this Message

Klatuu - 29 Aug 2006 14:30 GMT

You will not be able to split the values out and create new records during
the transfer. I am not the worlds expert with SQL, but I think it would also
be difficult to do it using SQL. If someone else has an idea, please chime
in. I can, however, offer a solution using Recordset processing and some VBA.

For example purposes, I will assume the spreadsheeet and the destination
Access table have the same record layout and that the fifth field is the
field containing multiple values.

Dim rstXLAs Recordset
Dim rstAC As Recordset
Dim varSplitter As Variant
Dim lngCtr As Long
Dim lngFld as Long
Const conScode As String = "|~*~|"

'Link to the spreadsheet
Docmd.TranferSpreadsheet acLink, , "_Trans", "F:\SomeDir\SomeSheet.xls", _
True

'Establish the Recordsets
Set rstAC = CurrentDb.OpenRecordset("SomeTable", dbOpenDynaset)
Set rstXl = CurrentDb.OpenRecordset("_Trans", dbOpenDynaset)
If rst.Recordcount > 0 Then
rst.MoveLast
rst.MoveFirst
Else
MsgBox "No Records to Process"
rstXl.Close
rstAc.Close
Set rstXl = Nothing
Set rstAc = Nothing
Exit Sub
End If

With rstXl
Do While Not .EOF
'Put the values from the multi value field in an arrray
varSplitter = Split(!MultiField, conScode)
'Create a record for each row in the array
For lngCtr = UBound(varSplitter) to 0 Step -1
.AddNew
For lngFld = 0 to .Fields.Count -1
If lngFld = 4 Then
rstAc.Fields(4) = varSplitter(lngCtr)
Else
rstAc.Fields(lngFld) = .Fields(lngFld)
End If
Next lngFld
.Update
Next lngCtr
Loop
End With 'rstXl

rstXl.Close
rstAc.Close
Set rstXl = Nothing
Set rstAc = Nothing

Be aware this is untested air code and may need some debugging.

> I am trying to import an excel spreadsheet in to MS Access.
>
[quoted text clipped - 9 lines]
> Thanks,
> Sreekanth

Reply to this Message

svanga - 29 Aug 2006 21:52 GMT

Hi,

Thanks for the solution. I am facing a problem while "creating a record
for each row in the array". Run-time error '3027' is displayed saying
Cannot update. Database object is read-only. This error is displayed
while executing the .AddNew method.

Can you please help me on this?

Thanks,
Sreekanth

> You will not be able to split the values out and create new records during
> the transfer. I am not the worlds expert with SQL, but I think it would also
[quoted text clipped - 70 lines]
> > Thanks,
> > Sreekanth

Reply to this Message

Klatuu - 29 Aug 2006 22:04 GMT

Sorry, I don't see the problem in the code I posted.
First, try opening the table you are trying to write to (rstAc) and see if
you can add a new record manually. If that works, then please post back with
your version of the code. if you are not able to add a record manually, then
there is something about the table that is causing it.
Also be sure you are working with the correct tables. You may have them
backwards. You can't update a linked XL table.
rstAC should be the Access table
rstXL should be the linked spreadsheet.

Please let me know what you find.

> Hi,
>
[quoted text clipped - 82 lines]
> > > Thanks,
> > > Sreekanth

Reply to this Message

svanga - 29 Aug 2006 23:20 GMT

Hi,

I have modified the code a bit to resolve the Run-time error '3027'
problem.
rstAC.AddNew instead of .AddNew
rstAC.Update instead of .Update

But, I am experiencing a new problem.

For e.g. the input spreadsheet has 1 row like,

Name Locn CGroup Instr LOCATIONTYPE
1 a b c d
FACTORY|~*~|LAB|~*~|LIAISON_OFFICE|~*~|LICENSEE|~*~|VENDOR|~*~|

I expect 5 records with location type splitted.

Some thing seems to be wrong with the loop. It is creating several
thousands of records instead of 5.

Below is my code.

Dim rstXL As Recordset
Dim rstAC As Recordset
Dim varSplitter As Variant
Dim lngCtr As Long
Dim lngFld As Long
Const conScode As String = "|~*~|"

'Link to the spreadsheet
DoCmd.TransferSpreadsheet acLink, , "InputTable", "C:\Documents and
Settings\svanga\Desktop\CoreCompetency\FSPCC.xls", True

'Establish the Recordsets
Set rstAC = CurrentDb.OpenRecordset("OutputTable", dbOpenDynaset)
Set rstXL = CurrentDb.OpenRecordset("InputTable", dbOpenDynaset)
If rstXL.RecordCount > 0 Then
rstXL.MoveLast
rstXL.MoveFirst
Else
MsgBox "No Records to Process"
rstXL.Close
rstAC.Close
Set rstXL = Nothing
Set rstAC = Nothing
Exit Sub
End If

With rstXL
Do While Not .EOF
'Put the values from the multi value field in an arrray
varSplitter = Split(!LOCATIONTYPE, conScode)
'Create a record for each row in the array
For lngCtr = UBound(varSplitter) To 0 Step -1
rstAC.AddNew
For lngFld = 0 To .Fields.Count - 1
If lngFld = 4 Then
rstAC.Fields(4) = varSplitter(lngCtr)
Else
rstAC.Fields(lngFld) = .Fields(lngFld)
End If
Next lngFld

rstAC.Update
Next lngCtr
Loop
End With 'rstXl

rstXL.Close
rstAC.Close
Set rstXL = Nothing
Set rstAC = Nothing

> Sorry, I don't see the problem in the code I posted.
> First, try opening the table you are trying to write to (rstAc) and see if
[quoted text clipped - 94 lines]
> > > > Thanks,
> > > > Sreekanth

Reply to this Message

Klatuu - 30 Aug 2006 13:58 GMT

Well, I did say it was untested air code. I notices there was no MoveNext at
the bottom of the loop to more to the next record in rstXl, so you will see
that added.

The results you are getting indicates there is a problem with the Split
function. What version of Access are you on? I know the Split function is
fairly new, but I don't remember what version it came in. If you are not
familiar with it, it turns the string in the first argument into an arrray
using the string in the second argment. VBA Help referrs to it as a
delimiter character, but I did test it using the string you posted. |~*~|

In case there is a translation issue, you may want to check the ASCII value
of the characters

| = 124

~ = 126
* = 42

The other thing to check would be this line:
If lngFld = 4 Then
rstAc.Fields(4) = varSplitter(lngCtr)

It is checking to see if it is the field to receive the split value. This
assumes that the field is the 5th field in the record (first field is 0).

With rstXl
Do While Not .EOF
'Put the values from the multi value field in an arrray
varSplitter = Split(!MultiField, conScode)
'Create a record for each row in the array
For lngCtr = UBound(varSplitter) to 0 Step -1
rstAc.AddNew
For lngFld = 0 to .Fields.Count -1
If lngFld = 4 Then
rstAc.Fields(4) = varSplitter(lngCtr)
Else
rstAc.Fields(lngFld) = .Fields(lngFld)
End If
Next lngFld
rstAc.Update
Next lngCtr
.MoveNext
Loop
End With 'rstXl

> Hi,
>
[quoted text clipped - 167 lines]
> > > > > Thanks,
> > > > > Sreekanth

Reply to this Message

svanga - 30 Aug 2006 19:21 GMT

Hi,

I have 5 columns in the sheet and the last column has multiple values.
Columns:
NAME    CHARACTERISTIC    CHARACTERISTICSEQ    COMPETENCYGROUP    EVALUATIONSCALE
    LOCATIONTYPE

Values:
CORPORATE_RESPONSIBILITY     Factory CR
Rating    1    CORPORATE_RESPONSIBILITY    SCALE_1_5    FACTORY|~*~|LAB|~*~|LIAISON_OFFICE|~*~|LICENSEE|~*~|VENDOR

When I execute the below code, I get an Runtime error 3265 <Item not
found in this collection.>

I have also tried declaring "lngCtr As Variant" and changing
rstAC.Fields(5) . I am using MS Access 2003.

Please help.

Below is the code I used

With rstXL
Do While Not .EOF

'Put the values from the multi value field in an arrray
varSplitter = Split(!LOCATIONTYPE, conScode)
'Create a record for each row in the array
For lngCtr = UBound(varSplitter) To 0 Step -1
rstAC.AddNew
For lngFld = 0 To .Fields.Count - 1
If lngFld = 4 Then
rstAC.Fields(4) = varSplitter(lngCtr)
Else
rstAC.Fields(lngFld) = .Fields(lngFld)

End If
Next lngFld
rstAC.Update
Next lngCtr
rstXL.MoveNext
Loop
End With 'rstXl

rstXL.Close
rstAC.Close
Set rstXL = Nothing
Set rstAC = Nothing

> Well, I did say it was untested air code. I notices there was no MoveNext at
> the bottom of the loop to more to the next record in rstXl, so you will see
[quoted text clipped - 211 lines]
> > > > > > Thanks,
> > > > > > Sreekanth

Reply to this Message

Klatuu - 30 Aug 2006 19:37 GMT

If you have 5 fields, then (5) will cause the error.
The fields will be numbered (0) through (4)
So if you did
?rstXl.Fields(0).Name
it would return
NAME

?rstXl.Fields(4).Name
will return
LOCATIONTYPE

So, (Lucky Guess), my orginal code where we are putting in one of the values
in the array.

If lngFld = 4 Then
rstAC.Fields(4) = varSplitter(lngCtr)
Else
rstAC.Fields(lngFld) = .Fields(lngFld)
would be correct.

One other thing, look at the Option statments at the top of the module this
code is in. There should be one that says
Option Base 1
Then it will cause the array processing I posted to be incorrect.
You will have to change this line
For lngCtr = UBound(varSplitter) To 0 Step -1
to
For lngCtr = UBound(varSplitter) To 1 Step -1

If there is not Option Base statement or it is Option Base 0, then this
should be correct.

> Hi,
>
[quoted text clipped - 259 lines]
> > > > > > > Thanks,
> > > > > > > Sreekanth

Reply to this Message

svanga - 30 Aug 2006 20:23 GMT

Hey.....I got it. Thanks a lot.....

The problem was with the O/P table. Its structure was different from
the I/P table, so I got the Runtime error (3265 <Item not found in this
collection.>) when trying to write the value in to the O/P field.
(rstAC.Fields(5) = varSplitter(lngCtr))

Below is the final code which works perfectly!

---------------------------------------------------------------------------------------------------------------------------------------------------------
Public Sub MyProcedure()

Dim rstXL As Recordset
Dim rstAC As Recordset
Dim varSplitter As Variant
Dim lngCtr As Variant
Dim lngFld As Long
Const conScode As String = "|~*~|"

'Link to the spreadsheet
DoCmd.TransferSpreadsheet acLink, , "InputTable", "C:\Documents and
Settings\svanga\Desktop\Test
Cases\Supplier\BO\CoreCompetency\FSPCC.xls", True

'Establish the Recordsets
Set rstAC = CurrentDb.OpenRecordset("OutputTable", dbOpenDynaset)
Set rstXL = CurrentDb.OpenRecordset("InputTable", dbOpenDynaset)
If rstXL.RecordCount > 0 Then
rstXL.MoveLast
rstXL.MoveFirst
Else
MsgBox "No Records to Process"
rstXL.Close
rstAC.Close
Set rstXL = Nothing
Set rstAC = Nothing
Exit Sub
End If

With rstXL
Do While Not .EOF

'Put the values from the multi value field in an arrray
varSplitter = Split(!LOCATIONTYPE, conScode)
'Create a record for each row in the array
For lngCtr = UBound(varSplitter) To 0 Step -1
rstAC.AddNew
For lngFld = 0 To .Fields.Count - 1
If lngFld = 5 Then
rstAC.Fields(5) = varSplitter(lngCtr)
Else
rstAC.Fields(lngFld) = .Fields(lngFld)

End If
Next lngFld
rstAC.Update
Next lngCtr
rstXL.MoveNext
Loop
End With 'rstXl

rstXL.Close
rstAC.Close
Set rstXL = Nothing
Set rstAC = Nothing

End Sub
------------------------------------------------------------------------------------------------------------------------------------------

> If you have 5 fields, then (5) will cause the error.
> The fields will be numbered (0) through (4)
[quoted text clipped - 291 lines]
> > > > > > > > Thanks,
> > > > > > > > Sreekanth

Reply to this Message

Klatuu - 30 Aug 2006 20:31 GMT

Fantastic! Glad I could help.
Would you please rate the final response?
Thanks

> Hey.....I got it. Thanks a lot.....
>
[quoted text clipped - 279 lines]
> > > > > > > Cannot update. Database object is read-only. This error is displayed
> > > > > > > while executing the .AddNew method.

Reply to this Message