Formatting problems with csv file

Question

Ok, I've checked this SO question, but it has not helped me at all.

I need to get a csv file to a user, so they can export it into PeopleSoft. One of the fields (batch_id) has to be 4 digits exactly, and currently Excel keeps dropping those leading zeroes. I've also opened the csv file in TextPad and verified that those zeroes are gone.

So heres the process:

Browser is pointed to myapp/Batch/course as shown below:

    @GET
    @Path( "Batch/{course}" )
    @Produces( "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" )
    @HttpHeader( values="Content-Disposition=attachment;filename=Batch.csv" ) 
    public String getNextBatch ( @PathParam( "course" ) String course )
    {
      ...
    }

Within that method, an Oracle database is queried and gets data sent back as XML (In the XML, batch_id has the correct number of digits). That XML is then processed into one long comma separated String via StringBuffer.append(), which the method then returns. The @Produces attribute takes that String and makes sure Excel handles it, and the file is downloaded.

I can get it to appear as 4 digits by appending tabs after each comma, but then it bombs when its loaded into PeopleSoft. I can also get it to appear with the correct 4 digits if I single quote it (either a single leading quote, or quoting the entire batch_id), but again theres the problem of the PeopleSoft upload.

More info: I'm appending the data as such (but in a loop):

    NodeList participants = XmlUtil.selectNodeList( doc, "Batch/row" );
    buffer.append( "\t" + item.getAttribute( "batchid" ) );

Does anyone have any idea how to get Excel to behave correctly?

Edit: I marked a solution, because in any other case it would work. Trying to spit out the data to Excel like I am now is just a kludge, so I'm just going to rewrite it a different way.

score 0 · Answer 1 · answered Apr 25 '12 at 01:19

0

Save the file locally, and then open it with TextPad directly, the leading zeroes will be there.

Do not open the CSV file in Excel. Excel will reformat it and strip the leading zeros.

Try it yourself, make a simple CSV file by hand in TextPad, save it, and then open it in Excel. The leading zeroes will be stripped.

If you need it to open properly in Excel, then use a format designed for Excel. Have the user download an XLS file or XLSX file. Libraries like Apache POI http://poi.apache.org/spreadsheet/quick-guide.html are a good place to start.

answered Apr 25 '12 at 01:19

anon

1

In this case, those leading zeroes won't be there, I've checked. If I change the method header to text/plain and change the file extension to .txt then the zeroes are there, no matter how many times I change the extension back and forth. This was supposed to be just a quick and dirty solution to get the data to Excel, but it looks like maybe that isn't going to work. – Corwin01 Apr 25 '12 at 12:45

score 0 · Accepted Answer · answered Apr 25 '12 at 01:31

You can open that file in excel without losing the zeroes.

Do as follows:

1 - Open Excel; 2 - Click - File->Import and Select the CSV File you want to view in Excel; 3 - Select Delimited and Click Next; 4 - Select Comma as Delimiter and click Next; 5 - Mark Column Data as Text at the column you don't want to lose the zeroes on the left; 6 - Finish.

Formatting problems with csv file

2 Answers2