How to read data from a file record by record process and update a table

  • Thread starter Thread starter Karen Middleton
  • Start date Start date
K

Karen Middleton

Hello All

I want some VBA code samples that can read a .csv delimited file
record by record do processing and update the data into a Access
table.

I would appreciate if any of you can kindly share the VBA code for
this purpose.

Thanks
Karen
 
Karen,

Here's some sample code to do what you want (I understand you want to
append records, right?):

Sub split_csv()
Dim db As DAO.Database
Dim rst As DAO.Recordset
InputFile = "C:\SomeFolder\SomeFile.csv"
TargetTable = "MyTable"
Delim = ","
Open InputFile For Input As #1
Set db = CurrentDb
Set rst = db.OpenRecordset(TargetTable)
Do Until EOF(1)
Line Input #1, InpString
FVals = Split(InpString, Delim)
rst.AddNew
For i = 0 To rst.Fields.Count - 1
rst.Fields(i) = FVals(i)
Next
rst.Update
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
Close #1
End Sub

Note: The code assumes the fields in the .csv file are the same number
and in the same order as the field tables. If not, the code will need
some modifications.
To run this code, it is required to have an appropriate DAO Object
Library reference. While in the VB editor window, go to menu item Tools
References; check if a Microsoft DAO reference is present among the
Click to expand...
ones checked at the top of the list. If not, scroll down to find the
appropriate Microsoft DAO X.X Object Library reference and check it. The
appropriate reference is DAO 3.51 for A97, DAO 3.6 for A2K or later.

HTH,
Nikos
 
Karen,

I would imagine you needd 2 lines of code, one being
DoCmd.TransferText ...
to import the data from the .csv file into a "holding" table in your
Access database. And then probably your processing of the data and its
import into the main table can be done via an Append Query. Maybe it
will require an Update Query as well as an Append Query... I think we
would need to know more details, with examples, of the type of data and
the type of processing you need, before anyone could advise more
specifically.
 
Back
Top