How to open a delimited text database file safely in Microsoft Excel

DesignMerge® Pro is a powerful suite of variable-data printing and document assembly software for QuarkXPress or Adobe InDesign. DesignMerge provides the ability to merge records of variable data from a delimited text file into a document. DesignMerge also includes ability to produce Intelligent Mail Bar Codes, automatic copy-fitting, and advanced conditional programming features. Supports all major variable data formats, including PPML, PDF/VT, and Creo VPS. Can also be extended to produce 2D Bar Codes and other types of Linear Bar Codes. Great for creating personalized publications such as invitations, business cards, certificates, custom catalogs, etc. For more information about DesignMerge, go to http://www.designmerge.com.

 

AutoPrice™ helps to streamline the production and versioning of catalogs, price lists, retail flyers and financial documents. AutoPrice provides sophisticated linking of documents to any external database of information (either a delimited text file, or ODBC). AutoPrice is great for performing price, picture, or text updates for any type of information that can be referenced by a “key value” such as product number (SKU). For more information about AutoPrice, go to http://www.meadowsps.com/autoprice.

If you are using Microsoft Excel to open or edit a delimited text file for use with DesignMerge or AutoPrice, then you will prefer to open the database file without automatically changing any of its content. 

 

When Microsoft Excel automatically opens a plain delimited text file, Microsoft Excel will apply the General format to the data inside this file. This format may automatically change the text that is in the database. For example, this format will remove leading zeros and will also remove zero decimal digits.

 

To prevent the text in the database from being automatically changes when the database is opened in Microsoft Excel, follow the steps below:

  1. Change the filename extension on the database file to the .TXT filename extension.
     
  2. Start Microsoft Excel.
     
  3. Open the MS Excel File menu and select Open.
     
  4. Select the .TXT database file.
     
  5. This will automatically start the MS Excel Text Import Wizard.
     
  6. On Step 1 of the Text Import Wizard, select Delimited for the Original Data Type, and click Next to continue to the next step.
     
  7. On Step 2 of the Text Import Wizard, select the appropriate Delimiter, uncheck Treat consecutive delimiters as one, and click Next to continue to the next step.
     
  8. On Step 3 of the Text Import Wizard, click on the First Column that is displayed in the Data Preview box.
     
  9. Then, scroll in the Data Preview box to the Last Column, press Shift and click on the Last Column. This selects all columns in the Data Preview box.
     
  10. With all columns selected in the Data Preview box, select Text for the Column data format
     
  11. Confirm all columns are labeled as Text in the Data Preview box, and then click the Finish button.
     
  12. The database file will now be opened in MS Excel and all of the data in this open file will have been formatted as Text (no changes will have been made to the text data).
 

Copyright © Meadows Information Systems, LLC d/b/a Meadows Publishing Solutions. All rights reserved worldwide.

DesignMerge is a registered trademark of Meadows Information Systems, LLC.

Quark, QuarkXPress and XTensions are registered trademarks of Quark, Inc.

Adobe and InDesign are registered trademarks of Adobe, Inc.