1

I'm outputting a tab-delimited file from my webapp that should be opened in Excel. The problem is that .xls seems not good for opening and editing it, then Excel required some other format, and if I change the extension to .tsv then the file becomes unknown for Excel (on Windows 7) and .csv is for comma-separated. Can you advice me which the file extension should be?

This is the code that outputs the file and it works. It's just that I should choose the most suitable extension for tab-separated values. 

@RequestMapping(value = "/export", method = RequestMethod.GET)
@ResponseBody
public ModelAndView export(HttpServletResponse response) {
    try {
        String str = "";
        Iterator<Individual> iterator = customerAccountService.getAllIndividuals().iterator();
        while(iterator.hasNext()){
            Individual individual = iterator.next();
            str = str + individual.getId() + "\t" +individual.getIndividualName().getName() + "\t" + individual.getAddress().getStreetName() + "\n";
        }
        InputStream is = new ByteArrayInputStream(str.getBytes());
        IOUtils.copy(is, response.getOutputStream());
        response.setContentType("application/xls");
        response.setHeader("Content-Disposition","attachment; filename=export.tsv");
        response.flushBuffer();
    } catch (IOException ex) {
        //logger.info("Error writing file to output stream. Filename was '" + fileName + "'");
        throw new RuntimeException("IOError writing file to output stream");
    }
    ModelAndView modelAndView = new ModelAndView(ViewName.MENU);
    modelAndView.addObject(ObjectName.ADD_FORM, new LoginForm());
    return modelAndView;
}
Niklas Rosencrantz
  • 25,640
  • 75
  • 229
  • 424

1 Answers1

8

Put

sep=\t

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

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

sep=     (an actual tabulator character here, it's just not visible...)
sina
  • 1,817
  • 1
  • 18
  • 42
  • Your answer here solved the problem I had here: https://stackoverflow.com/questions/57429513/downloading-csv-data-into-excel-from-a-browser/57480001 - drop an answer down on my question and I will award you the bounty. – Jimmery Aug 13 '19 at 14:36
  • This is the correct answer. I added this line to the start of my tab delimited file (downloaded using wget in a bash script) and simply ran 'open' and excel understood it was a TDF and displayed the data correctly. +1 – touson Apr 30 '20 at 07:42
  • @touson Glad I could help! Please be aware that this is not a standardized feature of CSV, so if you use any tool other than Microsoft Excel, it might not work. – sina May 04 '20 at 09:09