C# writeline properly show leading zeros save as csv

Question

Got a problem in properly handling leading zeros in csv, already tried adding ' before the string value but in csv file it shows 'StringValue

Sample Output

Already tried \t it works but when i try to open it with notepad it shows a tab.

Is there other way I can solve this issue? My main purpose is that when it is open through csv file the ' must not show and when I open it with text editor it will not tab.

This is my current code

foreach (var i in TransmittedDataBLL.Instance.GetDepEdTextFile(data))
        {
            writer.WriteLine(
                "'" + i.Region.ToString() + ",'" + i.Division.ToString() + ",'" + i.Station.ToString() + ",'" + i.EmployeeNumber.ToString()
                + "," + i.FirstName.ToString() + "," + i.MiddleInitial.ToString() + "," + i.LastName.ToString() + "," + i.Appelation.ToString()
                + ",'" + i.DednCode.ToString() + "," + i.DednSubCode.ToString() + ",'" + i.EffectDate.ToString() + ",'" + i.TermDate.ToString()
                + ",'" + i.AmountStr.ToString() + ",'" + i.LoanAmountStr.ToString() + ",'" + i.InterestAmountStr.ToString() + ",'" + i.OtherChargesStr.ToString()
                + "," + i.BillingType.ToString() + "," + i.UpdateCode.ToString()
                );
        }

Updated Code:

writer.WriteLine("'{0}','{1}','{2}','{3}'", i.Region.ToString(), i.Division.ToString(), i.Station.ToString(), i.EmployeeNumber.ToString());

Updated Code: Same issue, leading zeros not showing

writer.WriteLine("\"{0}\",\"{1}\",\"{2}\",\"{3}\"", i.Region.ToString(), i.Division.ToString(), i.Station.ToString(), i.EmployeeNumber.ToString());

This the data from my query

SAMPLE RAW DATA

Full code:

private void CreateTextFile(string regioncode, string branchcode)
{
    string sFileName = Path.GetRandomFileName();
    string fileName = DateTime.Now.ToString("mmddyyyy") + ".csv";
    string Serverpath=ConfigurationManager.AppSettings["UploadPath"].ToString() + "\\DepEdReports\\";

    TransmittedData data = new TransmittedData();
    data.Region = regioncode.Trim();
    data.BranchCode = branchcode.Trim();

    if (!Directory.Exists(Serverpath))
        Directory.CreateDirectory(Serverpath);

    using (StreamWriter writer = new StreamWriter(Serverpath + sFileName + ".csv"))//Server.MapPath("TextFiles/" + sFileName + ".txt")))
    {
        foreach (var i in TransmittedDataBLL.Instance.GetDepEdTextFile(data))
        {
            //writer.WriteLine(
            //    i.Region + "," + i.Division + "," + i.Station + "," + i.EmployeeNumber
            //    + "," + i.FirstName + "," + i.MiddleInitial + "," + i.LastName + "," + i.Appelation
            //    + "," + i.DednCode + "," + i.DednSubCode + "," + i.EffectDate + "," + i.TermDate
            //    + "," + i.AmountStr + "," + i.LoanAmountStr + "," + i.InterestAmountStr + "," + i.OtherChargesStr
            //    + "," + i.BillingType + "," + i.UpdateCode
            //    );
            //writer.WriteLine(string.Format("\"{0}\",\"{1}\",\"{2}\",\"{3}\"", i.Region, i.Division, i.Station, i.EmployeeNumber));

            writer.WriteLine(GetCSV(i));

        }
    }

    FileStream fs = null;
    //fs = File.Open(Server.MapPath("TextFiles/" + sFileName + ".txt"), FileMode.Open);
    fs = File.Open(Serverpath + sFileName + ".csv", FileMode.Open);
    byte[] fbyte = new byte[fs.Length];
    fs.Read(fbyte, 0, Convert.ToInt32(fs.Length));
    fs.Close();

    Response.AddHeader("Content-disposition", "attachment;filename=" + fileName);
    Response.ContentType = "application/octet-stream";
    Response.BinaryWrite(fbyte);
    Response.End();

}

