Questions? We have answers.

Converting Text Files (delimited or fixed width) to Excel

Article author
Kate H
  • 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. In fact, 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.
  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.

Additional Consideration

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

 

Share this:

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.