One CSV per sheet, and what to do about merged cells
A CSV holds one table, so a workbook with three sheets is three CSVs. Excel's Save As does them one at a time, and it warns you that only the current worksheet will be saved. Here every sheet is in the Sheet list, hidden ones too. Pick one, download it, pick the next. Each file is named after the workbook and the sheet, so nothing overwrites anything.
Merged cells are the other surprise. When cells are merged, only the upper-left one keeps its value, so in a CSV the rest are empty. For a report grouped by region or owner, that means rows with a blank region or owner. Check "Fill merged cells" and each cell in the range gets the value.
One Region cell merged down three rows, saved as CSV
Merged cells left as they are
region,account,mrr West,A-1001,1250 ,A-1002,890.5 ,A-1003,2400
With "Fill merged cells"
region,account,mrr West,A-1001,1250 West,A-1002,890.5 West,A-1003,2400
How each value is written
How a cell goes from the workbook to the CSV
| Stored in the file | Excel shows | In the CSV |
|---|---|---|
| 40729 | 7/5/2011 | 2011-07-05 |
| 7030 | 07030 | 07030 |
| 1250 | $1,250.00 | 1250 |
| =E2*12, saved as 15000 | $15,000.00 | 15000 |
- Dates become YYYY-MM-DD. Excel stores a date as a count of days: July 5, 2011 is 40729, and the cell's format decides how it looks. Any cell with a date format comes out as 2011-07-05, or 2011-07-05 14:30:00 when it has a time, and a time on its own comes out as 14:30:00. No import has to guess whether 7/5 means July 5 or May 7. Workbooks that start their date count in 1904 instead of 1900 are read correctly too.
- Numbers come out as stored. Currency and percent formats are dropped, so 12.5% becomes 0.125. One exception: a format made of zeros, like 00000 on a zip code, keeps the leading zeros you see in Excel. Numbers are written in full, up to the 15 digits Excel keeps, never as 1.23E+17. Excel already cut off the end of any number longer than that. The report flags those, so you can get a fresh copy from the system it came from.
- Formulas give their result. The CSV has the value saved with the file the last time it was calculated. A file made by another program that never calculated its formulas has no result to give, and the report says how many cells came out empty. Open the file in Excel, save it, and open it here again.
- Everything else stays as written. Text, TRUE and FALSE, and error values like #N/A come through as they are. A value with the separator, a quote or a line break goes in double quotes, as RFC 4180 describes, so it stays one cell.
The CSV is saved as UTF-8 text. If someone will open it in Excel, check "Add a UTF-8 marker", and Excel opens the file normally, accents and other special characters included. Leave it off if the CSV is going into another system's import, which may keep the marker as part of the first column's name.
Questions
- How can I convert an XLSX file to CSV without opening it in Excel?
- Open it on this page. The converter reads the workbook itself, so you don't need Excel installed. Files can be up to 50 MB.
- What about .xls files?
- This converter reads .xlsx, not the older Excel 97-2003 binary .xls format. Open the .xls in Excel, Google Sheets or Numbers, save it as .xlsx, and open that here. Same for a workbook with a password: save a copy without one.
When the spreadsheet came from the customer's old system
A spreadsheet a new customer exports from the system they're leaving is rarely the whole story. Across Rubie's migrations, 95% of source systems don't have a clean API or export that captures all the data needed. What the export misses sits behind a login or in a legacy app.
Rubie moves that history out of the old system and into your product, without engineering time, and cuts the migration timeline by 50-90% on average, according to Rubie. See how Migration Playbooks run a migration end to end. Going the other way? The CSV to Excel converter makes a workbook that keeps zip codes and long IDs as written.
