Create Table Using Split Command

[DiamondGeezer]

Hi all, I'm looking for some help please on a create table macro that, after the initial run, can hopefully change to an append query. I need a macro that will trim & remove commas from the information in one field that existed in CSV format in an excel spreadsheet prior to import. I need to create individual records for each of the items (serial numbers) between the commas.

I am trying to use a table named Source as the source to create a table named SerialSplit and this is what I tried to use which seems to be creating a new table, but the data remains the same.

' Procedure : SpliSerials

' Purpose : One field has comma delimited data - split them to multiple records

' Copy the primary table (Source) and delete all records - the copy becomes SerialSplit

' Row 80 replace the field with the comma delimited data after the rsSource!

' Row 150 - on the rsout - add the name for the split

' in the Immediate window type then enter : SplitToRows

10 On Error GoTo PROC_Error

Dim db As DAO.Database

Dim rsSource As DAO.Recordset

Dim rsOut As DAO.Recordset

Dim SplitToRows() As String

Dim i As Integer

20 Set db = CurrentDb

30 Set rsSource = db.OpenRecordset("Source") ' source table

40 Set rsOut = db.OpenRecordset("SerialSplit") ' copy of empty source table

50 If (Not rsSource.BOF And Not rsSource.EOF) Then

60 rsSource.MoveFirst

70 Do Until rsSource.EOF

80 SerialSplit = Split(rsSource!Serial_Source, ",", -1) ' comma - change to other delimiter here.

90 For i = LBound(SplitToRows()) To UBound(SplitToRows())

100 rsOut.AddNew

110 rsOut("Order Nbr") = rsSource("Order Nbr") ' Keep these the same

120 rsOut("Shipped to City") = rsSource("Shipped to City")

130 rsOut("Shipped to State") = rsSource("Shipped to State")

140 rsOut("Lease_Type") = rsSource("Lease_Type")

' Data was in one field - split by a comma

150 rsOut("Serial_Source") = SerialSplit(i) ' The field name that gets split based on a comma

160 rsOut.Update

170 Next i

180 rsSource.MoveNext

190 Loop

200 Else

210 MsgBox "No Records in Input"

220 End If

230 rsSource.Close

240 Set rsSource = Nothing

250 rsOut.Close

260 Set rsOut = Nothing

270 Set db = Nothing

PROC_Exit:

280 Exit Sub

PROC_Error:

290 'On Error GoTo 0 ' comment out 300 if no message box is needed

300 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in SplitSerials procedure"

310 On Error GoTo 0 ' clear error

Resume PROC_Exit:

End Sub

 

[Ranman256]

There is no need to write code to import data.
use append query.
use docmd.transferText to import CSV data. Set the import spec to allow quotes,or commas, etc.

 

[oleronesoftwares]

I need a macro that will trim & remove commas from the information in one field that existed in CSV format in an excel spreadsheet prior to import

you want to import from excel, in comma-delimited format?

 

[oleronesoftwares]

use docmd.transferText to import CSV data. Set the import spec to allow quotes,or commas, etc.

he wants the commas removed

 

[DiamondGeezer]

There is no need to write code to import data.
use append query.
use docmd.transferText to import CSV data. Set the import spec to allow quotes,or commas, etc.

Thank you for looking, but maybe I wasn't clear. I don't just want to import the data, I need to create new (otherwise duplicate) records for each individual serial number contained within the CSV data

 

[bastanu]

You declare your array as SplitToRows yet on line 80 you use SerialSplit:

' Procedure : SpliSerials

' Purpose : One field has comma delimited data - split them to multiple records

' Copy the primary table (Source) and delete all records - the copy becomes SerialSplit

' Row 80 replace the field with the comma delimited data after the rsSource!

' Row 150 - on the rsout - add the name for the split

' in the Immediate window type then enter : SplitToRows
Public Sub SpliSerials()
10 On Error GoTo PROC_Error
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsOut As DAO.Recordset
Dim SplitToRows() As String
Dim i As Integer

20 Set db = CurrentDb
30 Set rsSource = db.OpenRecordset("Source") ' source table
40 Set rsOut = db.OpenRecordset("SerialSplit") ' copy of empty source table
50 If (Not rsSource.BOF And Not rsSource.EOF) Then

60 rsSource.MoveFirst
70 Do Until rsSource.EOF
80      SplitToRows= Split(rsSource!Serial_Source, ",", -1) ' comma - change to other delimiter here.
90     For i = LBound(SplitToRows()) To UBound(SplitToRows())
100         rsOut.AddNew
110         rsOut("Order Nbr") = rsSource("Order Nbr") ' Keep these the same
120         rsOut("Shipped to City") = rsSource("Shipped to City")
130         rsOut("Shipped to State") = rsSource("Shipped to State")
140         rsOut("Lease_Type") = rsSource("Lease_Type")
        ' Data was in one field - split by a comma
150         rsOut("Serial_Source") = SplitToRows(i) ' The field name that gets split based on a comma
160         rsOut.Update
170     Next i
180 rsSource.MoveNext
190 Loop

