I download, every week, a CSV file containing a very large amount of data. It consists of subscriber account information.
Here is a sample of how the data comes out:
="433030040404089273119",1400,"KIMBLE,SEAN","BRYAN",TX,77801,IE,="",2215
I access it using ADO, here's the connection string:
db.Open ("Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\;Extensions=asc,csv,tab,txt;Persist Security Info=False;")
strSQL = "SELECT * FROM qc.csv"
Call OpenConnectionMS
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Open strSQL, db
Set DataGrid1.DataSource = rs
Ignore the naming/programming conventions, as this is a sample.
The problem is that the CSV file comes with equal signs in front of some fields, mostly because most people opening the CSV file open it with Excel, and this stops Excel from trimming the number. However, it's wreaking havoc with ADO, as it will return a recordset with nothing but headers and equal signs. If I take out the equal sign, it returns a fully functional populated recordset.
I cannot modify the way the reports are generated. I do not want to have to preprocess the file to remove equal signs unless I REALLY have to, as this is a file with thousands and thousands of rows.
Any thoughts on getting the ADO API to properly process the fields with = in them?
Here is a sample of how the data comes out:
="433030040404089273119",1400,"KIMBLE,SEAN","BRYAN",TX,77801,IE,="",2215
I access it using ADO, here's the connection string:
db.Open ("Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\;Extensions=asc,csv,tab,txt;Persist Security Info=False;")
strSQL = "SELECT * FROM qc.csv"
Call OpenConnectionMS
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Open strSQL, db
Set DataGrid1.DataSource = rs
Ignore the naming/programming conventions, as this is a sample.
The problem is that the CSV file comes with equal signs in front of some fields, mostly because most people opening the CSV file open it with Excel, and this stops Excel from trimming the number. However, it's wreaking havoc with ADO, as it will return a recordset with nothing but headers and equal signs. If I take out the equal sign, it returns a fully functional populated recordset.
I cannot modify the way the reports are generated. I do not want to have to preprocess the file to remove equal signs unless I REALLY have to, as this is a file with thousands and thousands of rows.
Any thoughts on getting the ADO API to properly process the fields with = in them?
