0

Possible Duplicate:
Opening csv-File via ADO-connection - Column Limitation to 255 / or just use another file type?

I have the following code to open a csv file as a record set

Set cN = New ADODB.Connection
Set RS = New ADODB.Recordset
cN.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=C:\data;" & _
               "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""")
RS.ActiveConnection = cN
RS.Source = "select * from myFile.csv"

The above code works for files which have less than 255 columns. However, if my CSV file has more than 255 columns, I get only the first 255 columns of data. Is there any alternate way of opening a CSV file into a ADODB recordset?

Community
  • 1
  • 1

1 Answers1

0

The problem is with access databases. You can't have more than 255 fields per table:

http://office.microsoft.com/en-us/access-help/access-2007-specifications-HA010030739.aspx http://office.microsoft.com/en-us/access-help/access-2010-specifications-HA010341462.aspx

Depending on your data, could you possibly transpose it, or split it into multiple tables?

mkingston
  • 2,678
  • 16
  • 26
  • unfortunately, no. The code snippet is actually inside a function that has a return type of ADODB.RecordSet. That is why, I am looking for an answer that can allow me to return some kind of data structure that will contain all columns and rows –  Oct 09 '12 at 13:43
  • 1
    Does the data have to be on disk immediately? Could you return an array or collection instead, then write to disk later? – mkingston Oct 09 '12 at 20:57
  • I guess not, given your response to Sean. There is no "fix". You will have to find a workaround similar to that proposed in my comment above, or in the link from Sean. – mkingston Oct 09 '12 at 21:01
  • other databases (MySQL, PostGreSQL) have different column limits on their tables. You *may* be able to load your data into another type of database, then return a recordset for that database. I'm not actually sure whether ADO itself has a limit of 255 columns, you'd have to check that. You could also make your own object representing your data, with the required methods/properties from RecordSet. – mkingston Oct 09 '12 at 21:25
  • I am going to go with returning an array instead. Is it possible to return the entire csv file or do I have to return it row by row? Also, if you could give me your solution as an answer instead of a comment, I will go ahead and accept it. –  Oct 10 '12 at 01:54
  • I'm sorry, but I really don't have time to do this right now. I've just been given a lot of work. All the information you should need is in the link Sean gave you. – mkingston Oct 11 '12 at 03:05