decimal data in .xls file not being read and/or converted to .txt file

Question

3.00/5 (1 vote)

See more:

I am creating a web app that allows the user to choose which report they are trying to convert and upload the associated file. I am using the OleDbCommand and OleDbConnection to acess the .xls file. All the rows and columns of the .xls file show up when converted to the .txt file except for a column of decimal values. Can someone take a look and point me in the right direction? Thank you!

Here is the code I have so far:

if (ddlType.SelectedValue == "direct-debit")
        {
            string filename = FileUpload1.PostedFile.FileName.ToString();
            if (filename != "")
            {
                FileInfo file = new FileInfo(filename);
                if (file.Exists)
                {
                    Excel.Application xlApp;
                    Excel.Workbook xlWorkBook;
                    Excel.Worksheet xlWorkSheet;
                    object misValue = System.Reflection.Missing.Value;
                    xlApp = new Excel.ApplicationClass();
                    xlWorkBook = xlApp.Workbooks.Open(@"C:\directdebitTEST\directdebitTEST.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                    //The connection string to the excel file
                    string connstr = @"Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\directdebitTEST\directdebitTEST.xls;Extended Properties=Excel 8.0";
                    //The connection to that file
                    OleDbConnection conn = new OleDbConnection(connstr);
                    //The query
                    string strSQL = "SELECT * FROM [Sheet1$]";
                    //The command 
                    OleDbCommand cmd = new OleDbCommand(strSQL, conn);
                    DataTable dt = new DataTable();
                    conn.Open();
                    try
                    {
                        OleDbDataReader dr1 = cmd.ExecuteReader();
                        StreamWriter sw = new StreamWriter(@"C:\directdebitTEST\directdebitExport.txt");
                        if (dr1.Read())
                        {
                            dt.Load(dr1);
                        }
                        int iColCount = 3;
                            //dt.Columns.Count;
                        for (int i = 0; i < iColCount; i++)
                        {
                            sw.Write(dt.Columns[i]);
                            if (i < iColCount - 1)
                            {
                                sw.Write("\t");
                            }
                        }
                        sw.Write(sw.NewLine);
                        // Now write all the rows.
                        foreach (DataRow dr in dt.Rows)
                        {
                            if (dr.Field<string>("F1") != "User Group" && dr.Field<string>("F1") != "Batch Totals" && dr.Field<string>("F1") != "Grand Totals"
                                && dr.Field<string>("F1") != "Batch ID:" && dr.Field<string>("F1") != "Item Count")
                            {
                                sw.Write("6                           ");
                                for (int i = 0; i < iColCount; i++)
                                {
                                    if (!Convert.IsDBNull(dr[i]))
                                    {
                                        sw.Write(dr[i].ToString());
                                    }
                                    if (i < iColCount - 1)
                                    {
                                        sw.Write("\t");
                                    }
                                }
                                
                                sw.Write(sw.NewLine);
                            }
                        }
                        sw.Write("9");
                        sw.Close();
                    }
                    catch (OleDbException caught)
                    {
                        Response.Write(caught.Message);
                    }
                    finally
                    {
                        conn.Close();
                    }
                    xlWorkBook.Close(true, misValue, misValue);
                    xlApp.Quit();
                    string fileName = "directdebitExport.txt";
                    string filePath = @"C:\directdebitTEST\directdebitExport.txt";
                    Response.Clear();
                    Response.AppendHeader("content-disposition",
                    "attachment; filename=" + fileName);
                    Response.ContentType = "application/octet-stream";
                    Response.WriteFile(filePath);
                    Response.Flush();
                    Response.End();
                }
            }
            else
            {
                Response.Write("This file does not exist.");
            }
        }

Posted 15-May-12 8:43am

Richard C Bishop

Updated 15-May-12 8:49am

v2

Add a Solution

Comments

Richard MacCutchan 15-May-12 15:09pm

It would help if you gave us a clue as to where the conversion is and what happens there.

Richard C Bishop 15-May-12 16:17pm

Well, I am creating a datatable "dt" with my OleDbCommand that selects everything from sheet1 of the .xls file. Once the datatable is created, I load an OleDbDataReader into the datatable and use a Streamwriter to write it out to a .txt file. So I guess there is not really any data conversion, just file type conversion.

Here is where the .xls file is read, streamwriter created and loading the datatable:

OleDbConnection conn = new OleDbConnection(connstr);
//The query
string strSQL = "SELECT * FROM [Sheet1$]";
//The command
OleDbCommand cmd = new OleDbCommand(strSQL, conn);
DataTable dt = new DataTable();
conn.Open();
try
{
OleDbDataReader dr1 = cmd.ExecuteReader();
StreamWriter sw = new StreamWriter(@"C:\directdebitTEST\directdebitExport.txt");
if (dr1.Read())
{
dt.Load(dr1);
}
The code goes on to read all the rows and columns and then prompts the user to save the new file type. I hope this helped.

Richard MacCutchan 15-May-12 16:52pm

Somewhere in here you are reading a "decimal" value or values, and (presumably) writing it out to your text file. How is that decimal value converted from a number to a text string?

Richard C Bishop 15-May-12 17:04pm

Well it is not being converted, so is that my problem?

Richard MacCutchan 15-May-12 17:08pm

Well that's pretty obvious. But you still have not answered my question: "How (or where) is that decimal value supposed to be converted from a number to a text string?".

Richard C Bishop 15-May-12 17:17pm

I'm sorry, I am new to this. I am needing the decimal value in the excel file to be captured and written to the text file. I do not have a place where the decimal is being converted to a string nor do I know where it should go. I must not understand your question, sorry again.

Richard MacCutchan 15-May-12 17:41pm

My question is quite simple (or at least it would seem so to me). You are reading data from an Excel file into your program. Some cells of that Excel file contain decimal numbers which you wish to write out to a text file. So, somewhere in your program you need to add some code to take those values and convert them to text strings before writing them out to the file. Or are you expecting the conversion to be done automatically somewhere?

Richard C Bishop 16-May-12 10:10am

I got ya. I just assumed that all the data in my columns would be read and sent to my datatable. I didn't not even think of having to convert anything. So to answer your question, I don't have anything converting decimals to strings.

[no name] 15-May-12 17:23pm

The column that you are missing would not happen to be the last one in the .xls file?

Richard C Bishop 15-May-12 17:24pm

I get all the columns in the excel file before and after the decimal value column

Richard C Bishop 15-May-12 17:26pm

So no, the last column is captured, only the decimal column is left out.

Richard MacCutchan 16-May-12 5:33am

I have just revisited this as I have a sample program that does something similar but gets all values converted correctly. I note that you have some code using the Excel Interop namespace and some code using OleDB. Can you explain why you have both, and what each part is supposed to do?

Richard C Bishop 16-May-12 10:08am

Actually I can't explain why because I don't know. I am justing using bits and pieces of code I found online to try and make this thing work. I am still in the learning stage trying to figure out what stuff does. But if I had to guess, I would say that the excel interop is used to access the .xls file I need and then the OleDB is used to read that info and create a datatable. I am sure my conception is misconstrued and would appreciate any advice.

Richard MacCutchan 16-May-12 10:26am

Yes, very misconstrued; you use one or other method, not both.

Quite frankly your approach is so wrong as to be almost off the scale. Downloading code from the net without any understanding of it will lead you into lots of blind alleys and bad practices. Get yourself a good book or two and look at some decent online learning materials such as .NET Book Zero, the MSDN C# Tutorials, and look at the articles here on CodeProject.

Richard C Bishop 16-May-12 11:03am

I see what you mean, thank you!

1 solution

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)

Maciej Los · Accepted Answer · 2012-05-15T10:51:00

In my opinion, the connection string is wrong.
Replace:

C#

string connstr = @"Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\directdebitTEST\directdebitTEST.xls;Extended Properties=Excel 8.0;";

with:

C#

string connstr = @"Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\directdebitTEST\directdebitTEST.xls;Extended Properties=Excel 8.0;HDR=no;IMEX=0;";

Important note!

The quota " in the string needs to be escaped using your language specific escape syntax.
c#, c++   \"
VB6, VBScript   ""
xml (web.config etc)   "
or maybe use a single quota '.

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.

SQL syntax "SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance. Please also note that adding the IMEX=1 option might cause the IMEX feature to set in after just 8 rows. Use IMEX=0 instead to be sure to force the registry TypeGuessRows=0 (scan all rows) to work.

If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: "Could not decrypt file."

Source: http://www.connectionstrings.com/excel[^]

Try to debug program to find out what data are written into text file...