Re: ADO with Excel using .csv? (VB6-Pro)

Frustrated wrote on Tue, 13 Sep 2005 00:57:19 -0700:

> Paul Clement (VB MVP) on the microsoft.public.vb.database.ado forum found
> the error of my ways (thanks again, Paul, if you happen to see this in
> this forum).
>
> I had misunderstood how to do it as explained at
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;257819
> and had stipulated a connection string as:
>
> strcon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & csvFile & ";" & _
> "Extended Properties=""Excel 2002;IMEX=1;HDR=YES"""
>
> It should instead read:
>
> strcon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & csvFile & ";" & _
> "Extended Properties=""Excel 8.0;IMEX=1;HDR=YES"""
>
> The code then worked with a .xls type file just fine, I could iterate
> through all the rows and columns, but it still won't work with a .csv
> export of the same file, and gives a "Run-time error -2147467259
> (80004005) External table is not in the expected format." response. But
> the same .csv file will load into Excel just fine if I double-click the
> .csv file, no parsing errors are then apparent. I'm going to try to get
> around this by using automation to make Excel itself load the .csv file
> and then to save it as a normal .xls type file so that I can then use the
> above connection string to move the Excel "records" over into Access.
> Unless someone can tell me how to make the above connection string work
> with a .csv file, that is!
>

Is there any reason why you're using the Excel driver instead of the Text
driver? The Text driver is designed to read CSV and formatted text files. If
you're just trying to read the data in a recordset and then do something
with, the Text driver should handle it. There's a short example at http://www.vb-helper.com/howto_ado_load_csv.html

Dan


.

Relevant Pages

  • Driving Excel from a DataSet
    ... and this may be the wrong forum. ... dataset that i would like to use to create an excel spreadsheet. ... dynamically generate a .csv file from my dataset and then simply 'drive' ...
    (microsoft.public.data.ado)
  • Re: CSV import, excel corrupts data
    ... I guess if you want to use excel, you'll have to play by its rules. ... If opening .csv files directly doesn't work for you, ... Doing the macro trick corrupts the data as described when the file is a ... empty worksheet, use the data import wizard to select the csv file, ...
    (microsoft.public.excel)
  • Re: CSV import, excel corrupts data
    ... It _does_ bring up the data import wizard. ... There really seems to be no way to accomplish this in excel. ... Doing the macro trick corrupts the data as described when the file is a ... empty worksheet, use the data import wizard to select the csv file, ...
    (microsoft.public.excel)
  • Re: Basic Program to Import a CSV file
    ... Given that the OP asked about importing a CSV file into UV, ... some Excel to MV tool to import a CSV file into UV? ... Hey, why stop at replacing the database, replace the spreadsheet tools ... The directory file may well be UV ...
    (comp.databases.pick)
  • Re: CSV import, excel corrupts data
    ... If you rename your .csv file to .txt, you can have complete control over each ... field--you can choose Text for the fields that excel thinks are dates (but ... Now Excel utterly corrupts the data and displays some numeric ... its type from standard to text and finally confirm the import at cell A1. ...
    (microsoft.public.excel)