How to import external csv/text file WITHOUT table formatting

I am using Excel that comes with Office 365. And this version of Excel does something annoying that's breaking all my macros.

I want to import a CSV file into a sheet WITHOUT any table formatting and such. This was the default behaviour in older versions of excel. But now that the table formatting is there, it's destroying all the macros that I have programmed.

The end result I want is shown in this picture:

But that's not what I'm getting. Here's what I did.

I first went to Get Data>From File>From Text/CSV as shown in this picture:

I select the file I want then I see a pop up window and I press the Load button as shown in this picture:

But the result I get is this green formatted table as shown in the picture:

This green formatted table is breaking my macros. I want to import the csv as a plain spreadsheet with no formatting, no drop down header functionality etc...I want just a normal basic spreadsheet. How do I get Excel to do this?

3 Answers

You can certainly go back to the previous method of importing.

• GoTo: File-->Options-->Data:

• Select the Text legacy import wizard.

Then, when you want to do the import

• Data-->Get&Transform-->Get Data
• You will see the Legacy Wizard in the drop down

I'm not sure if this fits your workflow, but instead of importing you can open the .csv file in Excel:

• File ➡ Open (or press CTRL+O).
• Make sure All Files or Text Files is selected in the file type dropdown.
• Select the .csv file and press Open.

Now your .csv file should be opened as a regular range (without table formatting).

This is a partial answer as I am not sure if you can avoid the formatting when importing the data. But after importing it, it is possible to remove the formatting.

1. Select the entire table with Ctrl+A
2. On the Table Design tab, click on Convert to Range
3. Then on the Home tab, click on Clear -> Clear Formats