Importing .csv to existing table in Access Database

[Mazzy7]

I am creating a database to help my husband at work. I would like to import .csv bank statements into an existing table to enable bank reconciliations through Access.

I have no idea where to start, but would like this to be automatic, such as I can press a button and it just does it (wishful thinking?).

I hope someone out there can help.

Amanda

 

[bubba100]

To start I would suggest that you NOT move the data directly in to existing table. First import to a temp table. Deal with any errors there and then move it to your existing table.
As a start you can go to File>GetExternalData>Import. Use the file open screen to move to the folder, be sure to set the box "Files of Type" to see csv (text) format files. Then follow in wizard.

 

[OzFoxy]

Hi Mazzy, the code below is PART of the code behind a command button on one of my forms. Whilst it reads the CSV into the controls on a form, the same logic would read the CSV into a recordset.

In this case I know exactly how many lines/rows to read (intPlayersAdded). You would need to incorporate end of file detection.

Hope it is of some help.

----------------
On Error GoTo NOFILEERR
' input from saved file if there is one
Open "C:\Golf1\SS.CSV" For Input As #2
Input #2, dateFixture, strTemp4(2), strTemp4(3), strTemp4(4), strTemp4(5), intPlayersAdded
Me.cboFixtureDate = dateFixture

Me.fldSSCourse = strTemp4(2)
Me.fldSSCompetition = strTemp4(3)
Me.fldSSTrophy = strTemp4(4)
Me.fldSSSponsor = strTemp4(5)
Me!fldPlayers = intPlayersAdded
MSFlexGrid1.Rows = intPlayersAdded + 1
For intCounter = 1 To intPlayersAdded
MSFlexGrid1.Row = intCounter
For intCounter2 = 0 To 10
MSFlexGrid1.Col = intCounter2
Input #2, strData
MSFlexGrid1.Text = strData
Next intCounter2
Next intCounter
Close #2
------------------

 

[DrSimon]

I agree with the above comments, but the one thing may you need to consider is the order of the imported files, as the individual records are not usually given a date/time stamp. If you need to reconcile your database make sure you have an ID index which will order the records correctly.