How to Read a CSV File, and insert data into an Access Database, Using VB

Question

Am new to VB programming,I have a requirement where, there will be table which is already created in Access and I need to write code which will read the file and enter the data in the record. Please help me out how to achieve this. I use Access as my DB

The input file is .CSV file and its in format of :

FIRST_NAME, LAST_NAME, HOME_ADD, COMPANY, DESIG
Mark , Taylor , UK , XYZ , ABC
Mark , Taylor , UK , XYZ , ABC
Mark , Taylor , UK , XYZ , ABC
Mark , Taylor , UK , XYZ , ABC
Mark , Taylor , UK , XYZ , ABC
Mark , Taylor , UK , XYZ , ABC
Mark , Taylor , UK , XYZ , ABC

This values sholud be read from "C:\input\data.csv" folder read and write in Access table which is already created.

Thanks in Advance

yes schopy but what i did was it reads a file and creates a new table displays the data there — desiretolearn
– desiretolearn, Commented Apr 8, 2014 at 13:31
I don't understand what you mean. But if you have already written code that didn't work, why not post that? — Mark Bertenshaw
– Mark Bertenshaw, Commented Apr 8, 2014 at 17:42
hi @MarkBertenshaw what i did was creating a new table and mapping the data to that table, but what required is to map to already created table — desiretolearn
– desiretolearn, Commented Apr 9, 2014 at 6:54

Bond · Accepted Answer · 2014-04-10 01:25:55Z

1

Access already supports importing records from CSV files, so this can be done quite easily by launching and controlling an instance of Access.

With CreateObject("Access.Application")
    .OpenCurrentDatabase "C:\Database1.accdb"
    .DoCmd.TransferText , , "Table1", "c:\input\data.csv", True
    .Quit
End With

The True parameter of TransferText specifies that your CSV file contains a header row.

answered Apr 10, 2014 at 1:25

Bond

16.3k6 gold badges34 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark Bertenshaw · Accepted Answer · 2014-04-09 08:17:34Z

I am not sure whether you will know the fields in advance. I will assume that you don't know until the file is opened. Also, I am assuming you are using ADO.

Private Sub InsertCsvDataIntoTable(ByRef in_sCsvFileName As String, _
                                   ByRef in_oConn As ADODB.Connection, _
                                   ByRef in_sTableName As String)

    Dim iFileNo             As Integer
    Dim sLine               As String
    Dim vasFields           As Variant
    Dim lIndex              As Long
    Dim sSQL_InsertPrefix   As String
    Dim sSQL                As String

    iFileNo = FreeFile

    Open in_sCsvFileName For Input As #iFileNo

    If EOF(iFileNo) Then
        Exit Sub
    End If

    ' Read field names from the top line.
    Line Input #iFileNo, sLine

    ' Note that in a "proper" CSV file, there should not be any trailing spaces, and all strings should be surrounded by double quotes.
    ' However, the top row provided can simply be used "as is" in the SQL string.

    sSQL_InsertPrefix = "INSERT INTO " & in_sTableName & "(" & sLine & ") VALUES("

    Do Until EOF(iFileNo)

        Line Input #iFileNo, sLine

        ' Initialise SQL string.
        sSQL = sSQL_InsertPrefix

        ' Again, in a proper CSV file, the spaces around the commas shouldn't be there, and the fields should be double quoted.
        ' Since the data is supposed to look like this, then the assumption is that the delimiter is space-comma-space.
        vasFields = Split(sLine, " , ")

        ' Build up each value, separated by comma.
        ' It is assumed that all values here are string, so they will be double quoted.
        ' However, if there are non-string values, you will have to ensure they don't get quoted.
        For lIndex = 0 To UBound(vasFields) - 1
            sSQL = sSQL & "'"
            sSQL = sSQL & vasFields(lIndex)
            sSQL = sSQL & "'"
            sSQL = sSQL & ","
        Next lIndex

        ' This chunk of code is for the last item, which does not have a following comma.
        sSQL = sSQL & "'"
        sSQL = sSQL & vasFields(lIndex)
        sSQL = sSQL & "'"

        sSQL = sSQL & ")"

        ' Run the SQL command.
        in_oConn.Execute sSQL

    Loop

    Close #iFileNo

End Sub

But is it the correct answer? Your CSV file looked a bit weird (see my client comments) - is this sample data created by yourself, or is it the real data?

Collectives™ on Stack Overflow

How to Read a CSV File, and insert data into an Access Database, Using VB

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related