string split between 2 fields, concatenate w/o comma

[kimpal]

Hello,
I have 5 simialr fields, each field is either null, has 1 value or multiple values separted by commas.
The system which is producing this excel extract had a character limit, when reached, the string would proceed in the next column.

Goal: to concatenate all the unique container numbers into the 'Containers' field, keeping a comma between each value.
Challenges:

some fields are null then the field after has data

some values end in comma, some do not

a 128 char limit in the source system caused the values to split across multiple fields, so these needs to be concatenated without a separating comma (see red highlight)

The long text field size with rich text format still seems to be truncating or not having enough space for more than 256 chars (see blue highlight)


Here is my code: Desparately hoping for some help.
[BEGIN CODE]
Sub Process_Containers()
Dim rst As Recordset
Set rst = currentdb.OpenRecordset(Name:="tblImport")

Dim C23 As String
Dim C34 As String
Dim C45 As String

If Len(rst![container no2]) = 128 And IsNull(rst![Container No3]) = True Then
C23 = ""
ElseIf Len(rst![container no2]) = 128 And IsNull(rst![Container No3]) = False Then
C23 = ""
Else
C23 = ","
End If

If Len(rst![Container No3]) = 128 And IsNull(rst![Container No4]) = True Then
C34 = ""
ElseIf Len(rst![Container No3]) = 128 And IsNull(rst![Container No4]) = False Then
C34 = ""
Else
C34 = ","
End If

If Len(rst![Container No4]) = 128 And IsNull(rst![Container No5]) = True Then
C45 = ""
ElseIf Len(rst![Container No4]) = 128 And IsNull(rst![Container No5]) = False Then
C45 = ""
Else
C45 = ","
End If

rst.MoveFirst
Do Until rst.EOF

rst.Edit
If Right(rst![container no2], 1) = "," Then
rst![Containers] = rst![container no2] & C23 & rst![Container No3] & C34 & rst![Container No4] & C45 & rst![Container No5]
Else
rst![Containers] = rst![container no1] & "," & rst![container no2] & C23 & rst![Container No3] & C34 & rst![Container No4] & C45 & rst![Container No5]
End If
rst.Update
rst.MoveNext

Loop
End Sub
[END CODE]

Then I run this code 4 times to remove trailing commas
[BEGIN CODE]
Sub TrimCommas()
Dim rst As Recordset
Set rst = currentdb.OpenRecordset(Name:="tblImport")

rst.MoveFirst
Do Until rst.EOF
' If IsNull(rst![container no1]) = True Then
' rst.MoveNext
' Else

If Right(rst![Containers], 1) = "," Then
rst.Edit
rst![Containers] = Left(rst![Containers], Len(rst![Containers]) - 1)
rst.Update
Else
' Exit Sub
' End If

End If

rst.MoveNext
Loop
End Sub
[END CODE]

I am hopeful there is a better way to get rid of the trailing commas as well.
Thanks in advance your assistance!

 

[MajP]

Goal: to concatenate all the unique container numbers into the 'Containers' field, keeping a comma between each value.

Why would you want to do that? A waste of time in my opinion. You will not be able to do anything with it. What would you do with that big long string? Can't do any viable queries. You should put each value as a record in a table, and relate it to some parent record by a foreign key. What do these container values relate to? To determine a split record is the rule as follows:

If the current container is length 128 and the last character is not a "," then if the next container is not null the values before the first comma belong to the last number in the previous container.

 

[kimpal]

Ji Majp - Excellent questions. I will clarify.

Background: These records represent inter-company invoices issued to the US office by other offices aruond the world. We are a shipping company, so the majority of these containers represent actual cargo containers, which become the reference to know which shipment in SAP to post the charges to. Air shipments are all numeric, while ocean shipments are 4 char + 7 numeric, and the field is free text, so really we can receive any combination if the user manually enters the data. I should also mention this information is extracted from a specially created tranaction code which marryies GL data with the order data.

