Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 836
  • Last Modified:

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
0
Delta7428
Asked:
Delta7428
  • 6
  • 5
  • 3
4 Solutions
 
Robert SchuttSoftware 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 SchuttSoftware 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
 
lenordisteCommented:
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
 
lenordisteCommented:
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now