| {\rtf1\ansi\ansicpg1252\deff0\deflang1044{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}} \viewkind4\uc1\pard\f0\fs17 This example imports a text file into a database table using ADO. Because it uses ADO, it should work with all data sources, though it has only been tested with Access. Usage is explained in the code's comments \par \par Public Function ImportTextFile(cn As Object, _ \par ByVal tblName As String, FileFullPath As String, _ \par Optional FieldDelimiter As String = ",", _ \par Optional RecordDelimiter As String = vbCrLf) As Boolean \par \par 'PURPOSE: Imports a delimited text file into a database \par \par 'PARAMTERS: cn -- an open ado connection \par ' : tblName -- import destination table name \par ' : FileFullPath -- Full Path of File to import form \par ' : FieldDelimiter -- (Optional) String character(s) in \par ' file separating field values \par ' within a record; defaults \par ' to "," \par ' : RecordDelimiter -- (Optional) String character(s) \par ' separating records within text \par ' file; defaults to vbcrlf \par \par 'RETURNS: True if successful, false otherwise \par 'EXAMPLE: \par 'dim cn as new adodb.connection \par 'cn.connectionstring = _ \par ' "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\db1.mdb" \par 'cn.open \par 'ImportTextFile cn, "MyTable", "C:\\myCSVFile.csv" \par \par 'REQUIRES: VB6 \par \par Dim cmd As New ADODB.Command \par Dim rs As New ADODB.Recordset \par Dim sFileContents As String \par Dim iFileNum As Integer \par Dim sTableSplit() As String \par Dim sRecordSplit() As String \par Dim lCtr As Integer \par Dim iCtr As Integer \par Dim iFieldCtr As Integer \par Dim lRecordCount As Long \par Dim iFieldsToImport As Integer \par \par \par 'These variables prevent \par 'having to requery a recordset \par 'for each record \par Dim asFieldNames() As String \par Dim abFieldIsString() As Boolean \par Dim iFieldCount As Integer \par Dim sSQL As String \par Dim bQuote As Boolean \par \par \par On Error GoTo errHandler \par If Not TypeOf cn Is ADODB.Connection Then Exit Function \par If Dir(FileFullPath) = "" Then Exit Function \par \par If cn.State = 0 Then cn.Open \par Set cmd.ActiveConnection = cn \par cmd.CommandText = tblName \par cmd.CommandType = adCmdTable \par Set rs = cmd.Execute \par iFieldCount = rs.Fields.Count \par rs.Close \par \par \par \par ReDim asFieldNames(iFieldCount - 1) As String \par ReDim abFieldIsString(iFieldCount - 1) As Boolean \par \par For iCtr = 0 To iFieldCount - 1 \par asFieldNames(iCtr) = "[" & rs.Fields(iCtr).Name & "]" \par abFieldIsString(iCtr) = FieldIsString(rs.Fields(iCtr)) \par Next \par \par \par iFileNum = FreeFile \par Open FileFullPath For Input As #iFileNum \par sFileContents = Input(LOF(iFileNum), #iFileNum) \par Close #iFileNum \par 'split file contents into rows \par sTableSplit = Split(sFileContents, RecordDelimiter) \par lRecordCount = UBound(sTableSplit) \par 'make it "all or nothing: whole text \par 'file or none of it \par cn.BeginTrans \par \par For lCtr = 0 To lRecordCount - 1 \par 'split record into field values \par \par sRecordSplit = Split(sTableSplit(lCtr), FieldDelimiter) \par iFieldsToImport = IIf(UBound(sRecordSplit) + 1 < _ \par iFieldCount, UBound(sRecordSplit) + 1, iFieldCount) \par \par 'construct sql \par sSQL = "INSERT INTO " & tblName & " (" \par \par For iCtr = 0 To iFieldsToImport - 1 \par bQuote = abFieldIsString(iCtr) \par sSQL = sSQL & asFieldNames(iCtr) \par If iCtr < iFieldsToImport - 1 Then sSQL = sSQL & "," \par Next iCtr \par \par sSQL = sSQL & ") VALUES (" \par \par For iCtr = 0 To iFieldsToImport - 1 \par If abFieldIsString(iCtr) Then \par sSQL = sSQL & prepStringForSQL(sRecordSplit(iCtr)) \par Else \par sSQL = sSQL & sRecordSplit(iCtr) \par End If \par \par If iCtr < iFieldsToImport - 1 Then sSQL = sSQL & "," \par Next iCtr \par \par sSQL = sSQL & ")" \par cn.Execute sSQL \par \par Next lCtr \par \par cn.CommitTrans \par rs.Close \par Close #iFileNum \par Set rs = Nothing \par Set cmd = Nothing \par \par ImportTextFile = True \par Exit Function \par \par errHandler: \par 'on error resume next \par If cn.State <> 0 Then cn.RollbackTrans \par If iFileNum > 0 Then Close #iFileNum \par If rs.State <> 0 Then rs.Close \par Set rs = Nothing \par Set cmd = Nothing \par \par \par End Function \par \par Private Function FieldIsString(FieldObject As ADODB.Field) _ \par As Boolean \par \par \par Select Case FieldObject.Type \par Case adBSTR, adChar, adVarChar, adWChar, adVarWChar, _ \par adLongVarChar, adLongVarWChar \par FieldIsString = True \par Case Else \par FieldIsString = False \par End Select \par \par End Function \par \par Private Function prepStringForSQL(ByVal sValue As String) _ \par As String \par \par Dim sAns As String \par sAns = Replace(sValue, Chr(39), "''") \par sAns = "'" & sAns & "'" \par prepStringForSQL = sAns \par \par End Function \par } |
Import a Delimited Text File into a Database |
India web developer web development India | India web development company India ecommerce web developer