When we migrated to Office 365, we were migrating from Office 2016. Office 365 is like Office 2019, which DES didn’t install on client PCs and laptops. Importing text files into Excel changed substantially. The import text to Excel function doesn’t result in the same column count once accomplished by Excel 2016. It comes up short for some reason, despite selecting the same delimiter as used in Excel 2016. Below is the description of how you can import text files into Excel and get the same results as you did with Excel 2016.
Open up Excel like you’ve done in the past. You’ll see this window:
Normally, you’d select “Data” from the menu bar, look at the tool ribbon and select importing from a text or CSV file to start the conversion. However, you should avoid this because you won’t get the results you expect. Oddly, if you create a text file using a text editor and use tabs as delimiters, this function will work properly. However, if you take a rich text format (RTF) file and save it as a text file using Word, this option will not work. You’ll have to follow the alternative process described in this document.
Figure 2 – Text/CSV File Import
Instead of using the tool ribbon function to convert, you should open a text file. From the menu bar, select File, Open to see a list of files.
You don’t see any text files display because Excel defaults to listing Excel files. You must change the file type to see the text files. Click the drop down list and select “All Files (*.*)”
Now you’ll see a complete list of files including the text file you need to convert. Select a file and click Open.
When Excel sees that it’s opening a text file, it launches the Text Import Wizard we’re used to using.
When you go to Step 2, be sure to select the correct delimiter. Then click Next.
Now you’re at the final stop of the conversion. Make sure the data is separated properly into columns.
If you’re satisfied with how it all looks, click Finish. Next, you’ll see your text data in Excel
In this example, I expected to see 8 columns (A-H), and that’s what I got. Now I can proceed to do the work I need to do with this Excel file. When you save it, you’ll have to make sure you select the proper format (XSLX).