Convert character delimited text files to Excel format

Updated February 26, 2024 21:15

Summary

MindBridge can read delimited text files, so it is not necessary to convert them to Excel format before you import them. However, you may want to view these text-based data files in Excel, since the data is easier to view and analyze in a spreadsheet.

Learn how to convert a character delimited text file into an Excel (.xlsx) file.

Note: Excel enforces a maximum of 1,048,576 rows, and truncates rows in excess of this limit. If your data exceeds this amount, it will not be possible to convert it into a single sheet without data loss

Steps to convert a character delimited text file to an Excel workbook

Open a new instance of Microsoft Excel.
Select Open in the side menu.
Select Browse.
Navigate to the location of your saved text filed.
Select the text file file you want to convert, then Open the file.
Note: For Excel to detect text files, change the file type detection (outlined in red in the image below) to either All Files or Text Files.
You will be taken to the Text Import Wizard (displayed below).
Delimited is selected by default as the original data type. This does not need to be changed.
If your file contains a header row, select the checkbox to the left of My data has headers, otherwise leave it unselected.
Select Next to proceed to the next step.
Select the character used as the delimiter in your file. For example, select Tab for tab-delimited files.
If your file's delimiter character is not listed, select Other and enter the character in the field.
Note: You can only apply one delimiter character.
Refer to the Data preview section at the bottom of the Import Wizard for a preview of how the data will be separated.
Select Next to proceed to the final step.
This screen lets you specify the data type for each column, as well as indicate if a column should not be imported.
First, choose the column you want to action on by selecting it in the Data preview table. In the example image below, JournalNumber was selected.
Next, select the column data format you want Excel to apply to the given column:
- General (default): Allows Excel to detect data formats within the column and convert values to the corresponding type.
- Text: Converts all values to text format.
  - Note: If you want your column data to remain exactly as shown (for example, keeping all leading and trailing 0s in account numbers), select the Text option.
- Date: Converts all values to date format.
- Do not import column (skip): Excel will not import the given column.
When you are satisfied, select Finish. You will be taken to your converted file.
To save the file in Excel format, go to File, select Save As, then select Excel Workbook (.xlsx) from the menu options.
Select Save.