How to Preserve Trailing Zeros When Importing Data

This article was previously published under Q192041
This article has been archived. It is offered "as is" and will no longer be updated.
Novice: Requires knowledge of the user interface on single-user computers.

SUMMARY
When you import data into Microsoft Access, trailing zeros may be lost. This will happen when you import data that is formatted to show these zeros, but where the zeros are not actually part of the data. For example, in a Microsoft Excel workbook, you can format the number 1234 so that it will be displayed as 1234.000. When you import this workbook into a Microsoft Access table, the number will be displayed as 1234. This articleshows you how to preserve trailing zeros when you import data into Microsoft Access.
MORE INFORMATION
The following example illustrates how to import formatted numeric data froma Microsoft Excel worksheet into a Microsoft Access database, and thenhow to create a query that filters the numbers.

Creating the Import Data File

This section shows you how to create a Microsoft Excel worksheet that hasnumbers formatted to contain trailing zeros in each cell. It then showsyou how to save the worksheet to a text file to retain the trailing zeros.These trailing zeros are part of the number format, not part of the dataitself.
  1. Start Microsoft Excel.
  2. Type the following data:
          A1: 1234      A2: 3.7      A3: 45.01      A4: 15.75						
  3. For each of the following cells, on the Format menu, click Cells. In the Category list, click Number. Type the following numbers in the Decimal places box, and then click OK:
          A1: 3      A2: 2      A3: 4      A4: 3						

    Notice that the numbers appear in the following format:
          A1: 1234.000      A2: 3.70      A3: 45.0100      A4: 15.750						

  4. On the File menu, click Save As. In the File Name box, type Import. In the Save As Type list, click Text (Tab delimited)(*.txt). Click Save. Click OK.
  5. On the File menu, click Exit. Click No because the file was saved in the previous step.
NOTE: If you receive a warning that says "The selected file type does notsupport workbooks that contain multiple sheets," click OK.

Importing the Data

This section shows you how to import the data into Microsoft Access.

NOTE: The numbers in the new table will appear the same as they did in theExcel workbook.
  1. Start Microsoft Access.
  2. Open the sample database, Northwind.mdb.
  3. On the File menu, point to Get External Data, and then click Import.
  4. In the Files Of Type list, click Text files (*.txt;*.csv;*.tab;*.asc). Click Import.txt, and then click Import.
  5. On the first screen of the Text Import Wizard, click Delimited, and then click Next.
  6. When the Text Import Wizard asks you to Choose Your Delimeter, click the Tab option, and then click Next.
  7. Click Next again to skip the question about a New or Existing table, and then click Next.
  8. Type TextNumbers in the Field Name box and in the Data Type list, click Text. Click Next.
  9. On the fifth screen of the Text Import Wizard, click No Primary Key. Click Finish, and then click OK.

Using the Numeric Value of the Data

The following example shows how you can use the CVar() function to have thecontents of the field be evaluated as a number data type rather than as atext data type. Because the data is in a text format, you may not receivethe expected results when running the query, unless you use the numericvalue of the number.
  1. Create the following query:
          Query: qryImport      ---------------------------------------      Type: Make Table Query      Field: RealNumbers: CVar([TextNumbers])      Criteria: >20						

  2. On the Query menu, click Run.
  3. On the File menu, click Save. Type qryImport, and then click OK.
  4. On the File menu, click Close.
NOTE: The trailing zeros will be lost if you perform a calculation on thenumber. For example, the trailing zeros will be lost if you change thefield in Step 1 to the following:
   Field: RealNumbers: CVar([TextNumbers])*1				
REFERENCES
For more information about data types, click Contents And Index on the Helpmenu, click the Index tab in Microsoft Access Help, type the following textdata typeand then double-click the selected text to go to the "Type ConversionFunctions" topic. If you are unable to find the information you need, askthe Office Assistant.
inf
Properties

Article ID: 192041 - Last Review: 10/10/2013 20:49:31 - Revision: 2.1

Microsoft Access 2.0 Standard Edition, Microsoft Access 95 Standard Edition, Microsoft Access 97 Standard Edition

  • kbnosurvey kbarchive kbhowto KB192041
Feedback
ERROR: at System.Diagnostics.Process.Kill() at Microsoft.Support.SEOInfrastructureService.PhantomJS.PhantomJSRunner.WaitForExit(Process process, Int32 waitTime, StringBuilder dataBuilder, Boolean isTotalProcessTimeout)