C# Export xls to csv .. connection string?

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

!! 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! :)

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.

lenordiste

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.

@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

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

lenordiste

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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

SOLUTION

lenordiste

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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

ASKER

kewl. I will get back to this in the a.m.

TY!

ASKER

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

Glad to hear all is well now!