CSV File Not Retaining Formatting

jeffvetter · 2024-02-15T20:30:01.000Z

We have an XLS file that is generated with a column for date, name and dollar amount. We have to convert it to a CSV file to upload to a banking site. The issue is the dollar amount column is displayed as 379.1 instead of 379.10 and 436 instead of 436.00. We can change the formatting of the column in the CSV file to include the decimal places but saving it does not keep the formatting. Is this caused by converting it from XLS to CSV or something else?

Jrx1216 · 2024-02-15T20:54:31.000Z

Yeah, CSVs don’t have any formatting, it’s plain text with the columns separated by commas and rows separated by newlines. If you open a CSV in notepad, it’ll open just like a txt file

jeffvetter · 2024-02-15T20:59:23.000Z

OK, that’s what I was thinking but I didn’t know if there was a way to save the formatting.

GeorgeSVFC · 2024-02-15T23:14:29.000Z

Does the Excel data genuinely have zeros? You can make it appear to have them in Excel with decimal place or Currency formatting. Click on an Excel field, then check the content field above (where you can edit an equation). If there zeros aren’t up there, they won’t appear in a CSV.

general-tsao · 2024-02-16T12:18:05.000Z

Does the generated data have the correct formatting? You might need to start there.

egp_dave · 2024-02-16T12:41:56.000Z

Look at the CSV file in a text editor (Notepad++ or BBEdit) and you will likely find the zeros in place. The importing app has to deal with that. In Excel, you can format cells. A banking database should (hopefully?) handle currency with two decimal places.

craigrrr · 2024-02-16T13:58:27.000Z

For best handling of numbers for use in csv’s, pass all of the data as string/text data types, not numeric data types. That should keep leading/trailing zeros intact. And if at all possible, SKIP THE XLS FILE STEP. Excel is what will misinterpret your datatypes and it is almost impossible to handle all of the cases where it will corrupt numbers into other number formatting, in particular taking long integers and slamming them into scientific notation is one of the worst hassles, along with leading/trailing zeros handling. Write the data as csv files first, those will open ok in excel as-is, even if excel butchers the data display, and the csv will have the data as passed into it without excel having a chance to do its horrible data type assumptions.

AdmiralKirk · 2024-02-16T18:00:45.000Z

CraiGrrr:

For best handling of numbers for use in csv’s, pass all of the data as string/text data types, not numeric data types. That should keep leading/trailing zeros intact. And if at all possible, SKIP THE XLS FILE STEP. Excel is what will misinterpret your datatypes and it is almost impossible to handle all of the cases where it will corrupt numbers into other number formatting, in particular taking long integers and slamming them into scientific notation is one of the worst hassles, along with leading/trailing zeros handling. Write the data as csv files first, those will open ok in excel as-is, even if excel butchers the data display, and the csv will have the data as passed into it without excel having a chance to do its horrible data type assumptions.

I can’t agree more. Skip Excel if at all possible. Whenever I work with machine-generated data that is going to be transmitted to another machine, I use a text editor (Notepad++ usually) as my viewer and never touch Excel. At a previous job I even associated .csv files with Notepad++ and refused Excel’s offers to take over that association.