1

How to know the .csv field delimiter for the data copied to the clipboard by Excel?

Is there a way to know what is the standard field delimiter that an export should use? Please note, that like for me (Canada-French), the double value separator is comma "," and the field delimiter seems to be semicolon ";".

So how to know what Excel will use as field delimiter to export its data to the clipboard?

Eric Ouellet
  • 10,996
  • 11
  • 84
  • 119

4 Answers4

2

The default is tab. If you copy data from Excel and paste it into a text editor tabs separate the cells.

ServerS
  • 452
  • 3
  • 15
  • 1
    I would figure the question is about programmatically determining the separation character from a saved csv file, not manually copying cells from Excel. – gunr2171 Mar 11 '21 at 20:12
  • 1
    @gunr2171 Yes could be, I didn't think of that. But he said clipboard, so I'm not sure it's programmatic. – ServerS Mar 11 '21 at 20:13
2

If you right click on your excel/csv file and open with Notepad, you will see what is the type of delimiter set in your configuration.

Omur
  • 136
  • 1
  • 7
1

Saved-to-disk csv files don't have any metadata storage, so you don't have a bullet-proof way of determining if a csv file is delimited by a comma, a semicolon, a pipe, the word "something", or any other character.

There is a standard for how csv files are created, but, uh, well, you know how that goes...

Microsoft decides to do their own thing, changing the character based on "Regional and Language Options". If your program is running on the same box as the Excel app that made the file, you might be able to programmatically determine that.

I'd say your best bet to infer the separation character is by looking at the first line - the headers. HOPEFULLY you don't have a comma in your header fields, and you can have a whitelist of expected separation characters. When you look at that header line, if you see a comma, you know the whole file is separated by commas, or semicolons if you see a semicolon.

This, of course, is not perfect. If you have any control over making Excel consistently printing csv's with the same separator character, that's always going to be a much better solution. Or, when importing a file, ask the user which format the file is in (comma csv vs semicolon csv, etc).

gunr2171
  • 16,104
  • 25
  • 61
  • 88
1

After some investigation, I think there is no way to make sure what Excel will use as field delimiter into the clipboard for the "CSV" format. Also, if there is a way, then it would depends on the version of excel on the PC (if there is only one) and also could only be by user.

My needs was to ensure a consistant way to paste from excel and decided to go with the "Text" format instead of the "CSV" format. Just to make sure everyone understand what I mean: When you "cut" or copy" in Excel, there is many formats (type) the data is put into the clipboard.

To know the available formats:

    IDataObject dataObj = System.Windows.Clipboard.GetDataObject();

    if (dataObj != null)
    {
        string[] formats = dataObj.GetFormats();

Using the "Text" format (formats.Contains(DataFormats.Text)) ensure the use of tab ("\t") field delimiter and carriage return + line feed ("\r\n") as line delimiter. The format is fixed and never change. It is consistent all the time.

Thanks to everyone who give answer. They are all good but I wanted to add some additional information.

Eric Ouellet
  • 10,996
  • 11
  • 84
  • 119