C# Export xls to csv .. connection string?

I am trying to export an Excel spreadsheet to csv from some code I found and converted from console to form app.

It seems the connection is not opening.  Is my connection string or something else?

I am using Excel 2002.

Bitmap of complete error msg and Spreadsheet are attached.

I plan to go car shopping as soon as I get this figured out.  lol  :(

The Error:
--------------
System.argumentExceptin: Format of the initialization string does not conform to specification starting at index 97 at System.Data.Common.DbConnections ..



The Code:
-------------
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
namespace XlsToCsv
{
    public partial class Form1 : Form
    {
        public Form1()
        {

            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
           
            string sourceFile, worksheetName, targetFile;

            sourceFile = "C:\\Keys\\Book2.xls"; worksheetName = "Sheet1"; targetFile = "C:\\Keys\\target.csv";

            if (File.Exists(sourceFile))
            {
                MessageBox.Show("howdy");
                convertExcelToCSV(sourceFile, worksheetName, targetFile);
            }
            else
            {
                MessageBox.Show("nobody home");
            }
        }

        static void convertExcelToCSV(string sourceFile, string worksheetName, string targetFile)
        {
            string strConn = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\xl.xls;Extended Properties=\"Excel 10.0\";\"HDR=Yes;IMEX=1\"");

            OleDbConnection conn = null;

            StreamWriter wrtr = null;

            OleDbCommand cmd = null;

            OleDbDataAdapter da = null;

            try
            {

                conn = new OleDbConnection(strConn);

                conn.Open();

                cmd = new OleDbCommand("SELECT * FROM [" + worksheetName + "$]", conn);

                cmd.CommandType = CommandType.Text;

                wrtr = new StreamWriter(targetFile);

                da = new OleDbDataAdapter(cmd);

                DataTable dt = new DataTable();

                da.Fill(dt);

                for (int x = 0; x < dt.Rows.Count; x++)
                {

                    string rowString = "";

                   

                    for (int y = 0; y < dt.Columns.Count; y++)
                    {

                        rowString += "\"" + dt.Rows[x][y].ToString() + "\",";

                    }

                    wrtr.WriteLine(rowString);

                }

                MessageBox.Show("Done! Your " + sourceFile + " has been converted into " + targetFile + ".");

            }

            catch (Exception exc)
            {
                MessageBox.Show(exc.ToString());

            }

            finally
            {

                if (conn.State == ConnectionState.Open)

                conn.Close();

                conn.Dispose();

                cmd.Dispose();

                da.Dispose();

                wrtr.Close();

                wrtr.Dispose();

            }

        }
    }
}
Book2.xls
TheERROR.bmp
Delta7428Asked:
Who is Participating?
 
Robert SchuttConnect With a Mentor Software EngineerCommented:
This works for me:
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sourceFile + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";

Open in new window

So 3 changes:
- removed extra quotes in the Extended Properties
- use variable instead of hardcoded filename
- [optional] changed Excel version in the extended properties, that may just be something that was needed on my system (even older excel version installed).
0
 
Delta7428Author Commented:
!! works !!  

And with exactly the results I expected - including the extraneous comma at then end of each row.

I noticed that the "real" spreadsheets I am working with have several extraneous commas at the end of each row when I save to csv from Excel - likely due to blank columns.

I intentionally added some spaces into the last column of my test spreadsheet,,, lo and behold, there's an extra comma in the csv.

This should be an easy fix I think.  If not, I'll post a new question.

I'll play around with it when I get back from from brother's bday breakfast.

If you have a quick solution, here, that would be great.

You have points coming either way.

Thank you Robert!  :)
0
 
Robert SchuttSoftware EngineerCommented:
Well, I'm not sure what causes the extra commas because when I add a space in the 4th column, then look at the converted file I see a space in quotes and another comma after that. Also, after removing the space and saving, control+end takes me to the end of the 3rd column so it would seem there is nothing to warrant that extra comma. So no, I'm afraid I can't come up with a quick solution.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
lenordisteCommented:
might be related to the dreaded "last cell syndrom" in excel :-) http://support.microsoft.com/kb/244435/en-us

I don't recall having this issue but you may want to clear the n+1 column to make sure an extra comma is not created.
0
 
Robert SchuttSoftware EngineerCommented:
@lenordiste:
Worth a try, good find!

Clearing 'All' to get rid of extra formatting seemed promising but on my system the manual method didn't make a difference (I didn't try the add-in). Even copying only the used range to a blank workbook didn't help.

Some more things I tried that didn't get rid of that extra comma:
- changing the "SELECT *" to "SELECT Field1, Field2, Field3"
- changing to IMEX=0
- changing to the ACE driver
0
 
Robert SchuttConnect With a Mentor Software EngineerCommented:
Erm, looking at this again, it's actually just the export code that's doing this ;-)

Try changing to this:
                    for (int y = 0; y < dt.Columns.Count; y++)
                    {
                        if (y > 0)
                            rowString += ",";
                        rowString += "\"" + dt.Rows[x][y].ToString() + "\"";
                    }

Open in new window

0
 
lenordisteConnect With a Mentor Commented:
you might want to do this to avoid a nested loop and not having to resort to a IF condition:

