SQL Insert: Add Records to Closed CSVs

A poster in a forum I frequent asked:

"About 5 to 10 people will be using Excel that contains a subroutine that writes to the same csv file over a network. What's the best way to handle multiple users writing to one file? The Open, Write and Close methods happen quickly, but there is a good chance two or more people will attempt to open the csv at the same time. Thoughts?"

With VBA, ADO, and SQL, this is easy. Before we talk about the solution, however, I want to remind readers that there are free databases out there and this sort of thing is best done in a database. So with that said, let's answer our poster.

The method for writing to CSV's or MS Access Tables or SQL Server Tables is the same.

Create a Connection Object
Create an SQL "Insert Statement"
Execute the SQL Insert Statement using the Connection Object.
When finished with all updates, close the Connection Object

So, for an example, let’s assume:

We have a CSV file called Temp.CSV in C:\TEMP
It has three columns
Columns have headings
We want to insert “V1” in column 1, “V2” in column 2 and "V3" in column 3
C:\Temp\Temp.csv is closed (no one has it open in Excel or Notepad)

Add the following code to a normal code module. Afterwards, call it from the immediate window

Sub Write2CSV()

Dim cn As Object

Dim sSQL As String

Set cn = CreateObject("ADODB.Connection")

sSQL = "Insert Into Temp.CSV Values('V1', 'V2', 'V3')"

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Temp;" & _

"Extended Properties='Text;" & _

"HDR=Yes;" & _

"FMT = CSVDelimited '"

cn.Execute sSQL

cn.Close

End Sub

After the code runs our CSV looks like this.

NOTE! Your PC may not have the provider used in this example.

This shows the basic steps to updating a CSV. To complete this project we:

Should add error handling
Would add code to pluck data from Excel's cells and replace the SQL Insert Statement's 'V#' with those values
Should add code to keep the connection open until all updates are finished and then close it.

References:

Connection Objects: http://msdn.microsoft.com/en-us/library/ms807027.aspx

Connection Strings: http://www.connectionstrings.com/textfile

SQL Insert Statement: http://www.w3schools.com/sql/sql_insert.asp

Page updated

Google Sites

Report abuse