I have figured out a way to export data in csv format from an online database that we use for our member management system by using the API export call.
The problem arises when I import the data in Excel. Some of the fields delimit incorrectly when users have entered commas in the fields resulting in incorrect data in incorrect fields.
Another company that have done work for us on another project informed me that they do not save the file but export it directly to a database.
My question is as follows. I need code to write the csv file to a sql server express database.
My questions are inside the code as below
'This is the part where the API csv file is downloaded- this part works already
Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False
myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
oStream.Type = 1
'At this point I normally save the data as a csv file- this is where I want to bulk insert into a sql server
'Not sure if I can now open connection to sql server?
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "Provider=SQLOLEDB;Data Source=ALWYNS\SQLEXPRESS;Initial Catalog=Your membership;Integrated Security=SSPI;"
'Not sure if I can insert FROM WinHttpReq.responseBody? Will the code below automatically create the columns according to the names in the first row of the csv file? How will the column types be determined?
strSQLQuery = "BULK INSERT dbo.Customers " & _
"FROM WinHttpReq.responseBody " & _
"WITH (FIRSTROW = 1, FIELDTERMINATOR = ',', " & _
"ROWTERMINATOR = '\n', TABLOCK)"
.ActiveConnection = locConnection
.CommandType = adCmdText
.CommandText = strSQLQuery
Set conn = Nothing