Opening a CSV in Excel to fix formatting
Excel silently reformats dates and numbers when opening a CSV, which corrupts import files.
On this page:
The problem
Opening a CSV directly in Excel can change how dates and other values are stored, producing an import file that no longer matches what you exported. Importing it back then propagates the corruption. This mostly affects non-U.S. locales: Insightly exports dates as month/day/year, and Excel can silently misread them. For example, reading 05/03/2015 as March 5th, or treating 8/21/2015 as invalid text since there’s no 21st month.
The fix
- Save the exported file to your computer rather than opening it directly in Excel.
- Open Excel, then use the Text Import Wizard (usually under the Data menu or Get External Data) instead of double-clicking the file.
- In the wizard, set each date column’s format explicitly rather than leaving it on Excel’s default guess. Use General for standard text fields, and Date to choose the correct format for date columns.
This adds a few extra steps compared to just opening the file, but it’s far fewer steps than cleaning up misformatted data row by row afterward.