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?
[code]
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()
[/code]