How to import external csv/text file WITHOUT table formatting

Question

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?

Answer 1

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

Answer 2

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).

Answer 3

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

Answer 4

After crying at every version change of Excel, I adopted this trick to open a .CSV: I rename it to .TXT and the I open it with Excel that does not recognize it and so it open the old import wizard. In some cases can happen that Excel would not open the old import wizard, well, when this occurs I force it by using "text to columns" function.

Surely not the best solution, but with this trick I do not care of the Excel version in use, this method will always function. I also explained this to some "not expert user" and after understanding this trick they had no more problems in importing theirs .CSV files.

just my 2 cents.

Answer 5

In Excel 365, after selecting 'Data'>'From Text/CSV' option, and specifying the CSV file you want to load, when presented with the data Preview screen, be sure to specify the "Data Type Detection" dropdown as "Do not detect data types". This will ensure that excel does not try to interpret the data types in each column (it will leave the data exactly as it appears in your csv file, e.g. leading zeros retained etc). So even though the cells are all formatted as "General", this solution will output the data exactly as it appears in the text file. No messing with renaming file extensions, pasting data, or reformatting columns.

In terms of the power query loading the data into the spreadsheet directly (rather than into an Excel table), a previous answer on this thread to use the "Table Design">"Convert to Range" menu would work just fine (and you only need to select a single cell in the table, not necessarily the entire table to achieve this).