for (int x = 0; x < dt.Rows.Count; x++)
{
   //get an array of quoted strings
   var rowArray= dt.Rows[x].ItemArray.Select(row =>"\""+row.ToString()+"\"").ToArray();
   //join the strings with a comma and write the line
   wrtr.WriteLine(String.Join(",",rowArray"));
}

Open in new window

0
 
Delta7428Author Commented:
You guys rock!

Sorry for the delay in getting back.  IT IS Sunday, ya know?  What a bunch of nerds we are, ya think?? ;)

Robert, your code got rid of the comma at the end of each row.  As far as what is causing it, it seems a fluke to me that the code got rid of it.

Like I posted previously ...,the "real" spreadsheets throw in a comma, actually 3 or 4 when I open the spreadsheet and select "save as" .. to csv.

So ... I tried selected the last 3 or 4 columns in Excel, and deleting them .. then converting to CSV, doing a "save as" in Excel.

I will have to test tomorrow on the production spreadsheets as I at home and don't have access right now.

Thank you!

------------------------------------------
lenordiste,

Using your code, I am getting :

At this line >
----------------
   wrtr.WriteLine(String.Join(",",rowArray"));         }

I am getting >
-----------------
* newline in constant
* ) expected
* ; expected

>>>>>>>>>>>>>>>>


Heres the proc with your code inserted.  It may just be a brrracket squiggly issue lol ... I am a little cross-eyed at this point and not able to pinpoint it.

------------------------------------------
namespace XlsToCsv
{
    public partial class Form1 : Form
    {
        public Form1()
        {

            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
           
            string sourceFile, worksheetName, targetFile;

            sourceFile = "C:\\Keys\\Book2.xls"; worksheetName = "Sheet1"; targetFile = "C:\\Keys\\target.csv";

            if (File.Exists(sourceFile))
            {
                MessageBox.Show("howdy");
                convertExcelToCSV(sourceFile, worksheetName, targetFile);
            }
            else
            {
                MessageBox.Show("nobody home");
            }
        }

        static void convertExcelToCSV(string sourceFile, string worksheetName, string targetFile)
        {
            //string strConn = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\xl.xls;Extended Properties=\"Excel 10.0\";\"HDR=Yes;IMEX=1\"");
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sourceFile + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";

            OleDbConnection conn = null;

            StreamWriter wrtr = null;

            OleDbCommand cmd = null;

            OleDbDataAdapter da = null;

            try
            {

                conn = new OleDbConnection(strConn);

                conn.Open();

                cmd = new OleDbCommand("SELECT * FROM [" + worksheetName + "$]", conn);

                cmd.CommandType = CommandType.Text;

                wrtr = new StreamWriter(targetFile);

                da = new OleDbDataAdapter(cmd);

                DataTable dt = new DataTable();

                da.Fill(dt);

                for (int x = 0; x < dt.Rows.Count; x++)
                {
                    string rowString = "";

                    for (int y = 0; y < dt.Columns.Count; y++)
                    {

                        //rowString += "\"" + dt.Rows[x][y].ToString() + "\",";

                       // if (y > 0)
                        //    rowString += ",";
                        //rowString += "\"" + dt.Rows[x][y].ToString() + "\"";

                        rowString += "\"" + dt.Rows[x][y].ToString() + "\"";
                        //get an array of quoted strings
                        var rowArray = dt.Rows[x].ItemArray.Select(row => "\"" + row.ToString() + "\"").ToArray();
                    }

                    //wrtr.WriteLine(rowString);

                    //join the strings with a comma and write the line
                       wrtr.WriteLine(String.Join(",",rowArray"));        

                }

                MessageBox.Show("Done! Your " + sourceFile + " has been converted into " + targetFile + ".");

            }

            catch (Exception exc)
            {
                MessageBox.Show(exc.ToString());

            }

            finally
            {

                if (conn.State == ConnectionState.Open)
                    MessageBox.Show("BOO");
                conn.Close();

                conn.Dispose();

                cmd.Dispose();

                da.Dispose();

                wrtr.Close();

                wrtr.Dispose();

            }

        }
    }
}


Thx Denise
0
 
lenordisteConnect With a Mentor Commented:
my bad, an extra double quotation mark character jumped in !

this should be:
wrtr.WriteLine(String.Join(",",rowArray));

Open in new window

or if you prefere for clarity's sake:
var rowCsv=String.Join(",",rowArray);
wrtr.WriteLine(rowCsv);

Open in new window

0
 
Robert SchuttSoftware EngineerCommented:
Note that the strength of the Linq select is that you don't need the inner loop anymore. In the new code you posted above you can also comment out:
for (int y = 0; y < dt.Columns.Count; y++)

Open in new window

0
 
Delta7428Author Commented:
kewl.  I will get back to this in the a.m.

TY!
0
 
Delta7428Author Commented:
Side note:

http://www.youtube.com/watch?v=bauz0nMgsDo

http://www.youtube.com/watch?v=lehmZgaFMH8

Posting this may be against EE's code of acceptable posts...??

Re: personal experience this afternoon.  All is well.  Whew
0
 
Robert SchuttSoftware EngineerCommented:
Glad to hear all is well now!
0
 
Delta7428Author Commented:
Thank you Mr Expert.  And thx for checking out vid.  I owe all you EE guys and girls sooooo much .. over the years.

I should close this and award your points, but I would like to take a final look tomorrow a.m, est, when my head is clear in case I have another question or 2.

Hope we all had a good weekend in spite of ourselves!

Denise
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.