Link to home
Create AccountLog in
Avatar of Jeff Fillegar
Jeff FillegarFlag for United States of America

asked on

C# - format column when writing to CSV file

Experts,

Is there a way to format the column in a csv file?  When writing to the csv file the values are correct but when user opens file the format is not what expected.  I have 2 columns: property and value.

This is what the datatable has in it by virtue of a datagridview
property, value
id,412570631141
vacant,false

When viewing the csv file ouput looks like this:
property,value
id,4.12571E+11
vacant,FALSE

How can i format the column so that the values match what they see on screen(datagridview)?  

Code used to write datatable to csv file attached.

Thanks in advance.

using (StreamWriter sw = new StreamWriter(filePath))
{
    foreach (DataColumn col in dt.Columns)
    {
        sw.Write(col.ColumnName + ",");
    }
    sw.WriteLine();

    foreach (DataRow row in dt.Rows)
    {
        foreach (DataColumn col in dt.Columns)
        {
            sw.Write(row[col] + ",");
        }
        sw.WriteLine();
    }
}

Open in new window

Avatar of kaufmed
kaufmed
Flag of United States of America image

It sounds like you are opening the file in Excel (or another spreadsheet software). If you right-click the cell and choose "Format Cells," you can go to the "Number" tab and change the format to have it display properly. You can also select multiple cells and do it all at one time. Your CSV is fine; it's Excel playing tricks on you  = )
Avatar of Jeff Fillegar

ASKER

Our users know how to format cells in Excel; however, we are trying to prevent them from having to do that.  
ASKER CERTIFIED SOLUTION
Avatar of kaufmed
kaufmed
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
You could output to a "Symbolic Link" (.slk) file instead:
http://en.wikipedia.org/wiki/SYmbolic_LinK_(SYLK)
http://www.pindari.com/sylk.html

*Just learned this today*

Example:  
ID;P
P;P0
C;Y1;X1;K"id"
C;Y1;X2;K412570631141
C;Y2;X1;K"vacant"
C;Y2;X2;Kfalse
F;P0;C2
F;W2 2 15
E

Open in new window


Lines #2, #7 and #8 tell Excel to format Column 2 using a numeric format with no decimal places and makes it 15 chars wide.
@Idle_Mind
Very interesting, however, it seems rather obscure--especially since there is no official specification. What are your thoughts on this going away at some point (e.g. newer versions of Excel)?
Avatar of Rick
Rick

Try:


double dbl;

using (StreamWriter sw = new StreamWriter(filePath))
{
    foreach (DataColumn col in dt.Columns)
    {
        sw.Write(col.ColumnName + ",");
    }
    sw.WriteLine();

    foreach (DataRow row in dt.Rows)
    {
        foreach (DataColumn col in dt.Columns)
        {
            if (double.TryParse((string)row[col], out dbl))
                    {
                        sw.Write("=TEXT(C1," + '\u0022' + row[col] + '\u0022' + "),");
                    }
                    else
                    {
                        sw.Write(row[col] + ",");
                    }
        }
        sw.WriteLine();
    }
}

Obscure seems almost to kind!  I personally wouldn't bet on it being supported from one version to the next and wouldn't use it for anything other than a convenience for a specific type of user in a very specific, tested environment.  ;)

@rick, wouldn't that make the cell a string value and therefore wouldn't work if you wanted to do a calculation on the column (like SUM)?
@Idle_Mind,

It worked for me on Excel 07 and 10. User generated image
j420exe1,

If you decide to go with this you'll just need to change your delimiter to something else than ","
@rick - based on your solution, what would you recommend other than the comma delimiter if this is creating a cvs file?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Excel won't open that correctly though...everything would end up in the first column.  =\
    sw.Write("=NUMBER(C1," + '\u0022' + row[col] + '\u0022' + ");");

Except that instead of NUMBER, you want TEXT on the line above
@Idle_Mind,

It worked for me on 07 and 10.
 User generated image
Interesting.  I'm running Excel 2010 and this is what I get with a semi-colon delimited file:
Semi-ColonDelimited.png
test-semi-colon-file.csv
*That same file with commas instead of semi-colons opens correctly into different columns (albeit with the original formatting issue).
Well, when you open any csv file with Excel, if you just click the "Finish" button on step 1 of the "Text Import Wizard", that's what you end up with (everything in the first column).

You need to go to step 2 and select your data delimiter, then click the "Finish" button.
I don't get a wizard...it goes straight to the worksheet and appears as in the screenshot.
That's strange. When I open any delimiter-separated text file with Excel 07 or 10, this is what I get: User generated image User generated image
I wonder what the difference is.  As far as I know, I haven't changed any settings.

To get that dialog I have to switch to the "Data" Tab and hit the "From" Text Button.
ExcelDataFromTextButton.png
I don't know... I haven't changed any settings either.
I remember opening delimiter-separated files with Excel 2000 and 2003, some years ago, and having the same type of Wizard come up.
I think the difference is I'm double clicking the .csv file in Windows Explorer?
for me the data ended up in the same column when using the semicolon.  I did not get the import wizard prompt either.
Take a look at this link
...

If the file has a csv extension, Excel will immediately open it with your default data format settings. But if the file has a txt extension (or anything else), Excel automatically starts the Import Text wizard.

So the options you have are:

- Change your code to output a file with a txt extension (instead of csv)

- Change the "List Separator" from comma to semicolon under "Region and Language" settings -- No need to change the code --

- Go with 37101448
I split the points based on the discussion and the points that I tried.