Goal: the reason for having the references combined into one field is because we then, as you rightly predicted, we need to split the records, one record/row per container in the field. I then also need to prorate that line item amount across the newly created rows. Once this step is done, then we fill in a WINSHUTTLE template (fancy macro run on SAP) which automates updating the charge lines onto the sales order in SAP.

I was trying to do this in steps, first, get all the containers into 1 field, properly separted by commas, then I can count how many records that one will split into (based on number of unique container references) to be used for prorating the invoice line amounts, and then use the comma to trigger when a new record should be created.

End goal:

If I can do all steps in one go, that would be great!

 

[MajP]

Any chance you can post a spreadsheet like that with some real data? Would be easier to test. Just need a couple of rows showing different possibilities of empty containers, split records, and non split records. I think it may be easier to do it in one swoop instead of doing a bunch of string manipulations you can use the Split function to get an array (or arrays) of values. You can do some checks for the split records and merge them into a single array value. Then loop the arrays doing an insert query into your new table of values.

 

[MajP]

Public Sub CreateContainers()
  Const DomainName = "tblImport"
  Const StartContainerIndex = 1
  Const EndContainerIndex = 5
  'I used the index of the field (zero based) in case the field name changes or you
  'add more fields
  
  Dim i As Integer
  Dim j As Integer
  Dim RS As DAO.Recordset
  Dim arr_Containers() As String
  Dim strContainers As String
  Dim LeftOver As String
  Dim PreviousLeftOver As String
  Dim DividedRecord
  Dim InvoiceID As String
  Dim strSql As String
  Set RS = CurrentDb.OpenRecordset(DomainName)
  
  Do While Not RS.EOF
    InvoiceID = RS.Fields("Inv_+_InvLine")
    LeftOver = ""
    For i = StartContainerIndex To EndContainerIndex
      If IsNull(RS.Fields(i)) Then
        Exit For
      End If
      strContainers = RS.Fields(i)
      DividedRecord = IsDivided(strContainers)
      arr_Containers = Split(strContainers, ",")
      PreviousLeftOver = LeftOver
      If Not PreviousLeftOver = "" Then arr_Containers(0) = PreviousLeftOver & arr_Containers(0)
      If DividedRecord Then
        LeftOver = arr_Containers(UBound(arr_Containers))
        ReDim Preserve arr_Containers(UBound(arr_Containers) - 1)
      Else
        LeftOver = ""
      End If
      
      For j = 0 To UBound(arr_Containers)
        If Not arr_Containers(j) = "" Then
          strSql = "Insert into tblInvoice_Containers (InvoiceID,ContainerID) values ('" & InvoiceID & "', '" & arr_Containers(j) & "')"
          CurrentDb.Execute strSql
        End If
      Next j
     Next i
    RS.MoveNext
    
  Loop
End Sub
End Function
Public Function IsDivided(strContainers As String) As Boolean
  IsDivided = (Len(strContainers) = 128 And Right(strContainers, 1) <> ",")
End Function

That takes this:

and makes this

invoiceID containerID
123+1	9QX35L4F6Z6NT7P
123+1	OY953HBYI5VLHMB
123+1	FSYRSZQMMCK2MDN
123+1	WFQKUL1VQ2EMUXQ
123+1	TN8QYA2SSJE8NJG
123+1	LU2R6GT5J4A778P
123+1	ICGVVLJWDBCKLJE
123+1	2MZZ602WO3EH8RT
123+1	JREXF27AKX3QVTN
123+1	FUD1NLZKIYBU65E
123+2	1FXXN348J40RUP
123+2	R97QGOEJRNR2FP
123+2	G8GZR2BOM3O3TF
123+2	QUK0QCDN273VWQ
123+2	PA5RASSOLNIO9R
123+2	D70HEUITUSHGD4
123+2	CWE8RPT98BJGGZ
123+2	7PA3HCUDZD6NPU
123+2	3OH3REHQ3P8MZZ Note
123+2	32PT0JST7AZ3CX
123+2	7KWUSI4I9NNLE6
123+2	AE5Y8ONXLZYURB
123+2	GL5U2QN9YNLWA1
123+2	H0UU5CUJHFZ71B
123+2	AATWLP2C0FM8AC
123+2	2NTI75XVBJW5ZN
123+2	CQTE11GLU5PMNT
123+2	94UF09GSG3WNE2 Note
123+2	YSJVQRSPNA7LDR
123+2	HYAA8QXAERIQAR

