Sometimes I find the “old” tools work better. They aren’t weighted down with extra bells and whistles. A good example of this is the Text Import Wizard. When you have a simple CSV file, it can be faster than Get & Transform Data. The issue is Microsoft has buried the feature. In this tutorial, I’ll show how to surface it.
In an earlier tutorial, I showed how you could use ChatGPT to produce a CSV file with various data elements for the US states. While ChatGPT did a good job, I didn’t notice the extra comma which would force the year into the STATE ANIMAL column. You can see this in VS Code below.
This was an easy fix. I just asked ChatGPT to generate a new file with the dates wrapped in quotation marks.
Opening the CSV File in Excel
I figured with my new file, it would be a snap to pull it into Excel. At first, I right-clicked the file and chose “Open with Excel“. I then used the Text to Columns wizard. All looked good until I widened the columns. For some reason, about a half dozen states insisted on having a custom date format.
I stared at the source file trying to see if I could spot anything. I even submitted the file to ChatGPT and asked if it could spot anything. No luck.
Sometimes you just go to Plan B.
Unhiding the Text Import Wizard
The Text Import Wizard is great for simple files such as this CSV. I also know it allowed me to set the column format. However, Microsoft hid the feature when they introduced the Get & Transform Data tools.
To enable the wizard,
- Open Microsoft Excel.
- Click File.
- From the left navigation, select More… from the bottom.
- From the pop-up menu select Options.
- The Excel Options dialog box opens.
- Click Data from the left menu.
- In the Show legacy data import wizards section, check From Text (Legacy).
- Click OK.
Now, when you go to Get Data, you’ll have a new menu option – Legacy Wizards.
Once I’m in the wizard, I can change the problem column’s data format from General to Text.
Get & Transform Data
Some folks might be wondering why I didn’t use Get Data and the From TXT/CSV option. Technically, I could but it had several drawbacks.
- It doesn’t use the First Row as Headers. You have to tell it to use it.
- Even though it says the column is Data Type Text, it comes in as General.
- The interface is more than I need for a simple task. I didn’t need Queries and Connections.
To be fair, the data did look fine when it came in as an Excel Table. But when I added a column to use Flash Fill to get a short date, it failed.
If I tried to set the data format to Short Date on Column C, the numbers would be converted back to a date. However, the alignment was off. The end result was more steps than using the legacy Text Import Wizard.