Hi,
Getting a weird problem when uploading a spreadsheet via C#.
Here's an example of the spreadsheet I'm using:
JournalISSN
-----------
1650327
223956
1651781
9254927
3064530
9209964
924977X <- this is causing an error
3768716
My upload code (snipped for brevity)
string connectionString = "Provider=Microsoft.Jet.OleDb.4.0; Data Source="
+ Server.MapPath("/admin/journals/upload.xls") + "; Extended Properties=\"Excel 8.0;HDR=YES;\"";
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
using (DbCommand Command = Connection.CreateCommand())
{
Command.CommandText = "SELECT [JournalISSN], JournalTitle, JournalDescription, ImpactFactor, Psychology "
+ "FROM [Sheet1$]";
using (DbDataReader DataReader = Command.ExecuteReader())
{
while (DataReader.Read())
{
Response.Write(string.IsNullOrEmpty(DataReader["JournalISSN"].ToString()) + "<br/>");
}
}
Now what happens, is when the DataReader gets to the row with the "X" in the "JournalISSN" column, it returns True (so it's null/ empty), all other columns return false. So the problem seems to be that whenever a column contains a character that is not numeric, it does not get pulled out during the upload.
A few things i've tried:
- Moving the 'X' to different positions in the text (ie. 111X111)
- Changing to a different letter
- Deleting the cell and starting again
- Setting the format type in excel to "General" - applied on all rows.
Would be grateful for any help/ insight as to why this is happening.
Cheers, Sean