im trying to read data from a .csv file using similar costs like yours.
however i keep hitting a error:
"System.Data.OleDb.OleDbException: External table is not in the expected format.
my connection string is as follows:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\blablabla\blablabla\dumbFile.CSV;Extended Properties="Excel 8.0;HDR=YES;""
and this is my query string:
"select * from [dumbWorksheetName$]"
the first row of my csv file contains the respective columns names, with the rest of the rows being data
any suggestions to what i'm doing wrong???
am i supposed to remove the '$' from the query string?
Theres always a better way of doing things,
Cheers,
but i tested that code before and there is some problems wit it.. If i have a column in excel sheet whose first row value is a string then the dataset expects all other values as string.. and any numeric value encountered will get a null value..
i know this aint a much feasible solution... but i have tried datareader, dataadapter (when it reads gives you a dbnull) and streamreader (cant even read)
and since nobody seems to have a solution so far, this is the best you can go until somebody gives a better post...
Theres always a better way of doing things,
Cheers,
It's an ODBC issue, you must tell your connection that the fields (cells) can be in mixed mode, otherwise the ODBC driver will try to classify the field as numeric. And if it finds anything other than numeric in the cell it will return a null value. The
work around is in setting a property in your connection string. The property is IMEX, by setting this to 1 it will treat the cells as intermixed and will return the value of the cell. Below is how you should set your connection string up....
myConnection =
new
OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data source=" + strPath +
"; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'");
You don't need to add the HDR=Yes, because that is the default value. Also, remember to place the quotes aropund the Extended Properties= values.
A Simple method how to Read all Sheets or tables in an Excel WorkSheet (Sheet1, Sheet2, Sheet3, UserList, User Details, etc..), this will be helpful to all....
Here an Example..
Dim objExcelConn As New OleDb.OleDbConnection
Dim i As Integer
Dim sSName As String = ""
Dim dtTables As DataTable
''' Selects File & Loops Through the Records
objExcelConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & iSubPath & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""
objExcelConn.Open()
I am able to read excel (2007) using the method defined above. The problem I am having is - I am unable to read more than 255 columns from the excel sheet. Although excel 2007 now allows a sufficiently large number of columns, I am unable to take advantage
of this feature. Any suggestions?
By the way I have changed the connection string slightly to use excel 2007 driver:
anzer
Star
7928 Points
1506 Posts
READ EXCEL SHEET
Oct 27, 2006 05:35 AM|LINK
Hi,
I want to read multiple sheets from an excel document. Is there any free codes for this..
I know how to process CSV.. so please send me if there is any code to detach each work sheet to different CSV files.
Thanks in advance
Anz
excel
ClientSideAsp.Net | Blog
Jai B
Member
73 Points
22 Posts
Re: READ EXCEL SHEET
Oct 27, 2006 08:28 AM|LINK
This could help:
public DataSet Read(string path,string sheetName)
{
string connectionString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""",path);
string query=String.Format("select * from [{0}$]",sheetName);
OleDbDataAdapter dataAdapter =new OleDbDataAdapter (query, connectionString);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
return dataSet;
}
Jai
AnjinG
Participant
1224 Points
265 Posts
Re: READ EXCEL SHEET
Oct 27, 2006 10:02 AM|LINK
Hi,
im trying to read data from a .csv file using similar costs like yours.
however i keep hitting a error:
"System.Data.OleDb.OleDbException: External table is not in the expected format.
my connection string is as follows:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\blablabla\blablabla\dumbFile.CSV;Extended Properties="Excel 8.0;HDR=YES;""
and this is my query string:
"select * from [dumbWorksheetName$]"
the first row of my csv file contains the respective columns names, with the rest of the rows being data
any suggestions to what i'm doing wrong???
am i supposed to remove the '$' from the query string?
Cheers,
anzer
Star
7928 Points
1506 Posts
Re: READ EXCEL SHEET
Oct 27, 2006 12:33 PM|LINK
Hi JaiB,
thank u for ur reply,
but i tested that code before and there is some problems wit it.. If i have a column in excel sheet whose first row value is a string then the dataset expects all other values as string.. and any numeric value encountered will get a null value..
eg : - column1 ----- Column2
value1 | 1
value2 | abc
34 | 33
After excecuting the above code in this we will get a dataset with values
column1 ----- Column2
value1 | 1
value2 |
| 33
Is there any solution for this... please help
Anzer
excel
ClientSideAsp.Net | Blog
AnjinG
Participant
1224 Points
265 Posts
Re: READ EXCEL SHEET
Nov 27, 2006 02:26 AM|LINK
Hi,
I have worked around this problem by editing the excel sheet manually
you need to key in a ' in any cell that has a pure numeric value
eg :
- column1 ----- Column2
value1 | '1
value2 | abc
'34 | '33
dataset with values :
column1 ----- Column2
value1 | 1
value2 | abc
34 | 33
i know this aint a much feasible solution... but i have tried datareader, dataadapter (when it reads gives you a dbnull) and streamreader (cant even read)
and since nobody seems to have a solution so far, this is the best you can go until somebody gives a better post...
Cheers,
rstepan
Participant
1186 Points
204 Posts
Re: READ EXCEL SHEET
Jun 27, 2007 04:25 PM|LINK
It's an ODBC issue, you must tell your connection that the fields (cells) can be in mixed mode, otherwise the ODBC driver will try to classify the field as numeric. And if it finds anything other than numeric in the cell it will return a null value. The work around is in setting a property in your connection string. The property is IMEX, by setting this to 1 it will treat the cells as intermixed and will return the value of the cell. Below is how you should set your connection string up....
myConnection =
new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data source=" + strPath + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'");You don't need to add the HDR=Yes, because that is the default value. Also, remember to place the quotes aropund the Extended Properties= values.
Excel IMEX
White Bread
Member
11 Points
8 Posts
Re: READ EXCEL SHEET
Sep 29, 2007 08:11 AM|LINK
Try using `'table name$'` instead of [tablename$]
yeah buddy
White Bread
http://www.deasonguitars.com
suthish nair
All-Star
15176 Points
3304 Posts
Re: READ EXCEL SHEET
Jan 21, 2008 02:54 PM|LINK
Query Solved. :-)
A Simple method how to Read all Sheets or tables in an Excel WorkSheet (Sheet1, Sheet2, Sheet3, UserList, User Details, etc..), this will be helpful to all....
Here an Example..
Dim objExcelConn As New OleDb.OleDbConnection
Dim i As Integer
Dim sSName As String = ""
Dim dtTables As DataTable
''' Selects File & Loops Through the Records
objExcelConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & iSubPath & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""
objExcelConn.Open()
dtTables = objExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
If dtTables.Rows.Count > 0 Then
For i = 0 To dtTables.Rows.Count - 1
sSName = dtTables.Rows(i)("TABLE_NAME")
sSName = Replace(sSName, "'", "")
sSName = Replace(sSName, "$", "")
If sSName = "User Details" Then
sSql = ""
sSql = String.Format("select * from [{0}$]", sSName)
objAdapter = New OleDb.OleDbDataAdapter(sSql, objExcelConn)
objAdapter.Fill(objDataSet)
If objDataSet.Tables(0).Rows.Count <= 1 Then
''''' Your Logic goes
End If
End If
Next
End If
My Blog
AndreiR23
Member
14 Points
9 Posts
Re: READ EXCEL SHEET
Mar 12, 2008 04:52 PM|LINK
@rstepan : Thanks man, you saved me [Yes]
FahadAhmed
Member
2 Points
1 Post
Re: READ EXCEL SHEET
May 20, 2008 10:54 AM|LINK
I am able to read excel (2007) using the method defined above. The problem I am having is - I am unable to read more than 255 columns from the excel sheet. Although excel 2007 now allows a sufficiently large number of columns, I am unable to take advantage of this feature. Any suggestions?
By the way I have changed the connection string slightly to use excel 2007 driver:
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
+ FilePath +
";Extended Properties='Excel 12.0;HDR=YES';";Thanks in advance.
Excel 2007 255 column limit Oledb