Solved

C# Export xls to csv .. connection string?

Posted on 2013-06-22
14
725 Views
Last Modified: 2013-06-24
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
Comment
Question by:Delta7428
  • 6
  • 5
  • 3
14 Comments
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 400 total points
ID: 39268854
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
 

Author Comment

by:Delta7428
ID: 39269190
!! 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
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 39269291
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
 
LVL 11

Expert Comment

by:lenordiste
ID: 39269377
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
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 39269482
@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
 
LVL 35

Assisted Solution

by:Robert Schutt
Robert Schutt earned 400 total points
ID: 39269572
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
 
LVL 11

Assisted Solution

by:lenordiste
lenordiste earned 100 total points
ID: 39269613
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:Delta7428
ID: 39269844
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
 
LVL 11

Assisted Solution

by:lenordiste
lenordiste earned 100 total points
ID: 39269865
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
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 39269885
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
 

Author Comment

by:Delta7428
ID: 39269977
kewl.  I will get back to this in the a.m.

TY!
0
 

Author Comment

by:Delta7428
ID: 39269990
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
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 39269998
Glad to hear all is well now!
0
 

Author Comment

by:Delta7428
ID: 39270131
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now