LinqToCSV format column as Text

Question

I'm using LinqToCSV to output a List to CSV. Snippet of sample code is as follows:

class Person
{
    [CsvColumn(Name = "Name", FieldIndex = 1)]
    public string UserName { get; set; }
    [CsvColumn(Name = "Address 1", FieldIndex = 2)]
    public string Address1 { get; set; }
    [CsvColumn(Name = "Telephone", FieldIndex = 3)]
    public string Telephone { get; set; }
}


private void OutputToCSV(string filenamePrefix, List<Person> people)
{
    CsvFileDescription outputFileDescription = new CsvFileDescription
    {
        SeparatorChar = ','
        FirstLineHasColumnNames = true, 
        FileCultureName = "en-GB"
    };

    CsvContext cc = new CsvContext();
    cc.Write(
        people,
        @"C:\temp\people.csv",
        outputFileDescription);            
}

The issue I have is with the telephone number. If it is in the object as 0123456789012 then when I open the CSV in Excel it is seen as a number and the leading zero is removed. I'd like to pre format the column in the CSV as text.

According to Stop Excel from automatically converting certain text values to dates I can start the field with an equals and put the value in quotes, but is there an attribute I can set which will mean that LinqToCSV will do this for me? I don't really want to use LinqToCSV, then open the file and edit it to get it how I want.

Instead of CSV, use EPPlus to create a *real* Excel file. CSV doesn't have any formatting information so there's no way to tell whether a field is text or a number — Panagiotis Kanavos, Nov 06 '15 at 14:19
Panagiotis: This looks like a good alternative and a great way to create Excel files, but not CSV files. — Karl Gjertsen, Nov 06 '15 at 14:27

score 1 · Accepted Answer · answered Nov 06 '15 at 14:20

1

Is your intent to create a CSV or an Excel file? If the latter, then wouldn't it be much easier if you have instead used Epplus from Nuget? ie:

void Main()
{
  ExcelPackage pck = new ExcelPackage();
  List<Person> people = new List<Person> {
    new Person { UserName="Cetin", Address1="A1", Telephone="012345"},
    new Person { UserName="Timbo", Address1="A2", Telephone="023456"},
    new Person { UserName="StackO", Address1="A3", Telephone="0 345 6789 01 23"},
  };


  var wsEnum = pck.Workbook.Worksheets.Add("MyPeople");

  //Load the collection starting from cell A1...
  wsEnum.Cells["A1"].LoadFromCollection(people, true, TableStyles.Medium9);
  wsEnum.Cells[wsEnum.Dimension.Address].AutoFitColumns();
  //...and save
  var fi = new FileInfo(@"d:\temp\People.xlsx");
  if (fi.Exists)
  {
    fi.Delete();
  }
  pck.SaveAs(fi);
}

class Person
{
  public string UserName { get; set; }
  public string Address1 { get; set; }
  public string Telephone { get; set; }
}

answered Nov 06 '15 at 14:20

Cetin Basoz

22,495
3
31
39

The question is for CSV files, not Excel files. CSV files are often opened in Excel, but also used to import into other systems. – Karl Gjertsen Nov 06 '15 at 14:27
1

Read what I say at top. CSV means Comma Separated Value and is used in transferring data between different mediums and is not meant to be a particular for Excel - "are often opened in Excel" is an incorrect statement. It is often for only those who use Excel often. – Cetin Basoz Nov 06 '15 at 14:32
I'll have a look at Epplus. As far as I'm aware there's no requirement to import the file into anything else, I think it's just used in Excel so this will do the job. – timbo Nov 06 '15 at 14:48
Oh then certainly look into it. It is simple and it creates the xlsx file, even if Excel is not installed in user's machine. – Cetin Basoz Nov 06 '15 at 14:51

score 0 · Answer 2 · answered Nov 06 '15 at 14:11

0

Try using OutputFormat to force it ToString(). You may also be able to combine it with your other fix and use OutputFormat="=C" but I have not tried that.

[CsvColumn(Name = "Telephone", FieldIndex = 3, OutputFormat = "C")]
public string Telephone { get; set; }

answered Nov 06 '15 at 14:11

Stephen Brickner

2,584
1
11
19

It is not the output format that is the problem. All CSV entries are text, but Excel reads it as a number and amends it. the number has to be changed to have a leading '='. – Karl Gjertsen Nov 06 '15 at 14:20
No, that would convert it to a formula. Excel uses a single quote (') for fields that contain numeric data that should be treated as text – Panagiotis Kanavos Nov 06 '15 at 14:25
I'd already tried putting a single quote directly into the CSV. Excel displays the quote! – timbo Nov 06 '15 at 14:43

score -1 · Answer 3 · answered Nov 06 '15 at 14:19

-1

As you say, this is an Excel issue and you need to the data to have a leading = sign, so that 0123456789012 becomes ="0123456789012".

You can use the OutputFormat to change the format, but you would need to play around with the formatting:

[CsvColumn(FieldIndex = 2, OutputFormat = "dd MMM HH:mm:ss")]

answered Nov 06 '15 at 14:19

Karl Gjertsen

4,690
8
41
64

It *isn't* an Excel issue actually, there's no way to tell that the field contains a string or number. CSV doesn't have any type information, so Excel tries to guess the field types while importing – Panagiotis Kanavos Nov 06 '15 at 14:19
All CSV files are just text. However, Excel will translate the value into a numeric, so adding the '=' sign tells Excel to treat it as a number. – Karl Gjertsen Nov 06 '15 at 14:23
No, it will convert it to a formula. Excel uses the single quote (') in these cases. Better to create a *real* Excel file though. It's just as easy and a lot safer – Panagiotis Kanavos Nov 06 '15 at 14:27
We don't know if an Excel file is needed, the question says CSV, which is being opened in Excel. We don't know if the file is to be used elsewhere as a CSV. – Karl Gjertsen Nov 06 '15 at 14:29
1

"Elsewhere" would likely be a database where it wouldn't be a problem. – Cetin Basoz Nov 06 '15 at 14:34
2

Any field prefix makes the CSV inappropriate for use by any other application. You couldn't import such a file in a database for example, without first stripping the prefix. So a prefix pretty much requires Excel - or another application that treats prefixes in the same way – Panagiotis Kanavos Nov 06 '15 at 14:38
Ha ha..Good point! Then this is only a problem if it is to be imported into Excel. If so, Cetin's answer would be the best solution. I'm glad I am aware of EPPlus now. – Karl Gjertsen Nov 06 '15 at 14:40
Why the downvote? This isn't wrong if the import is for Excel. – Karl Gjertsen Nov 06 '15 at 14:42

LinqToCSV format column as Text

3 Answers3