Import of specific Columns from multiple CSV files into Access 2013 using vba/macros

Question

I have multiple csv files (semicolon separated) with same number of columns(around 616) and different number of rows. I want to import every file into different tables in Access. I am totally new to macro/vba. I have faced two problems:

I used this code [https://superuser.com/questions/1131994/ms-access-2016-import-multiple-csv-files][1] from a forum, it works fine but it's importing one csv file into one table in one column with semicolon separated (means 616 columns from csv file are showing in one coulmn in access with semicolon separated). although the data should be shown in separate columns of a table. I want the data to be shown in separate columns. After import, I am seeing the data in access same as csv file. The column heading in csv file has text and numbers (1 to 600) also.
Secondly, I want to import some specific columns from csv file. Could someone please edit this code and add some coding how to import specific columns from csv file into Access.

I hope I explained well.

You will have to normalise your data layout - you can only have a maximum of 255 fields in access. That many columns indicates a poor storage method. Have a read here techrepublic.com/article/… for some guidance on importing data into Access — Minty
– Minty, Commented Mar 2, 2017 at 12:56
thank you for sending me this article. I am new to Access. I really didn't knew that it has a max. limit of 255 fields. Now i understood better. — Abdul
– Abdul, Commented Mar 6, 2017 at 11:19

Parfait · Accepted Answer · 2017-03-08 05:08:24Z

0

There is no way you can import files with 600+ columns into Access. You can try SQL Server. Alternatively, you can import the specific fields that you need. I seriously doubt you need 600+ fields in your data sets.

Anyway, you can export specific fields from Excel to Access.

Sub DAOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
    Set db = OpenDatabase("C:\FolderName\DataBaseName.mdb") 
    ' open the database
    Set rs = db.OpenRecordset("TableName", dbOpenTable) 
    ' get all records in a table
    r = 3 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0 
    ' repeat until first empty cell in column A
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("FieldName1") = Range("A" & r).Value
            .Fields("FieldName2") = Range("B" & r).Value
            .Fields("FieldNameN") = Range("C" & r).Value
            ' add more fields if necessary...
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

edited Mar 8, 2017 at 5:08

Parfait

108k19 gold badges102 silver badges138 bronze badges

answered Mar 8, 2017 at 5:01

ASH

20.5k28 gold badges115 silver badges247 bronze badges

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

1 Comment

Abdul Over a year ago

Sorry 4 late reply. Actually it's difficult to explain. The automatic graph showing on Siemens HMI is saving all data in csv file and it has more than 600 columns. The big sensor moves continuously (reads the coating thickness on the aluminium foil) from left to right and right to left in 6 to 7 seconds. So once it moves from left to right it saves those 600 columns and the other way round. so for sure the automatic graph drawn on HMI is using those 600 values (thickness values on the foil from left to right and other way..). Just giving you idea. I am new to this also unfortunately

Collectives™ on Stack Overflow

Import of specific Columns from multiple CSV files into Access 2013 using vba/macros

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related