It loop all records and gets the invoice id
Next it loops the applicable container fields defined by the indices for those fields
It splits all the values into an array
It checks to see if the len is 128 and does not end in a comma if so that indicates a divided field.
If the field is divided it does not insert the last value of the array.
It saves the last value and concatenate the value with the first value of the next field
It loops the array and does an insert query

The only issue is how to handle a field that is 128 and it ends at the very end of an entry. The only way to tell it is not split would be a specific length on a container or some other rule.

 

[MajP]

[b]Public Sub CreateContainers2()
  Const DomainName = "tblImport"
  Const StartContainerIndex = 1
  Const EndContainerIndex = 5
  'I used the index of the field (zero based) in case the field name changes or you
  'add more fields
  
  Dim i As Integer
  Dim j As Integer
  Dim RS As DAO.Recordset
  Dim arr_Containers() As String
  Dim strContainers As String
  Dim InvoiceID As String
  Dim strSql As String
  Set RS = CurrentDb.OpenRecordset(DomainName)
  
  Do While Not RS.EOF
    InvoiceID = RS.Fields("Inv_+_InvLine")
    strContainers = ""
    For i = StartContainerIndex To EndContainerIndex
      If IsNull(RS.Fields(i)) Then
        Exit For
      End If
      If strContainers = "" Then
        strContainers = RS.Fields(i)
      Else
        If IsDivided(RS.Fields(i - 1)) Then
          strContainers = strContainers & RS.Fields(i)
        Else
          strContainers = strContainers & "," & RS.Fields(i)
        End If
      End If
    Next i
    Debug.Print strContainers
    arr_Containers = Split(strContainers, ",")
    For j = 0 To UBound(arr_Containers)
       If Not arr_Containers(j) = "" Then
          strSql = "Insert into tblInvoice_Containers (InvoiceID,ContainerID) values ('" & InvoiceID & "', '" & arr_Containers(j) & "')"
          CurrentDb.Execute strSql
        End If
      Next j
   RS.MoveNext
  Loop
End Sub[/b]

Here is a different version. Might be a little simpler. In this version you concatenate everything first then split it and do your insert query. If the field is less than 128 you concatenate and add a '," with the next field. If it is 128 then you concatenate without a comma.

 

[kimpal]

Hi MajP,
Uploaded a test file.
I will also try to test today and will let you know how it goes.

Thanks so much!

Kim

 

[kimpal]

Hi,
Thank you for sharign this! I am able to run the code but it is not updating the new table (tblImport_Split).
The array J is getting stuck on one container value and not cycling through them.
Below is the code where I updated my actual table and field names.

The table with the data is called 'tblImport', the invoice ID is called 'unique' and the new insert table is tblImport_Split.
I should mention there are many other columns in the table that need to be copied to the new record as well.


Public Sub CreateContainers()
Const DomainName = "tblImport"
Const StartContainerIndex = 1
Const EndContainerIndex = 5
'I used the index of the field (zero based) in case the field name changes or you
'add more fields

Dim i As Integer
Dim j As Integer
Dim RS As DAO.Recordset
Dim arr_Containers() As String
Dim strContainers As String
Dim LeftOver As String
Dim PreviousLeftOver As String
Dim DividedRecord
Dim InvoiceID As String
Dim strSql As String
Set RS = currentdb.OpenRecordset("tblIMPORT")
RS.MoveFirst
Do While Not RS.EOF

