Insert Data from a .CSV file into an access table

[Duncs]

Although I've used access or a while, I've never had to do this, so
I'm a bit lost.

I have a master table, into which data must be inserted on a weekly
basis. I receive a .CSV from an external source and the data must be
parsed and place din the table.

So far, I've got the following for opening & reading the file:

Function read_data()

Dim fs, f
Dim strFileName As String
Dim strFileText As String
Dim dteFileDate As Date

Set fs = CreateObject("Scripting.FileSystemObject")
strFileName = ""
Do While Not fs.FileExists(strFileName)
strFileName = InputBox("Please enter file path / name",
"FileName")
Loop

Set f = fs.opentextfile(strFileName, 1, 0)
strFileText = f.readline
While InStr(1, strFileText, "D", vbTextCompare) <> 2
strFileText = f.readline
Wend


End Function

But, what I don't know is how to split the data and insert it into the
table. All the data is separated by ',' and all fields are qualified
with the "" characters.

Your assistance is very much appreciated

TIA

Duncs

 

[Douglas J. Steele]

Have you tried simply importing the file, using the TransferText method?

To parse a comma-separated string into its component parts, you can use the
Split function:

Dim varValues As Variant

varValues = Split(strFileText, ",")

Now, varValues(0) will be the first field, varValues(1) will be the second
field and so on.

Let's assume that you also have the names of the fields in an array
varFields. You can insert the data using

Dim rsCurr As DAO.Recordset
Dim lngLoop As Long

Set rsCurr = CurrentDb.OpenRecordset("NameOfTable")
With rsCurr
.AddNew
For lngLoop = LBound(varValues) To UBound(varValues)
.Fields(varFields(lngLoop)) = varValues(lngLoop)
Next lngLoop
.Update
End With

or you can generate an INSERT INTO SQL statement and execute it.

 

[Duncs]

Cheers Doug. Works a treat.

Duncs

Have you tried simply importing the file, using the TransferText method?

To parse a comma-separated string into its component parts, you can use the
Split function:

Dim varValues As Variant

  varValues = Split(strFileText, ",")

Now, varValues(0) will be the first field, varValues(1) will be the second
field and so on.

Let's assume that you also have the names of the fields in an array
varFields. You can insert the data using

Dim rsCurr As DAO.Recordset
Dim lngLoop As Long

  Set rsCurr = CurrentDb.OpenRecordset("NameOfTable")
  With rsCurr
    .AddNew
    For lngLoop = LBound(varValues) To UBound(varValues)
      .Fields(varFields(lngLoop)) = varValues(lngLoop)
    Next lngLoop
    .Update
  End With

or you can generate an INSERT INTO SQL statement and execute it.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)















- Show quoted text -