Reading Excel ?

[vbcoop]

Hi, I’m developing a tools that needs to read data from an excel sheet. I’ve had it working using Microsoft.Jet.OLEDB.4.0, but now I have noticed it truncates the cell value if the character length is over 255.
I found that if you change the register value for “typeGuessRows” it seems to work, but this is not an option on some of the systems the tool will run.

Then I found that if I use “OdbcConnection” it seems to not truncate the value, but it has its own issues.
This would be if a colmun has mostly numerical data, it makes the string values “DBNULL”.

Does anyone know a way around the DBNULL issue?

Dim con As New OdbcConnection()
Dim cmd As New OdbcCommand()
Dim PrmPathExcelFile As String
PrmPathExcelFile = "Raw_Translation_300_wpsegroflashint.xls"
con.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;DBQ=" + PrmPathExcelFile
con.Open()
cmd.Connection = con
cmd.CommandText = "SELECT * FROM [" & sheetName & "$]"

cmd.CommandType = CommandType.Text

   Do While rdr.Read()


   Form1.list.Items.Add(checkDBNULL(rdr(0)).ToString + " 1- " + checkDBNULL(rdr(1)).ToString + " 2- " + checkDBNULL(rdr(2)).ToString + " 3- " + checkDBNULL(rdr(3)).ToString + " 4- " + checkDBNULL(rdr(4)).ToString + " 15- " + checkDBNULL(rdr(5)).ToString + " 6- " + checkDBNULL(rdr(6)).ToString + " 7- " + checkDBNULL(rdr(7)).ToString)
    Loop
 rdr.Close()
con.Close()

 

[cjard]

Excel connection string samples - ConnectionStrings.com

read about IMEX=1, it *may* help, though whether your chosen driver supports it, i do not know

 

[vbcoop]

Thats for that, but it doesn't seem to work with this version of connection.

It works very well when you use the "Microsoft.Jet.OLEDB.4.0" connection.

Thanks tho

 

[cjard]

Open the xls in a hex editor and check its definitely binary excel format. If it's xml or html you have other options. You might be approaching the point where you have to use office interop. Ugh

 

[vbcoop]

This tool will by used by users that do not have the knowledge or tools (hex editor) to do that type of thing.

Thanks tho,

 

[cjard]

It's for you, the developer to do, not them. If they are saving the files, simply get them to save ina format you can read. I understood your problem to be that you have Excel files that you must parse, and ou cannot change anything about their production