Excel Import problem with cell's format

Question

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 6-Jan-11 9:23am

Nancy3595

Updated 6-Jan-11 10:19am

Manfred Rudolf Bihy

v5

Add a Solution

Comments

HimanshuJoshi 6-Jan-11 16:12pm

Edited to add PRE blocks

4 solutions

Solution 1

Have you looked here? Clickety[^]

I have had many problems in the past importing and exporting data from Excel in regards to formatting.

Posted 6-Jan-11 21:36pm

Slacker007

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?

Solution 3

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

Posted 10-Jan-11 5:20am

Espen Harlinn

Solution 4

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[^]

Posted 10-Jan-11 7:30am

William Winner

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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Nancy3595 · Accepted Answer · 2011-06-15T03:34:00

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;
}

Excel Import problem with cell's format

4 solutions

Solution 5

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us