private string GetCSV(TransmittedData i)
{
    string[] list = new string[]
    {
        i.Region.ToString(),
        i.Division.ToString(),
        i.Station.ToString(),
        i.EmployeeNumber.ToString(),
        i.FirstName,
        i.MiddleInitial,
        i.LastName,
        i.Appelation.ToString(),
        i.DednCode.ToString(),
        i.DednSubCode.ToString(),
        i.EffectDate.ToString(),
        i.TermDate.ToString(),
        i.AmountStr,
        i.LoanAmountStr,
        i.InterestAmountStr,
        i.OtherChargesStr,
        i.BillingType.ToString(),
        i.UpdateCode.ToString()
    };

    string csvLine = "\"" + string.Join(@"", "", list) + "\"";
    return csvLine;
}

the values that youre writing, are they integer types that youre trying to write? — Simon Price, Dec 05 '16 at 06:14
just out of curiosity, how come your numbers are string type and not int? — Simon Price, Dec 05 '16 at 06:18
@SimonPrice because the given numbers from the report table has leading zeros — Angelo11292, Dec 05 '16 at 06:32

score 4 · Accepted Answer · edited May 23 '17 at 12:08

4

you need to add another single quote at the end of each field for example

   writer.WriteLine("=\"" + i.Region.ToString() + "\",=\"" + i.Division.ToString() + "\",=\""

you can use string.Format and give the fields using parameters, example

writer.WriteLine(string.Format("=\"{0}\",=\"{1}\"", i.Region,i.Division));

Note that many alternatives available to convert generic list to CSV rather than doing it by your own code.

How can I convert a list of objects to csv?

Fastest way to convert a list of objects to csv with each object values in a new line

edited May 23 '17 at 12:08

Community

1
1

answered Dec 05 '16 at 06:18

Damith

62,401
13
102
153

Tried your solution but still gives me the same problem https://postimg.org/image/5afizimnf/ check link thanks – Angelo11292 Dec 05 '16 at 06:31
tried but leading zeros not showing, updated my code. thanks – Angelo11292 Dec 05 '16 at 06:49
in your input strings is there leading zeros ? – Damith Dec 05 '16 at 06:53
use `=\"` before the value and at end add only `\"`, check my updated code – Damith Dec 05 '16 at 07:11
@Angelo11292 Finally! :) I'm glad it helped you. – Damith Dec 05 '16 at 07:14

Kai Thoma · Answer 2 · 2016-12-05T07:16:14.520

Regarding https://en.wikipedia.org/wiki/Comma-separated_values#Standardization you should put all your values inside double quotes or do not use quotes at all.

        foreach( var i in TransmittedDataBLL.Instance.GetDepEdTextFile( data ) )
        {
            writer.WriteLine( GetCSV( i ) );
        }
    }

    protected string GetCSV( TransmittedData i )
    {
        string[] list = new string[]
        {
            i.Region.ToString(),
            i.Division.ToString(),
            i.Station.ToString(),
            i.EmployeeNumber.ToString(),
            i.FirstName,
            i.MiddleInitial,
            i.LastName,
            i.Appelation.ToString(),
            i.DednCode.ToString(),
            i.DednSubCode.ToString(),
            i.EffectDate.ToString(),
            i.TermDate.ToString(),
            i.AmountStr,
            i.LoanAmountStr,
            i.InterestAmountStr,
            i.OtherChargesStr,
            i.BillingType.ToString(),
            i.UpdateCode.ToString()
        };

        string csvLine = "\"" + string.Join( "\",\"", list ) + "\"";
        return csvLine;
    }

If all your values are strings (like you mentioned above) you can omit the .ToString() for better readibillity.

If you want leading zeros to be shown you can use string.PadLeft() on the corresponding line:

i.Region.ToString().PadLeft( 8, '0' ) //make region to a total length of 8 and fills up with zeroes on the left side

in case some of your values are not strings but integers you can simply use .ToString( "D8" )

tried your solution sir but gives error https://postimg.org/image/pvquwas59/ check link thanks — Angelo11292, Dec 05 '16 at 06:56
got error Error 8 Argument 1: cannot convert from 'string' to 'System.IFormatProvider' — Angelo11292, Dec 05 '16 at 07:04

score 1 · Answer 3 · answered Jun 06 '19 at 16:10

1

Putting a tab space before solves the issue:

"\t" + field

answered Jun 06 '19 at 16:10

Hunter Harrison

11
1

C# writeline properly show leading zeros save as csv

3 Answers3