Reading a CSV file into an Excel table (listObject) with Visual Basic for Application

tab.csv

This is the CSV file we're going to import.

txt,num;cdat
fourty-two,42,2020-02-20 20:02:20
hello world,99.99,2020-02-03 04:05:06
"foo, bar and baz",123.456789,1999-09-19 09:19:29
René,98765.4321,1900-01-01 00:00:00
xyz,70.00,1970-03-05 01:02:03

Github repository about-MS-Office-object-model, path: /Excel/ListObject/connectionString/CSV/tab.csv

schema.ini

The schema.ini file defines some characteristics of the CSV file, such as column names, column data types and date/time format.

Because tab.csv is encoded in UTF-8, the schema.ini file contains the characterSet = 65001. This ensures that the fourth data line in the CSV file is imported correctly.

[tab.csv]
format         = csvDelimited
colNameHeader  = true
characterSet   = 65001
dateTimeFormat ="yyyy-mm-dd hh:nn:ss"
col1           = txt text
col2           = num decimal   precision 3 scale 2
col3           = dat datetime

Github repository about-MS-Office-object-model, path: /Excel/ListObject/connectionString/CSV/schema.ini

The VBA code

option explicit

sub main() ' {

    dim connectionString as string

    connectionString =                            _
       "OLEDB;"                                 & _
       "provider=Microsoft.ACE.OLEDB.12.0;"     & _
       "data source=" & thisWorkbook.path & ";" & _
       "extended Properties=text"

    dim destTable as listObject

    set destTable = activeSheet.listObjects.add( _
       sourceType  := xlSrcExternal            , _
       source      := connectionString         , _
       destination := cells(2, 2))

    with destTable.queryTable

        .commandType     = xlCmdSql
        .commandText     = array("select * from [tab.csv]")
        .backgroundQuery = true

        .refresh backgroundQuery := false

    end With

end sub ' }

Github repository about-MS-Office-object-model, path: /Excel/ListObject/connectionString/CSV/import-tab.bas

Created list object

The following image shows the created list object. As can be seen, the dates are imported with the correct numbers (days since 1899-12-31), but not formatted correctly.

It also shows that the é of René is correct (as per specification of characterSet in the schema.ini file).

Reading a CSV file into an Excel table (listObject) with Visual Basic for Application

tab.csv

schema.ini

The VBA code

Created list object

See also