Learn how to convert a character delimited text file into .XLSX format.
Steps to convert a character delimited text files to an Excel workbook
- Open a new instance of Microsoft Excel.
- Select "Open" in the side menu, followed by the "Browse" option
- Navigate to the directory where your text filed is saved at; select the text file file you want to convert, then "Open" the file. For Excel to detect text files, you will need to change the file type detection outlined in red in the screenshot below to either "All Files" or "Text Files".
- You will be taken to the Text Import Wizard.
- If your file has a header row, check the radio button "My data has headers", otherwise leave the button unselected. Leave the radio button for the "Original data type" as "Delimited".
- Select "Next" to proceed to step 2 of 3.
Select the character used as the delimiter, for example, Tab for tab-delimited files. For files delimited by a character not listed, type the character in the "Other:" option. For a preview of how your data will be separated refer to the "Data preview" section in the bottom portion of the Import Wizard.
- Select "Next" to proceed to step 3 of 3.
- This step lets you specify the data type for each column, as well as indicate if a column should not be imported ("Do not import column (Skip)"). The default is "General", which will allow Excel to guess the format and convert it to the corresponding type. For any columns you would like to keep the data exactly as shown, such as keeping all leading and trailing 0s in account numbers, select "Text" as the "Column data format".
Hit the "Finish" button, and you will be taken to your file converted to Excel. To save the file in Excel format, go to "File" then choose Save As, go to the dropdown menu under File Format, and select Excel Workbook (.xlsx)
Anything else on your mind?