Questions? We have answers.

Converting Text Files (delimited or fixed width) to Excel

  • Updated

Summary

Although Microsoft Excel files are easier to view and analyze, your client will often export GL data into a text file, whether CSV, delimited, or fixed width. In fact, MindBridge prefers CSV or delimited text files, especially when there are more rows than the 1048576 that Excel supports.

Note: CSV files are a special case of delimited text files, with, (comma) being the delimiter.

It is important to note that MindBridge reads these delimited text files very well, and it is not at all necessary to convert them to Excel format for ingestion. It is not possible to convert to an ingestible Excel file if there are more than 1048576 rows.

However, we realize that you might want to view your files in Excel for your own analyses outside of MindBridge, and for this reason, we provide instructions here on how to convert a delimited or fixed-width text file to Excel.


Resolution

Open Excel and choose to open a document directly from Excel (by hitting Ctrl-O on PC or ⌘O on Mac). You might need to select to display text files or all files *.*

Delimited Text Files

  1. You will be taken to the Text Import Wizard. If your data is delimited, make sure the Delimited radio button is selected (see the next section for instructions for fixed-width format text files): 
  2. Next, select the character used as a delimiter, for example, Tab for tab-delimited files. For files delimited by a character not listed, type the character in the box in the Other: option 
  3. The next screen lets you specify the data type of 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.  
  4. However, if you would like to keep the text exactly as shown, such as keeping all leading and trailing 0s in account numbers, select Text for the column, because if an account number contains only numbers and "." (period/decimal character), Excel will interpret it as a number and remove leading 0s as well as 0s at the end if there is a "." in the account number. For example, 0439.120 would be converted to 439.12 unless the column is converted to Text format.
    • Note that MindBridge is now supporting more and more date formats, including dd-mm-yy and mm-dd-yy that Excel will often convert to the wrong date, so some date columns without 4 digit years will also need to imported as Text.
  5. Hit the Finish button, and you will be taken to your file converted to Excel. To save the file in Excel format, choose Save As, go to the dropdown menu under File Format, and select Excel Workbook (.xlsx) 

     

Fixed Width Text Files

  1. For fixed-width format text files, select "Fixed width" in Step 1 of the Text Import Wizard.
  2. In Step 2, instead of being prompted to choose a delimiter, you will be prompted to specify where each column begins and ends by dragging, creating, or deleting the vertical arrows.
  3. Step 3 onward is the same as with a delimited file.

 

CSV Files

If the Text Import Wizard does not appear when you open CSV files in Excel, do the following before following the steps in the Delimited Text Files section:

  1. Rename the file to have .txt extension instead of .csv extension
    • To rename a file's extension in Windows 10 and above:
      • Open Windows File Explorer
      • If you do not already see extensions in your files: On the View Ribbon, turn on 'File Name Extensions'
      • Then you will be able to see the file extension. Right click the file and choose 'Rename' to change the .csv to .txt
  2. From within Excel, select to open a file (by hitting Ctrl-O on PC or ⌘O on Mac).
  3. The Text Import Wizard should appear. Follow all steps under the Delimited Text Files section, but in Step 2, select only comma as the delimiter.

Additional Considerations

Files with more than 1048576 rows cannot be converted to Excel.

Additionally, even files small enough to be converted to Excel can be slow and unwieldy to open and manipulate in Excel if they have more than about 100,000 rows and/or many columns.

 


Anything else on your mind?
Log in to MindBridge to chat with us, submit a request, or reach out to your assigned Customer Success Manager.


Related Articles

 

Was this article helpful?