''''''''''''''''''''''''
'arnelgp
'
'note:
'
'structure of table/textfile must be the same
'
Public Function TextFileToTable(ByVal pstrTextFile As String, ByVal pstrTable As String, Optional ByVal delim As String = ",")
Dim arrColumn() As String
Dim db As DAO.Database
Dim rs As DAO.recordSet
Dim intFile As Integer
Dim strData As String
Dim i As Integer
Dim var As Variant
Dim varValue As Variant
'* check if the textfile exists
If Len(Dir(pstrTextFile)) = 0 Then
Exit Function
End If
'* open the table
Set db = CurrentDb
If InStr(pstrTable, "SELECT ") > 0 Then
Set rs = db.OpenRecordset( _
"SELECT * FROM (" & pstrTable & ") " & _
"WHERE (1=0);", dbOpenDynaset)
Else
Set rs = db.OpenRecordset( _
"SELECT * FROM [" & pstrTable & "] " & _
"WHERE (1=0);", dbOpenDynaset)
End If
Set db = Nothing
intFile = FreeFile
'* open the text file
Open pstrTextFile For Input As #intFile
'* assume that the first line on text file is the header
Input #intFile, strData
var = Split(strData, delim)
'* save the header to dictionary
ReDim arrColumn(0 To UBound(var))
For i = 0 To UBound(var)
arrColumn(i) = var(i)
Next
'* step through each line in textfile
Do Until EOF(intFile)
Input #intFile, strData
var = Split(strData, delim)
rs.AddNew
For i = 0 To UBound(var)
Select Case rs.fields(arrColumn(i)).Type
Case dbByte
varValue = CByte(var(i))
Case dbInteger
varValue = CInt(var(i))
Case dbSingle
varValue = CSng(var(i))
Case dbDouble
varValue = CDbl(var(i))
Case dbLong
varValue = CLng(var(i))
Case dbDate
varValue = CDate(var(i))
Case dbCurrency, dbDecimal
varValue = CCur(var(i))
Case Else
varValue = var(i)
End Select
rs.fields(arrColumn(i)) = varValue
Next
rs.Update
Loop
Close #intFile
rs.Close
Set rs = Nothing
Erase var
Erase arrColumn
End Function