Access VBA - How to split data in one field and insert into other fields in an existing table

[Peter_W]

Hello all,

I have a piece of code regarding the "Split" function in Access VBA. It works great.

That is, basically, my sub takes a block of data, as a string, received by the Access database as a copy from the clipboard.

And then the current code here, converts the string to an array, and then places each line of data into a field called INPUT_FIELD in an Access table called VALUES. I get the right number of rows in the table after running the code

However, that data consists of 3 different columns of data from the original source file.

So, I would like to Split the data currently in INPUT_FIELD as follows, and separated by "pipes":

9834 | 7729 | 1536

Into inputs into INPUT_FIELD2, INPUT_FIELD3, and INPUT_FIELD4 respectively, which are existing fields within the same table VALUES, as follows:

9834 to INPUT_FIELD2 (different column in same row of data)
7729 to INPUT_FIELD3 " " "
1526 to INPUT_FIELD4 " " "


I sense I need the Split function, and/or maybe combined with Insert Into...Values code, but have failed on many attempts, while searching a lot too. Thank you in advance for any feedback. I am at the point of waving my white flag of surrender to VBA.


Working code:

Sub CopyFromClipToTable

Dim strString As String
Dim arrString() As String
Dim rs As DAO.Recordset
Dim i As Integer
Dim Db As DAO.Database


Set Db = CurrentDb
Set rs = Db.OpenRecordset("VALUES")

' Retrieve clipboard contents into data object
DataObj.GetFromClipboard

' Clipboard to string variable
strString = DataObj.GetText

' Convert string variable to array
arrString = Split(strString, vbNewLine)
' Post Array to Table using DAO recordset

For i = 0 To UBound(arrString) - 1
'Update Table with data from clipboard
rs.AddNew
rs!INPUT_FIELD = arrString(i)
rs.Update
Next I



Set rs = Nothing

End Sub

 

[Joe4]

So, can you show us an example of your current data, and exactly what your current VBA code does to it, after you run it?
I would like to take your sample file and run your code against it and see what it does.

 

[Peter_W]

So, can you show us an example of your current data, and exactly what your current VBA code does to it, after you run it?
I would like to take your sample file and run your code against it and see what it does.

Thanks Joe for the quick reply. Below is sample input, in tabular form, which is fed into the Access database by the copy from clipboard code (not exactly like my very simplified example, but again, these are text-formatted fields separated by pipes, by the original SAP file:

Category1 Cat2 Cat3
3300065815 FO 30110764
3300066027 FO 30110764
3300066165 FO 30110770
3300072438 FO 31780962
3300072858 FO 31780965
3300073937 FO 31780972
5000477829 NB 30110776
5000477830 NB 30110776
5000491340 NB 30110827
5000491806 NB 30110830
5000491806 NB 30110830
5000491806 NB 30110830
5000491806 NB 30110830
5000491806 NB 30110830
3300491806 NB 30110830
3300491806 NB 30110830
4200491806 NB 30110830
4200492080 NB 30110822
4200492080 NB 30110822
4200498873 NB 30110828
4200498873 NB 30110828
4200498873 NB 30110828
4700498873 NB 30110828
4800498873 NB 30110828
4900498873 NB 30110828

 

[Joe4]

I would like the exact structure of the original SAP file that you are running this against, so I can try to emulate your exact scenario and test it all out.
To me, your original description sounds like you have one long string with fields separated by Pipe symbols, and you are using the Split command to break it into multiple rows (records), but then are left with three fields in one, instead of being split into three.
Do I have that right?

What really would be must helpful is to see the following:
- sample of original file, EXACTLY how it appears (without any modifications)
- results you are getting with your code
- what your desired results should look like

 

[stumac]

Something like the following may work (untested)

 Dim strString As String
 Dim arrString() As String
 Dim splitValues() As String
 Dim rs As DAO.Recordset
 Dim i As Integer
 Dim Db As DAO.Database

 Set Db = CurrentDb
 Set rs = Db.OpenRecordset("VALUES")
 ' Retrieve clipboard contents into data object
 DataObj.GetFromClipboard
 ' Clipboard to string variable
 strString = DataObj.GetText
 ' Convert string variable to array
 arrString = Split(strString, vbNewLine)
 ' Post Array to Table using DAO recordset
 For i = 0 To UBound(arrString) - 1
 splitValues = Split(arrString, "|")
 'Update Table with data from clipboard
 rs.AddNew
 rs!INPUT_FIELD = arrString(i)
 rs!INPUT_FIELD2 = splitValues(0)
 rs!INPUT_FIELD3 = splitValues(1)
 rs!INPUT_FIELD4 = splitValues(2)
 rs.Update
 Next i
 Set rs = Nothing

 

[Peter_W]

Thank you Joe4 and stumac for your replies.

I tried the above code from stumac, and the code stops at the line below, and the error message is "Compile error: Type mismatch"

splitValues = Split(arrString, "|")

The code solution looks good to me. My table fields are all Text format, and I believe I am importing all text data. Any clues as to the type mismatch error? Thanks again in advance. Best, Peter_W

 

[stumac]

Oops my bad, try changing to:

 splitValues = Split(arrString(i), "|")

 

[Peter_W]

Oops my bad, try changing to:

 splitValues = Split(arrString(i), "|")

Thanks, I added the (I), but now the code breaks here, at this line:

DataObj.GetFromClipboard

I was testing with the same text data yesterday, and this line was not a problem. Any thoughts, thanks, Pete.

 

[stumac]

Hmmm, I was surprised it was working when you posted it. When using this method in the past I've had to set a reference to the msforms library and then declare the data object, something like:

Dim DataObj As MSForms.DataObject
 Set DataObj = New MsForms.DataObject

 

[Peter_W]

Hmmm, I was surprised it was working when you posted it. When using this method in the past I've had to set a reference to the msforms library and then declare the data object, something like:

Dim DataObj As MSForms.DataObject
 Set DataObj = New MsForms.DataObject

Thank you stumac for the de-bug. I will try this fix soon and get back. I have this distracting thing called my day job that I have to attend to now. All the best, Peter_W