Click here to Skip to main content
14,733,912 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
I am using C# in an asp.net application to import an excel spreadsheet and do some processing. If one of the cells in a column that is normally formatted as 'Number' is changed to 'Text', the value isn't read. That cell is seen as DBNull. I am using the DbProviderFactory. I have set IMEX=1 in my connection string. Below is my code. Can someone tell me how to import an Excel spreadsheet without losing values that are formatted incorrectly? Thanks much!

public static List<Patient> ReadPatientSpreadsheet(string Clinic, DateTime MeasureEndDate)
{
    {
        string CONNECTIONSTRING = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Data\Quality\PCAHTN.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";";
        List<Patient> PatientList = null;
        // Read spreadsheet and populate a patient object
        DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
        PatientList = new List<Patient>();
        using (DbConnection connection = factory.CreateConnection())
        {
            connection.ConnectionString = CONNECTIONSTRING;
            using (DbCommand command = connection.CreateCommand())
            {
                command.CommandText = "SELECT * FROM [Query$]";
                connection.Open();
                using (DbDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        Patient p = new Patient();
                        if (!(dr["PatientName"] is DBNull))
                        {
                            p.PatientID = (dr["PatientID"].ToString().Trim());
                            p.PatientName = dr["PatientName"].ToString().Trim();
                            p.PatientDOB = (Convert.ToDateTime(dr["PatientDOB"]));
                            p.PatientGender = dr["PatientGender"].ToString().Trim();
Posted
Updated 6-Jan-11 11:19am
v5
Comments
HimanshuJoshi 6-Jan-11 16:12pm
   
Edited to add PRE blocks

I finally found an easy solution to this problem. As I'm reading the excel file, I do a tryparse to determine if the value is an integer. If it is, I convert the serial date to a DateTime. Here is my date cleaning job.

<pre lang="cs">public static DateTime? CleanDateField(string DateField)
{
    // Convert the text to DateTime and return the value or null
    DateTime? CleanDate = new DateTime();
    int intDate;
    bool DateIsInt = int.TryParse(DateField, out intDate);
    if (DateIsInt)
    {
        // If this is a serial date, convert it
        CleanDate = DateTime.FromOADate(intDate);
    }
    else if (DateField.Length != 0 && DateField != "1/1/0001 12:00:00 AM" &&
        DateField != "1/1/1753 12:00:00 AM")
    {
        // Convert from a General format
        CleanDate = (Convert.ToDateTime(DateField));
    }
    else
    {
        // Date is blank
        CleanDate = null;
    }
    return CleanDate;
}
   
Comments
maitruc87 11-Jun-15 4:39am
   
Im my excel file have column'format is "dd/mm/yyyy". But whern i read this column, output data is "mm/dd/yyyy".

Can you help me???
Have you looked here? Clickety[^]

I have had many problems in the past importing and exporting data from Excel in regards to formatting.
   
Comments
William Winner 10-Jan-11 13:27pm
   
From OP:

Thanks, Slacker007. I checked the link you provided but don't see an answer. I'm new to programming so maybe I am missing it. Can you be more specific?
You are probably better of using one of these libraries to get the information from excel:
Excel Data Reader - Read Excel files in .NET[^]

EPPlus[^]

Take a look at the provided examples - that should help

Regards
Espen Harlinn
   
Well, you don't need to use third-party software and you definitely don't need to treat it as a database.

Microsoft has provided anyone that wants to download them with the Primary Interop Assemblies.

Using the Excel PIA is a much more reliable way of accessing an Excel spreadsheet.

See here: How to automate Microsoft Excel from Microsoft Visual C#.NET[^]
   
Comments
Nancy3595 12-Jan-11 11:29am
   
I'd rather not use third-party software. I read the article recommended by William Winner and it looked promising. This article shows examples on how to create spreadsheets, however, and I am trying to read a spreadsheet with inconsistent data types. I looked for a similar article for reading spreadsheets and didn't find one. I am using a workaround for now where I will change the format of the columns that are supposed to be 'number' to 'text' before reading it. This way, all the numbers are read as text and my program converts them. From other articles, it appears using OLEDB will always read a null when a cell is formatted incorrectly. If anyone can tell me how to read an excel spreadsheet without using OLEDB, I'd appreciate it. Meanwhile, thanks to everyone who offered help.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900