Hi all...I guess I'm the newbie here...so I thought I may post something that I've been working on..that hopefully may help others... I needed a way to take data out of an older database, and will...by someone else of course...and then save it as a CSV.. I want to say that I am not taking any credit here...because I've been through a lot of sample code, to help me get here...and there was a lot of piecing together to get to this...even so I changed some minor parts...Not taking credit... I just want to share with you what worked for me.
My setup was fairly simple...I was using SQLite as my database, with the SQLite ODBC Connector...and a System DNS named "SFA"...to connect to my database, in ODBC Data Source Administrator. I have a higher "Lock Timeout" setting...at 10000, but you can play with that to see your results. Sync mode to Normal. When you are configuring the DSN.
Here is the DDL Script for the table in SQLite:
CREATE TABLE tbl_accounts (
account_id INTEGER PRIMARY KEY NOT NULL,
account_name TEXT,
phone TEXT);
You can insert whatever data you need. I use SQLite Spy for testing the database.
And here is the script that outputs a CSV file named "data.csv" on the C drive...change your drive letter accordingly.
And double check your System DSN name !!! before executing.
Dim ad
Dim sql_query
Dim objFSO
Dim objOutFile
Dim intCount
Dim i
Dim RX
Set RX = new RegExp
RX.Pattern = "\r|\n|,|"""
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutFile = objFSO.OpenTextFile("C:\data.csv", 8, True)
Set ad = CreateObject("ADODB.Connection")
sql_query = "SELECT account_id, account_name, phone FROM tbl_accounts"
ad.ConnectionString="DSN=SFA"
ad.Open
Set rs = ad.execute(sql_query)
intCount = rs.fields.Count
do until RS.EOF
Separator = ""
for i = 0 to RS.Fields.Count - 1
Field = RS.Fields( i ).Value & ""
if RX.Test( Field ) then
Field = """" & Replace( Field, """", """""" ) & """"
end if
objOutFile.Write Separator & Field
Separator = ","
next
objOutFile.Write vbNewLine
RS.MoveNext
loop
Hope it works for you guys...it works for me. You can write to me if you have any questions ...I'll try to answer then as best I can.
I found many examples online of this script...but to no amazement... none of them worked...right out of the box...and the write up on some of them...were not so easy to follow along, not knowing VB for more then a week.
Again I take no credit here...so don't get mad if you think I stole code from you !...just trying to help others.