How to read .csv file with VBScript and ADO

Question

The Goal

Write a program that reads .csv files and uploads them to a database. It must run on Windows 10 with no special installations necessary.

What I've Tried

I'm familiar with VBScript and ADO connections (I've actually already written and tested the file upload portion of the program). Now all I'm trying to do is read in the .csv file with ADO (see why to use ADO instead of split() here: https://msdn.microsoft.com/en-us/library/ms974559.aspx)

The article above creates the .csv connection using the Microsoft Jet Database Engine, which is not installed by default, so I tried with Microsoft Text Driver, as recommended by other sources.

The Problem

I'm encountering an error upon attempting a connection. The error says:

Microsoft OLE DB Provider for ODBC
Drivers: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

That error occurs whether run from 32-bit or 64-bit cmd (this is where I got the 32-bit process: How do I run a VBScript in 32-bit mode on a 64-bit machine?). The Microsoft Text Driver can be found in the 32-bit ODBC Drivers.

I do have a file called test.csv on my desktop and it contains the text "Cell1","Cell2","Cell3"

The Code

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

set WshShell = CreateObject("Wscript.shell")
strDesktop = WshShell.SpecialFolders("Desktop")

Set fileConn = CreateObject("ADODB.Connection")
Set fileRS = CreateObject("ADODB.Recordset")

strPathtoTextFile = strDesktop & "\"
' Wscript.Echo(strPathtoTextFile)
fileConn.ConnectionString = "Driver={Microsoft Text Driver (*.txt;*.csv)};DBQ=" & strPathtoTextFile & ";"

fileConn.Open

fileRS.Open "SELECT * FROM test.csv", _
        fileConn, adOpenStatic, adLockOptimistic, adCmdText

The error occurs on the line fileConn.Open. The code is a modified snippet from the Microsoft article above.

Any ideas why I'm getting this error?

I think, you need FileSystemObject. And I think it is answered http://stackoverflow.com/q/7093550/2504101 — olyv, Nov 09 '16 at 10:45
The fact that I've got many nested commas in the strings I'll eventually be parsing prevents me from using the FSO, hence my attempt to use ADO (there's quite a good description of the benefits in the Microsoft article above). I would rather treat the .csv as a table and let ADO handle it all for me — Corbfon, Nov 09 '16 at 12:37
Ok, can you try to modify your connection string according to the example from mentioned article to make it ```Provider=Microsoft.Jet.OLEDB.4.0;```? — olyv, Nov 10 '16 at 14:31
According to the answer in this question http://stackoverflow.com/questions/13811179/where-how-can-i-download-and-install-the-microsoft-jet-oledb-4-0-for-windows-8, the Jet db provider isn't installed by default anymore, which is one of my requirements. Do you know differently? — Corbfon, Nov 14 '16 at 15:31

How to read .csv file with VBScript and ADO

0 Answers0