InvoiceID = RS.Fields("Unique") 'InvoiceID = RS.Fields("Inv_+_InvLine")
LeftOver = ""
For i = StartContainerIndex To EndContainerIndex
If IsNull(RS.Fields(i)) Then
Exit For
End If
strContainers = RS.Fields(i)
DividedRecord = IsDivided(strContainers)
arr_Containers = Split(strContainers, ",")
PreviousLeftOver = LeftOver
If Not PreviousLeftOver = "" Then arr_Containers(0) = PreviousLeftOver & arr_Containers(0)
If DividedRecord Then
LeftOver = arr_Containers(UBound(arr_Containers))
ReDim Preserve arr_Containers(UBound(arr_Containers) - 1)
Else
LeftOver = ""
End If

For j = 0 To UBound(arr_Containers)
If Not arr_Containers(j) = "" Then
'strSql = "Insert into tblImport_split (unique,Containers) values ('" & InvoiceID & "', '" & arr_Containers(j) & "')"
strSql = "Insert into tblImport_split (InvoiceID,ContainerID) values ('" & InvoiceID & "', '" & arr_Containers(j) & "')"
currentdb.Execute strSql
End If
Next j
Next i
RS.MoveNext

Loop
End Sub

 

[MajP]

I would first make a small test query with one or two records so you can test this. Then I would try to debug with a couple of print lines to make sure you are looping the correct fields, getting the correct string, etc. Also to make your post readable use the tags on the edit bar to wrap it. The code tag is left of the tag that looks like a wrapped present

Set RS = currentdb.OpenRecordset("tblIMPORT")
RS.MoveFirst
Do While Not RS.EOF
degug.print rs.recordcount
InvoiceID = RS.Fields("Unique") 'InvoiceID = RS.Fields("Inv_+_InvLine")
LeftOver = ""
debug.print "leftover: " & leftover
For i = StartContainerIndex To EndContainerIndex
If IsNull(RS.Fields(i)) Then
Exit For
End If
debug.print "Field Name: " & rs.fields(i)
strContainers = RS.Fields(i)
debug.print "StrContainers: " & strcontainers
DividedRecord = IsDivided(strContainers)
arr_Containers = Split(strContainers, ",")
PreviousLeftOver = LeftOver
If Not PreviousLeftOver = "" Then arr_Containers(0) = PreviousLeftOver & arr_Containers(0)
If DividedRecord Then
LeftOver = arr_Containers(UBound(arr_Containers))
ReDim Preserve arr_Containers(UBound(arr_Containers) - 1)
Else
LeftOver = ""
End If

For j = 0 To UBound(arr_Containers)
If Not arr_Containers(j) = "" Then
'strSql = "Insert into tblImport_split (unique,Containers) values ('" & InvoiceID & "', '" & arr_Containers(j) & "')"
strSql = "Insert into tblImport_split (InvoiceID,ContainerID) values ('" & InvoiceID & "', '" & arr_Containers(j) & "')"
currentdb.Execute strSql
End If
Next j
Next i
RS.MoveNext

Loop
End Sub

 

[kimpal]

Hi MajP,
I followed your advice and got it to work! My destination table had some validation rules that I didn't realize. I am able to see the split and it looks great. My last thing to do is get the rest of the fields to follow into the new table. There are alot of them, I could add them by name, one by one but wondering if it is possible to loop through all fields in the record and return them all?

Would you share the syntax to add more column names to the SQL to transfer to the new table?

Thank you so much! I can't tell you how much time this code will save my team!!

 

[kimpal]

Ok,I changed something in my origin table and now the records are not splitting in the destination table. uugggghhhh

 

[kimpal]

Update: it is working - I put a key on the field which needs to repeat.
I also just made a simple append query connecting the split table to the origin table and append all additional fields to a final table.

For real this time .... my last item to address is to prorate the charge amount associated to each line by the number of times the line splits (number of containers), in order not to duplicate the totals... I know I have add a variable to count the containers and then do the division on the field called [Amount USD], and update the new split value in a new column called [Amount USD Split] .. but I don't know where to start, not good with the looping.

 

[strongm]

>IsDivided = (Len(strContainers) = 128 And Right(strContainers, 1) <> ",")

Careful, there's at least one edge condition where this fails. If there are 32 or more container numbers ...