insert values with different delimiter

Question

i want to insert values into a table, with an INSERT INTO statement but i would like to use a different delimiter (not a comma) how do i do this?

i dont want to use a comma for the following reason:

the data is in this format:

|something|somethingelse|something3 ,moretextinsamefield|

field1 = "something"
field2 = "somethingelse"
field3 = "something3 ,something4"

Answer 1

Why don't you use the Split function which lets you specify a delimiter:

Dim aValues As Variant
aValues = Split("|something|somethingelse|something3 ,something4", "|")

ADDITION

Here is some sample code in VBA.

Public Sub AppendValues()
    Const SOURCE_VALUES = "|something|somethingelse|something3 ,moretextinsamefield|"
    Dim aValues As Variant

    aValues = Split(SOURCE_VALUES, "|")

    Dim oDB As DAO.Database
    Dim oRS As DAO.Recordset

    Set oDB = DBEngine.Workspaces(0).Databases(0)
    Set oRS = oDB.OpenRecordset("Table1", dbOpenTable)

    oRS.AddNew
    oRS("Col1") = aValues(1)
    oRS("Col2") = aValues(2)
    oRS("Col3") = aValues(3)
    oRS.Update

    Set oRS = Nothing
    Set oDB = Nothing
End Sub

Answer 2

Would it be easier to do a pre-insert operation and clean up the data? Replace the |'s with ,'s, and enclose all values in quotes (or something like that)?

If not, I think if you format the data in a text file, you can use Access to import text and specify that | is the delimiter, rather than ,.

Answer 3

Check out this article: Pipe Delimited File into access database

Try this code.

The approach is as given below.

1. Import the data from the text file to a temporary table ('Import')
2. Update existing records in the table ('Dest') by joining the imported table and the existing table
3. Select the records from the 'Import' table which are not present in the 'Dest' table
4. Insert these new records to the 'Dest'

You need to modify the queries as per your table structure.

Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\VBNET\Access\bin\dest.mdb")   
        'Import data to a temporary table   
        Dim cmd As New OleDbCommand("SELECT * INTO [import] FROM [Text;FMT=Delimited;Database=G:\VBNET\Access\bin;Hdr=Yes].[Students.txt]", conn)   
        conn.Open()   
        cmd.ExecuteNonQuery()   
        'Update Existing records 
        Dim updateQuery As String = "UPDATE Dest INNER JOIN Import ON Dest.id = Import.F1 set Dest.Name = Import.F2"  
        Dim queryCmd As New OleDbCommand(updateQuery, conn)   
        queryCmd.ExecuteNonQuery()   

        Dim selectQuery = "select F1, F2, F3 from Import where F1 not in (select Id from Dest) "  
        queryCmd = New OleDbCommand(selectQuery, conn)   
        Dim dataReader As OleDbDataReader   
        dataReader = queryCmd.ExecuteReader()   

        Dim appendrecords As New ArrayList()   
        Dim insertQuery As String   

        While dataReader.Read()   

            Dim F1 As String = dataReader.GetString(0).ToString()   
            Dim F2 As String = dataReader.GetString(1).ToString()   
            Dim F3 As Integer = dataReader.GetInt32(2).ToString()   
            insertQuery = "insert into Dest values ('" & F1 & "', '" & F2 & "', " & F3 & ")"   
            appendrecords.Add(insertQuery)   

        End While   
        dataReader.Close()   

        Dim i As Integer   
        For i = 0 To appendrecords.Count - 1   
            Dim insertCmd As OleDbCommand = New OleDbCommand(appendrecords(i), conn)   
            insertCmd.ExecuteNonQuery()   
        Next 
        conn.Close()  

Answer 4

If you prefer to do this with an INSERT statement, it's simple. Call the subroutine like this:

Call InsertValues("|something|somethingelse|something3 ,moretextinsamefield|")

Uncomment the Execute line to actually do the INSERT instead of just displaying the statement.

Public Sub InsertValues(ByVal pstrInput As String)
Dim i As Integer
Dim strSql As String
Dim strValList As String
Dim varValues As Variant

varValues = Split(pstrInput, "|")

'first and last array members are empty strings; skip them '
For i = 1 To 3
    strValList = strValList & ", " & Chr(34) & varValues(i) & Chr(34)
Next i
'strip off leading comma and space '
strValList = Mid(strValList, 3)
strSql = "INSERT INTO YourTable (field1, field2, field3)" & _
    vbNewLine & "Values (" & strValList & ");"
Debug.Print strSql
'CurrentDb.Execute strSql, dbFailOnError '
End Sub

Answer 5

Writing SQL to do this will get very complicated very quickly, with a bunch of nested Mid() and InStr() functions.

Instead, I'd do it with a function that uses Split().

  Public Function SplitField(varInput As Variant, strDelimiter As String, lngItemRequested As Long) As Variant
    Dim varTemp As Variant
    Dim arrInput() As String

    varTemp = varInput
    If Left(varTemp, 1) = strDelimiter Then
       varTemp = Mid(varTemp, 2)
    End If
    If right(varTemp, 1) = strDelimiter Then
       varTemp = Left(varTemp, Len(varTemp) - 1)
    End If
    arrInput = Split(varTemp, strDelimiter)
    If lngItemRequested - 1 <= UBound(arrInput()) Then
       SplitField = arrInput(lngItemRequested - 1)
       If SplitField = vbNullString Then
          SplitField = Null
       End If
    Else
       SplitField = Null
    End If
  End Function

Then in SQL, you'd call it thus:

  INSERT INTO TargetTable( Field1, Field2, Field3, Field4 )
  SELECT SourceTable.SourceField, SplitField([SourceField],"|",1),
    SplitField([SourceField],"|",2), 
    SplitField([SourceField],"|",3), 
    SplitField([SourceField],"|",4)
  FROM SourceTable

Note that the function I wrote can be used even when there is a variable number of subparts in the source field. That is, if some have 4 parts and some 2, it doesn't matter, as the function returns Null for the parts that aren't there.