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]