How to Create a Space Delimited Import File in Excel

Need to know how to easily create a Space Delimited Import File in Excel. 

• Imports

• Accounts Receivable

• Special Assessments (txspasmt)

A variety of programs offer the ability to import records with a space delimited file. A space delimited import file needs to be created. 

Space Delimited Import processes require that the necessary data be in specific columns or positions within a text document. The programs import the data based on the positions that the data is in. For example, in the value 123456789, the numeral 1 is in the first position, 2 in the second position, etc. 

There are many ways a Space Delimited Import File can be created. One way to easily create these files is in Excel using these basic steps: 

1. Open Microsoft Excel.

2. Open the corresponding import layout document.

3. Treat each cell in the spreadsheet as field. Enter the desired value into the cells using the import layout document as a reference.

4. Highlight all the cells with an entered value. 

5. RIGHT CLICK and choose Format Cells.

6. Select Text. 

7. Click OK. 

8. When all required and desired fields have been filled out and formatted, set each cell to the appropriate size as listed in the import layout document.

   1. To do this, RIGHT CLICK on the Column Header in Excel, for example, column A. 

   2. Select Column Width from the dropdown. 

   3. Enter the size of each field listed in the import document as the Column Width. 

   4. Click OK.

   5. Repeat steps a-d for each field.

9. When all fields have been appropriately sized, click File.

10. Choose Save As.

11. Enter the desired File name. 

12. Choose to save the document as Formatted Text (Space delimited) (*.prn). 

13. Click Save.

14. Find the newly created .prn file in the file directory. 

15. RIGHT CLICK the file and select Open with and then either Notepad (comes standard on all Window's machines) or Notepad++ (recommended, but must be installed). 

16. Compare the file to the import layout document and ensure that the data is in the right positions. 

• If entering values that have leading zeroes, for example, 000123, be sure to format the cells as text before beginning. Excel will drop leading zeroes in cells by default unless they are formatted correctly. 

• If the import file is longer than 240 characters per line, then the Formatted text (.prn) format will not work. Lines longer than 240 characters are wrapped onto the next line when the file is converted to the .prn format and this breaks the import file.

• There is a known issue with Microsoft's Excel where right justified fields will add a space after the field when it is saved as a .prn file. Some users report that installing the most recent updates for Excel resolve this issue. A way to work around it, if possible, is to fill out all of the positions in the cell. For example, if entering 10.00 as an amount in a field that is 8 characters/positions long, enter 00010.00 instead.