0

So I have a script in PHP that creates tab separated CSV output.

I have a button in my HTML that works like so:

<a href="https://www.domain.co.uk/api.php?mode=csv" target="_blank" download="export.csv">Export Data</a>

Ideally I want the user to open this CSV file in Excel.

The issue I have here is with tab separated CSVs, the file extension, and how Excel handles all of this. For example:

download="export.csv"

Results in the Browser asking me to open this in Excel (wanted behaviour), but then once in Excel none of the columns are respected as they are tab separated (not comma separated, which Excel is obviously expecting).

download="export.xls"

Results in the Browser asking me to open this in Excel (again, wanted behaviour), but then Excel complains that the file extension and the contents do not match and gives the user a warning. If the user goes past this warning the data displays as expected, but I could do without the warning.

download="export.txt"

Results in the Browser downloading the file as a text file. Once imported into Excel, the columns are respected, but I could do with this being thought of as an Excel file like CSV files are.

download="export.tsv"

Results in the Browser downloading the file, but as this extension isnt recognized, it will need to be imported into Excel manually, which isn't what I am after. Infact, even though TSV is the most correct file extension for tab separated verse, the TXT extension seems to work more smoothly.

I am unable to set file associations on the end users machine, and I would like to avoid going down the "export your data as an actual XLXS file" route if at all possible. I would prefer to use tab separated CSVs over comma separated CSVs because the exported data contains lots of commas naturally.

EDIT:

So as per Ron Rosenfeld suggested I tried outputting a comma separated CSV file with quotes around the data - and the file loads into Excel, with columns preserved - however the quotes appear on every piece of data in every column that uses quotes.

Is it possible to not have the quotes appear?

Ideally I would prefer to have the content tab separated, but at this stage anything that allows me to open a CSV file from a browser into Excel would be great.

I want a way to download a tab separated CSV file from a browser to Excel with as little fuss as possible. How can this be achieved?

Jimmery
  • 9,783
  • 25
  • 83
  • 157
  • What about using CSV, but enclosing the fields that contain commas within quotes? – Ron Rosenfeld Aug 10 '19 at 10:37
  • @RonRosenfeld thanks for the suggestion - please see the edit in my post – Jimmery Aug 13 '19 at 13:38
  • In the actual CSV file are the double quotes doubled? Or are they just a single instance of a double quote? – Ron Rosenfeld Aug 13 '19 at 13:41
  • a single instance - eg: `"name", "address", "etc"` – Jimmery Aug 13 '19 at 13:43
  • That's odd. Try to just enclose the fields that have included commas in the double quotes. – Ron Rosenfeld Aug 13 '19 at 13:44
  • But those quotes will still appear in Excel - and they will still need to be removed - is there anyway to open a CSV in Excel without any of these issues? – Jimmery Aug 13 '19 at 14:07
  • I'll check when I get back to my computer. The double coat marks should have been suppressed unless they are doubled up in the actual CSV file (not the one you open in Excel) – Ron Rosenfeld Aug 13 '19 at 14:09
  • When I save a CSV file from Excel, where some of the fields contain commas, those fields ONLY are surrounded by quotes in the actual CSV file (opened with a text editor; NOT with Excel). That file can subsequently be opened in Excel and displays without the quotes. I suspect your csv file construction is not occurring in accord with these rules. But I see you found a way to use the TSV files instead. – Ron Rosenfeld Aug 13 '19 at 20:46

3 Answers3

4

The difference between the CSV and TSV files are - as long as the creator followed some rules, that: CSV file will have comma separated values and a TSV file will have tab separated values.

For TXT files, there is no formatting specified.

CSV files are comma-delimited, so you have to use this:

sep=,

And TSV files are tab-delimited, so you have to use this:

sep=\t

If you have MS Excel installed on your computer, CSV files are closely associated with Excel.

Please, look at this post to find out what the use of sep=; for UTF-8 and UTF-16LE leads to.

It's very important to properly output UTF-8 and UTF-16LE CSV files in PHP.

So THIS POST will be informative and useful for you.

Community
  • 1
  • 1
Andy Jazz
  • 49,178
  • 17
  • 136
  • 220
2

CSV means "comma separated values", so the default separator is a ,.

To change that separator to a tab, put

sep=\t

as the first line in your .csv-file (yes, you can still name it .csv). That tells excel what the delimiter character should be.

Note, that if you open the .csv with an actual text editor, it should read like

sep=     (an actual tabulator character here, it's just not visible...)

This feature is not officially defined in the .csv RFC 4180, so if it works with any software other than Excel depends on that software's implementation.

Community
  • 1
  • 1
sina
  • 1,817
  • 1
  • 18
  • 42
0

I have done this before. A painful experience, which I rather not relive. but since you asked (and bountied).

  1. Make sure your http-headers read: Content-Type: application/x-www-form-urlencoded
  2. Make ; your separator
  3. Don't enclose by " (This is a magic I have yet to understand).
  4. Fingers crossed
S van Balen
  • 288
  • 2
  • 11