Solving the Mystery of Delimited Files
If you’ve thumbed through any of the Tyler documentation regarding importing/exporting files, you’ve undoubtedly seen these instructions:
“Make sure to save the spreadsheet as a comma delimited file.”
“The exported information will be saved to your computer as a tab delimited file.”
Despite our best efforts, it’s quite possible that those instructions made no sense to you whatsoever.
Dealing with delimited files can be confusing. But since many files that are imported and exported from Tyler use this format, it’s important that you understand how to view and create them.
Why Does Tyler Use Delimited Files?
Delimited files make it possible for information exported from Tyler (e.g. GUI Reports) to be opened in a spreadsheet program like Microsoft Excel. There you can re-organize the information, apply formulas, email it to a co-worker, etc.
Similarly, delimited files also make it possible for information from a spreadsheet (e.g. bottom text for invoices) to be imported into Tyler and used by the system.
What’s Is a Delimited File?
A delimited file is a file that uses one of the following “delimiters” to separate the information:
- tabs (spaces)
- commas
- the pipe symbol (|)
To see delimiters in action, all you have to do is open the file in Notepad.
Excel and Other Spreadsheet Software
For this article, we are assuming that you are using Microsoft Excel to open and create delimited files. If you are using another spreadsheet program (e.g. OpenOffice Calc), we have detailed instructions on creating and viewing delimited files here.
It’s All in the Name
When exporting a file from Tyler, it’s important to know the type of file you are working with so that you can name it correctly (‘.txt’ vs ‘.csv’). If the delimited file is saved with the wrong extension, it will not open correctly in Excel. For example, if you try to open a tab delimited file that was saved as a .csv, you will have problems.
The following are the extensions that should be used for each type of file:
If you forget to change the extension before downloading, you can always rename the file in Windows Explorer.
Always Open Delimited Files from within Excel
It’s important to always open delimited files from within Excel. Do not try to open them by clicking on the file in Windows Explorer, or you will lose the leading zero in all fields that begin with a zero. When those leading zeros are gone, you will need to either re-type each one manually, or close the file and then re-open it the correct way (we recommend the latter).
Now, Try it Yourself
We’ve covered the basics, so let’s take a step-by-step look at how to open a delimited file in Excel, and also how to save a spreadsheet as a delimited file in Excel.
Overview
This tutorial describes how to export a delimited file and open it in Excel. For this how to, we’re exporting the Sales Analysis Daily One Liner report (05-41-02).
Video
Step-by-Step
- Run the report you want to export and click Export File.
- Accept or change the pre-prompted download location and filename.
REMEMBER: If the file is tab or pipe delimited, make sure the extension is ‘.txt’. If it is comma delimited, save it as a ‘.csv’. If you’re unsure of the file type, download it to your computer and open it in Notepad. Then rename it in Windows Explorer accordingly.
- Open Microsoft Excel. Click File > Open > Computer > Browse and navigate to the folder where the file is saved.
- This step will vary slightly, depending on the version of Excel you are using.
- Using the drop-down list, change the file type from ‘All Excel Files’ to ‘All Files (*.*).
- Double-click the delimited file you want to open.
- The Text Import Wizard appears.
NOTE: You should never open a delimited file without using the Text Import Wizard or you will lose the leading zero in all fields beginning with a zero. If you open a delimited file and the Text Import Wizard screen does not appear, close the file and change the extension (if it was ‘.txt’, change it to ‘.csv’, or vice versa). Then open it again.
- On the first screen, select Delimited, since this is a delimited file.
- Click Next.
- On the second screen, select the delimiter for the file by checking ‘Tab’ or ‘Comma’ (depending on the file type). All other boxes here should be unchecked.
NOTE: If this is a pipe delimited file, check the ‘Other’ box and enter the pipe symbol ( | ) in the box to the right.
- Click Next.
- On the third screen, in the Data preview area, use the scrollbar to move to the last column on the spreadsheet.
- While holding down the Shift key, click the column heading of the last column. This selects all information in the file (all columns should be highlighted in black).
- Click the Text radio button at the top of the screen.
NOTE: This step is critical because if you do not format the information as text, the leading zero will be lost in all fields that begin with a zero.
- Click Finish.
- The report opens in Excel, where you can edit and view the data as necessary. We recommend saving the spreadsheet as an Excel file (.xlsx) for easy editing later.
Overview
This process is used when you need to import information from one of your spreadsheets to Tyler.
For example, if you are using Bottom Text Load (02-18-15) to create bottom text for your invoices, you first need to create a spreadsheet containing this information. Then you will save the spreadsheet as delimited file and import it to Tyler.
Step-by-Step
- Create your spreadsheet.
- Click File > Save As.
- This step will vary slightly, depending on the version of Excel you are using.
- Navigate to the folder where the file is being saved.
- Select ‘Text (Tab Delimited) (*.txt)’ from the file type drop-down list to save the spreadsheet as a tab delimited file.
Select ‘CSV (Comma delimited) (*.csv)’ to save the spreadsheet as a comma delimited file.
NOTE: Spreadsheets are not usually saved as pipe delimited files for import to Tyler.
- Enter the name of the file in the ‘File Name’ box.
- Click Save.
- Click Ok at the first warning message and click Yes at the second warning message.
- Upload the file using the appropriate Tyler selection.
Did I Leave Anything Out?
Hopefully this article has helped you make heads or tails of delimited files. If I left something out or you are still confused, please take a minute to let me know!