How to Import CSV Files into Excel Without Breaking Them!


Have you ever had this problem? You've got some data that's been exported from a CRM system, or your ERP, or emailed to you by a data provider or colleague. Your data should look something like this, with phone numbers all showing correctly whether they have spaces in the numbers or not:

But what they actually look like is this:

Everything is crooked, reality is poison, I WANT TO GO BACK!

The problem is, repairing a file after it has been damaged in this way is extremely laborious, if not impossible depending on it's size and how many number fields have been affected. A quick check into the the original CSV file shows that the numbers are in fact correct:

A lot of the time, the reason for this is the way that Excel treats numeric fields by default. If the file is saved in this state, ALL of the prefixed 0's will be gone forever. Simply hitting save right now would save back to the original CSV format, and looking at the file again we can see:

That the very fabric of the universe has been torn asunder. Also nobody will ever hear from Bert or Harry again.

So here's what we need to do to ensure this never happens again, and you can tell Timmy in accounts that he needs to do this, too, and save everyone a great deal of stress.


Correctly Importing CSV Files Into Excel

1) Firstly, we need to hijack Excel's import process. If you just double-click on the CSV file, Excel will just try and open it and make some assumptions on field types based on the kind of data it can see. What we need to do, is jump ion before those assumptions are made.

Head to the "Data" tab, and expand the "Get Data" dropdown, head through "From File" and finally select "From Text/CSV".


2) Find the CSV file you are trying to open, select it, and click on "Import".

3) The file import window will open and show the contents of your CSV file, as well as some options for data detection. From here you could make changes to the way that Excel is detecting the field types in your document, but right now we don't actually need to, we just need to select "Load".

NOTE: We don't need to do this in this example, but if you did select "Edit" from this window you would be taken the Query Editor, which will allow you to manipulate the data before it is imported into Excel. However, the majority of the time you shouldn't actually need to do this. This tools are considerably more powerful than Excel's older legacy import tools, but they are also quite complex. I won't be going through all the options and capabilities here, but just know that you can, and if you ever need to, the ability to re-form data during the import phase does exist. For now, let's not click Edit, and move on to the next step.


4) So, the data has been loaded, and look at that, we have all of our numbers present and correct. Which leads to the question, if we didn't actually do anything to the data and we just clicked on Load, how did it work when simply double-clicking on the file resulted in an incorrect import?

The answer is: It is the magic of the dance.

In the background, double-clicking on the file isn't actually sending Excel through the same import process, and instead is still using Excel's legacy import methods.

But what if you liked the older import methods? If you were familiar with the way older versions of Excel used to do things and want to go back? Well, you can.


Re-Enabling Excel's Legacy Import Modes

1) Click on "File" and then "Options".


2) From the Options Screen, go to the "Data" tab and place a tick in the "From Text (Legacy)" check box from the "Show legacy data import wizards" section. You can then click "OK" to close the Options Dialog.


We're only interested in this option for now, but there are other legacy import wizards in here that you may want to also enable, or simply remember for later, in case you need them.


3) Now, going through a similar route to the original method, you can head to the "Data" tab, then expand the "Get Data" dropdown. Only now, there is a new "Legacy Wizards" option, from which you can now select "From Text (Legacy)".


4) A slightly familiar window should now pop up. This is the older import wizard from previous versions of Excel.

From here you can choose "Delimited" to indicate that your file has column separation designated by some sort of special character (in this instance, in our comma-separated-values file, it is a comma) and click on "Next".


5) From the next screen, tick "Comma" from the list of delimiters and then click on "Next".


6) From the final part of the wizard, we need to make sure Excel is formatting the phone numbers column as "Text" rather than "Number". So select the column and then choose "Text" from the "Column data format" options.


Done! Our CSV is now imported using the legacy import wizard and all of the numbers are present and accounted for. Not quite as fancy as the new import wizard, but if you're familiar with the process and would rather keep using a tool you are comfortable with, then have at it!


If you found this guide helpful or have any questions and want to engage with Binary Blue, you can find us on Twitter and Facebook!

Tagged under