200 Else

210     MsgBox "No Records in Input"

220 End If

230 rsSource.Close
240 Set rsSource = Nothing
250 rsOut.Close
260 Set rsOut = Nothing
270 Set db = Nothing

PROC_Exit:

280 Exit Sub

PROC_Error:

290 'On Error GoTo 0 ' comment out 300 if no message box is needed

300 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in SplitSerials procedure"

310 On Error GoTo 0 ' clear error

Resume PROC_Exit:

End Sub

Cheers,

 

[MajP]

creating a new table, but the data remains the same.

Are you absolutely sure? Any chance you are looking at the wrong query and not the new table.
Your code looks correct to me, and I cannot see how that could happen.
The only way that could happen if your split function did not split the string and all of the results are in the first element of the array.
Can you put a debug.print in the code

SplitToRows= Split(rsSource!Serial_Source, ",", -1) ' comma - change to other delimiter here.
debug.print ubound(splitToRows)

The only way that could happen that I could think of is that those things that look like commas are not really commas. The reason I say this is this demo here.

?split("dog; cat; Bird",",")(0)
dog; cat; Bird

If I try to split on something not in the string (no commas only semicolons) it returns all of the string back to the first element of the array.

Try two things
Split(rsSource!Serial_Source, ",")
or
Split(rsSource!Serial_Source, " ")

if it splits on the empty space that tells me that thing that looks like a comma is some other character, therefore not splitting on it.

 

[MajP]

One more thing. Why duplicate. I think you would want a child table with only the fields Order Nbr and Serial. Then it is a related table 1 to many to the source.

 

[bastanu]

@MajP - the OP had an undeclared variable SerialSplit on line 80, what you show in post # 7 was my fix

80 SerialSplit = Split(rsSource!Serial_Source, ",", -1) ' comma - change to other delimiter here.

 

[MajP]

Sorry, I did not realize your code was the fix, that was why I thought it should work. I was looking in there since it was nicely formatted and could not see a problem.

 

[bastanu]

 

[DiamondGeezer]

Thank you for replying. I had got tired looking at it & had changed the procedure name to see if it helped me fix the problem, but I got sloppy & missed some, adding further confusion. I now have;

' Purpose : One field has comma delimited data - split them to multiple records

' Copy the primary table (Source) and delete all records - the copy becomes SerialSplit

' Row 80 replace the field with the comma delimited data after the rsSource!

' Row 150 - on the rsout - add the name for the split

' in the Immediate window type then enter : SerialSplit
Public Sub SplitSerials()
10 On Error GoTo PROC_Error
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsOut As DAO.Recordset
Dim SplitToRows() As String
Dim i As Integer

20 Set db = CurrentDb
30 Set rsSource = db.OpenRecordset("Source") ' source table
40 Set rsOut = db.OpenRecordset("SerialSplit") ' copy of empty source table
50 If (Not rsSource.BOF And Not rsSource.EOF) Then

60 rsSource.MoveFirst
70 Do Until rsSource.EOF
80 SerialSplit = Split(rsSource!Serial_Source, ",", -1) ' comma - change to other delimiter here.
90 For i = LBound(SerialSplit()) To UBound(SerialSplit())
100 rsOut.AddNew
110 rsOut("Order Nbr") = rsSource("Order Nbr") ' Keep these the same
120 rsOut("Shipped to City") = rsSource("Shipped to City")
130 rsOut("Shipped to State") = rsSource("Shipped to State")
140 rsOut("Lease_Type") = rsSource("Lease_Type")
' Data was in one field - split by a comma
150 rsOut("Serial_Source") = SerialSplit(i) ' The field name that gets split based on a comma
160 rsOut.Update
170 Next i
180 rsSource.MoveNext
190 Loop

200 Else

210 MsgBox "No Records in Input"

220 End If

230 rsSource.Close
240 Set rsSource = Nothing
250 rsOut.Close
260 Set rsOut = Nothing
270 Set db = Nothing

PROC_Exit:

280 Exit Sub

PROC_Error:

290 'On Error GoTo 0 ' comment out 300 if no message box is needed

300 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in SplitSerials procedure"

310 On Error GoTo 0 ' clear error

Resume PROC_Exit:

End Sub

It's not running now & I get "Error 3265 (Item not found in this collection.) in SplitSerials procedure

 

[arnelgp]

you have Line numbers in your code, so better make use of it.
it is useful in your error msg (to show which the error occurs).
so change the Msgbox to:

300 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in SplitSerials procedure at Line: " & Erl & ")"

now you know which line the error occurs, can you post the code on that line?

 

[Gasman]

I would expect all those fields with spaces in the names are not doing you any favours?
Use F8 in the debug window and step through line by line.

 

[bastanu]

You have the same problem that I pointed out in my first post, you declare the string array as Dim SplitToRows() As String yet when it comes to filling it using the Split() function on line 80 you use a different variable that is not even declared:
80 SerialSplit = Split(rsSource!Serial_Source, ",", -1) ' comma - change to other delimiter here.
You seem to have fixed the other issues by consistently using SerialSplit in the For Loop so you should just change the declaration to match what you have:Dim SerialSplit() As String

