Formatting data for .csv imports form Excel

Megan Bennett - Wednesday, January 18, 2012

If you have ever had the challenge of preparing data for importing into an online database of any shape, no doubt you most likely have come across a few issues with import compatibility. Some of these problems may be related to how the file is prepared if your are using Microsoft Excel.

This formatting routine below may help you remove hidden characters and formatting that can plague your precious data import even if you are importing to a .csv file.


  • Start a new Microsoft Excel spreadsheet
  • Type CTRL+A , then right click and choose  Format Cells. Choose Format to Text
  • Next, from the main menu, choose Data, select Get External Data From Text
  • Browse to/open the  .csv file you need to work with. A window will pop displaying the data.
  • Change Fixed Width to Delimited then click Next
  • Uncheck Tab and check Comma then click Next
  • In Data Preview screen, select the first column and hold down the SHIFT key, scroll horizontally and  click on the last column. This will select all columms of data in the file.
  • Select the radio button Text. Click Finish.
  • Choose the cell to start your data import, choose A1 - the first cell on the spreadsheet.
Back to blog list