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

  • Thread starter Thread starter svanga
  • Start date Start date
S

svanga

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
 
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.
 
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
 
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,

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
 
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,

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
 
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.
 
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
------------------------------------------------------------------------------------------------------------------------------------------
 
Back
Top