Using ADO to import a text file into an existing table structure

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a CSV file which I want to import to a pre-exixting table structure.

I believed that this could be accomplished using the code below, but when I
view the data afterwards, the first record from the CSV file has been
ignored. I am probably doing something really dumb, but any help would be
appreciated.

Here's the code:

Public Function ImportFile(strFilename, strTableName, intNumRecs)
Dim strMsg, strFileOnly, strPathOnly As String
Dim slashPos, I, intOutCount, numRead As Integer
Dim outConn, inpConn As Connection, inpRs, outRs As Recordset
Dim connStr As String

slashPos = InStrRev(strFilename, "\", , vbTextCompare)
strFileOnly = Right(strFilename, Len(strFilename) - slashPos)
strPathOnly = Left(strFilename, slashPos)
MsgBox "[" + strFileOnly + "][" + strPathOnly + "]"

Set inpConn = CreateObject("ADODB.Connection")
Set outConn = CurrentDb()
Set inpRs = CreateObject("ADODB.Recordset")
Set outRs = CreateObject("ADODB.Recordset")

inpConn.Open _
"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=" + strPathOnly + ";" & _
"Extensions=asc,csv,tab,txt"

inpRs.Open "SELECT * FROM " + strFileOnly, _
inpConn, adOpenStatic, adLockReadOnly, adCmdUnspecified

outRs.Open strTableName, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

intOutCount = outRs.RecordCount
If intOutCount > 0 Then
With outRs 'empty current contents of the table
outRs.MoveLast
outRs.MoveFirst
Do Until .EOF
.Delete
.Update
.MoveNext
Loop
End With
End If

With inpRs
numRead = 0
Do Until .EOF
outRs.AddNew
numRead = numRead + 1
For I = 0 To .Fields.Count - 1
outRs.Fields(I) = .Fields(I)
Next I
outRs.Update
.MoveNext
Loop
intNumRecs = outRs.RecordCount
End With
Set outRs = Nothing
Set inpRs = Nothing

End Function
 
I think this is because it assume that first row contain headers, I do not
remember exact syntax for connection, but if you look at documentation -
believe you find some setting for this
 
Thanks Alex. I kinda figured that was the case, but I can't find the
documentation that shows the various parameter settings.

Alex Dybenko said:
I think this is because it assume that first row contain headers, I do not
remember exact syntax for connection, but if you look at documentation -
believe you find some setting for this

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


Braveheart said:
I have a CSV file which I want to import to a pre-exixting table structure.

I believed that this could be accomplished using the code below, but when
I
view the data afterwards, the first record from the CSV file has been
ignored. I am probably doing something really dumb, but any help would be
appreciated.

Here's the code:

Public Function ImportFile(strFilename, strTableName, intNumRecs)
Dim strMsg, strFileOnly, strPathOnly As String
Dim slashPos, I, intOutCount, numRead As Integer
Dim outConn, inpConn As Connection, inpRs, outRs As Recordset
Dim connStr As String

slashPos = InStrRev(strFilename, "\", , vbTextCompare)
strFileOnly = Right(strFilename, Len(strFilename) - slashPos)
strPathOnly = Left(strFilename, slashPos)
MsgBox "[" + strFileOnly + "][" + strPathOnly + "]"

Set inpConn = CreateObject("ADODB.Connection")
Set outConn = CurrentDb()
Set inpRs = CreateObject("ADODB.Recordset")
Set outRs = CreateObject("ADODB.Recordset")

inpConn.Open _
"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=" + strPathOnly + ";" & _
"Extensions=asc,csv,tab,txt"

inpRs.Open "SELECT * FROM " + strFileOnly, _
inpConn, adOpenStatic, adLockReadOnly, adCmdUnspecified

outRs.Open strTableName, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

intOutCount = outRs.RecordCount
If intOutCount > 0 Then
With outRs 'empty current contents of the table
outRs.MoveLast
outRs.MoveFirst
Do Until .EOF
.Delete
.Update
.MoveNext
Loop
End With
End If

With inpRs
numRead = 0
Do Until .EOF
outRs.AddNew
numRead = numRead + 1
For I = 0 To .Fields.Count - 1
outRs.Fields(I) = .Fields(I)
Next I
outRs.Update
.MoveNext
Loop
intNumRecs = outRs.RecordCount
End With
Set outRs = Nothing
Set inpRs = Nothing

End Function
Click to expand...
Click to expand...
 
Hi,
cant find right now either. what you can do - just add one more row as a
first row

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


Braveheart said:
Thanks Alex. I kinda figured that was the case, but I can't find the
documentation that shows the various parameter settings.

Alex Dybenko said:
I think this is because it assume that first row contain headers, I do
not
remember exact syntax for connection, but if you look at documentation -
believe you find some setting for this

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


Braveheart said:
I have a CSV file which I want to import to a pre-exixting table
structure.

I believed that this could be accomplished using the code below, but
when
I
view the data afterwards, the first record from the CSV file has been
ignored. I am probably doing something really dumb, but any help would
be
appreciated.

Here's the code:

Public Function ImportFile(strFilename, strTableName, intNumRecs)
Dim strMsg, strFileOnly, strPathOnly As String
Dim slashPos, I, intOutCount, numRead As Integer
Dim outConn, inpConn As Connection, inpRs, outRs As Recordset
Dim connStr As String

slashPos = InStrRev(strFilename, "\", , vbTextCompare)
strFileOnly = Right(strFilename, Len(strFilename) - slashPos)
strPathOnly = Left(strFilename, slashPos)
MsgBox "[" + strFileOnly + "][" + strPathOnly + "]"

Set inpConn = CreateObject("ADODB.Connection")
Set outConn = CurrentDb()
Set inpRs = CreateObject("ADODB.Recordset")
Set outRs = CreateObject("ADODB.Recordset")

inpConn.Open _
"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=" + strPathOnly + ";" & _
"Extensions=asc,csv,tab,txt"

inpRs.Open "SELECT * FROM " + strFileOnly, _
inpConn, adOpenStatic, adLockReadOnly, adCmdUnspecified

outRs.Open strTableName, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

intOutCount = outRs.RecordCount
If intOutCount > 0 Then
With outRs 'empty current contents of the table
outRs.MoveLast
outRs.MoveFirst
Do Until .EOF
.Delete
.Update
.MoveNext
Loop
End With
End If

With inpRs
numRead = 0
Do Until .EOF
outRs.AddNew
numRead = numRead + 1
For I = 0 To .Fields.Count - 1
outRs.Fields(I) = .Fields(I)
Next I
outRs.Update
.MoveNext
Loop
intNumRecs = outRs.RecordCount
End With
Set outRs = Nothing
Set inpRs = Nothing

End Function
Click to expand...
Click to expand...
Click to expand...
 
Back
Top