insert values with different delimiter
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"
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
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.
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 ,
.
Check out this article: Pipe Delimited File into access database
Try this code.
The approach is as given below.
- Import the data from the text file to a temporary table ('Import')
- Update existing records in the table ('Dest') by joining the imported table and the existing table
- Select the records from the 'Import' table which are not present in the 'Dest' table
- 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()
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
精彩评论