Cheers,

 

[DiamondGeezer]

Line 80 is where it's erroring. I get "Error 3265 (Item not found in this collection.) in SplitSerials procedure at line 80"
I tried changing line 80 to 80 SerialSplit = Split(rsSource!Serial, ",", -1) ' comma - change to other delimiter here. & now I get "Error 9 (Subscript out of range in SplitSerials procedure at Line 90)"

 

[bastanu]

You have to be consistent when using the variables, please try this updated version:

Public Sub SplitSerials()
10 On Error GoTo PROC_Error
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsOut As DAO.Recordset
Dim SplitToRows() As String
Dim i As Integer

20 Set db = CurrentDb
30 Set rsSource = db.OpenRecordset("Source") ' source table
40 Set rsOut = db.OpenRecordset("SerialSplit") ' copy of empty source table
50 If (Not rsSource.BOF And Not rsSource.EOF) Then

60 rsSource.MoveFirst
70 Do Until rsSource.EOF
80 SplitToRows = Split(rsSource!Serial_Source, ",") ' Vlad-use the variable that you declared above 'comma - change to other delimiter here.
90 For i = LBound(SplitToRows) To UBound(SplitToRows)
100 rsOut.AddNew
110 rsOut("Order Nbr") = rsSource("Order Nbr") ' Keep these the same
120 rsOut("Shipped to City") = rsSource("Shipped to City")
130 rsOut("Shipped to State") = rsSource("Shipped to State")
140 rsOut("Lease_Type") = rsSource("Lease_Type")
' Data was in one field - split by a comma
150 rsOut("Serial_Source") = SplitToRows(i) ' The field name that gets split based on a comma
160 rsOut.Update
170 Next i
180 rsSource.MoveNext
190 Loop

200 Else

210 MsgBox "No Records in Input"

220 End If

230 rsSource.Close
240 Set rsSource = Nothing
250 rsOut.Close
260 Set rsOut = Nothing
270 Set db = Nothing

PROC_Exit:

280 Exit Sub

PROC_Error:

290 'On Error GoTo 0 ' comment out 300 if no message box is needed

300 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in SplitSerials procedure"

310 On Error GoTo 0 ' clear error

Resume PROC_Exit:

End Sub

Cheers,

 

[DiamondGeezer]

Thank you, I've tried the above code but am still getting Error 3265 - Item not found in this collection, The field I'm trying to split is named "Serial" so I have removed _source_ from line 80. I have also tried using [Serial] and I've tried renaming the field on the database & in the code, but all to no avail. I also removed lines 110-140 to make sure none of those fields were producing the error.

 

[bastanu]

What line raises the error? On line 80 you are trying to split a field called Serial_Source...
Can you upload a small sample with your table (exactly as it is but with no sensitive data)?
Cheers,

 

[DiamondGeezer]

OK, I've reassigned the field names and got rid of Error 3265. I now have new problems which is "invalid use of null" and "Error 13 type mismatch". These are presumably bought about because some of my records don't have a value in 'Serial' How can I adapt the code so that it ignores records with a null value in Serial? Alternatively can I create a create a query that filters only records with a value in Serial that I can then use as my source? My current macro is;
Public Function Split()

10 On Error GoTo PROC_Error
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsOut As DAO.Recordset
Dim SplitToRows() As String
Dim i As Integer
20 Set db = CurrentDb
30 Set rsSource = db.OpenRecordset("Source")
40 Set rsOut = db.OpenRecordset("SerialSplit")
50 If (Not rsSource.BOF And Not rsSource.EOF) Then
60 rsSource.MoveFirst
70 Do Until rsSource.EOF
80 SplitToRows = Split(rsSource!Serial, ", ", -1)
90 For i = LBound(SplitToRows()) To UBound(SplitToRows())
100 rsOut.AddNew
110 rsOut("Customer Purchase Order") = rsSource("Customer Purchase Order") ' Keep these the same
120 rsOut("Shipped to City") = rsSource("Shipped to City")
130 rsOut("Shipped to State") = rsSource("Shipped to State")
140 rsOut("Item Description") = rsSource("Item Description")
150 rsOut("Quantity") = rsSource("Quantity")
160 rsOut("Unit Price") = rsSource("Unit Price")
170 rsOut("Serial") = SplitToRows(i) ' Data was in one field - split by a comma
180 rsOut.Update
190 Next i '
200 rsSource.MoveNext
210 Loop
220 Else
230 MsgBox "No Records in Input"
240 End If
250 rsSource.Close
260 Set rsSource = Nothing
270 rsOut.Close
280 Set rsOut = Nothing
290 Set db = Nothing
PROC_Exit:
300 Exit Function
PROC_Error:
310 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in SplitToRows procedure"
320 On Error GoTo 0

Resume PROC_Exit:
End Function

I've attached a selection of records that I exported to Excel from my source table