Connecting to CSV Files

The SaveToDB add-in allows connecting to CSV files using two wizards:

This topic describes the first way that uses OLEDB providers and ODBC drivers.

The SaveToDB add-in supports the following data providers:

Microsoft Access Text Driver
Microsoft.Jet.OLEDB.4.0 (32-bit only)
Microsoft.ACE.OLEDB.12.0
Microsoft.ACE.OLEDB.16.0

Here is a sample of the Database Connection Wizard page where you can select the data provider:

Connecting Excel to CSV - Select Provider

In the next step, select a folder (or a file):

Connecting Excel to CSV - Select Folder

Note you can click the Properties button to edit the connection properties.

In the next step, select a CSV file to connect:

Connecting Excel to CSV - Select File

Microsoft CSV data providers require the schema.ini file in the same folder with the source file definition like:

[MsnMoneyQuotes.csv]
ColNameHeader=True
Format=Delimited(;)
MaxScanRows=100
CharacterSet=ANSI
Col1=LoadDate DateTime(yyyy-MM-dd)
Col2=LoadTime DateTime
Col3=MarketDate DateTime(yyyy-MM-dd)
Col4=MarketTime DateTime
Col5=Symbol Text
Col6=MsnSymbol Text
Col7=Bid Double
Col8=Ask Double
Col9=BidSize Integer
Col10=AskSize Integer
Col11=Open Double
Col12=High Double
Col13=Low Double
Col14=Last Double
Col15=Volume Integer

The SaveToDB add-in creates such definitions for you, analyzing file contents.

You can fix the created definitions manually. See details at https://learn.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver?view=sql-server-2017.

Use the following value for files in a utf-8 encoding:

CharacterSet=65001