Fix Data Import to Excel Data Type Issues


Ok. Excel often sucks because Microsoft thinks they're smarter than the rest of the world. So, they insist that "2-15" is a date and they will convert it to a date whether you like it or not. When the date is converted, it becomes a 5 (or 6) digit number and that's what is saved in your spreadsheet.

So, you have to fake out Excel. To do so, you need to save the spreadsheet on your desktop as a ".txt" file. Then you need to "import" this file into Excel. During the import process you need to identify the column you want left alone then import that column's data as "text" data.

Build and Save .csv File

The first step is to run the Advantos Data Transfer Builder Go ahead and run data transfer script you want to run. After the script runs you will be prompted to do something with the ".csv" file created. When this dialog box appears, select "Save as".

Save the file to your "Desktop". Note: save the file as a type: "All Files(*.*), then give it a name you want with a ".txt" extension. For example:

Click the [OK button in the Advantos CSV Manager dialog box.

Next open Excel by itself (e.g. Start > All Programs > Microsoft Office > Microsoft Excel). If you have an Excel icon somewhere else, use it.

With Excel opened to a blank sheet, click on the "Data" tab and click on "From Text" in the "Get External Data" section of the icons near the top of the window (the Excel ribbon).

Select the ".txt" file you just saved (usually on your desktop) to import. You will be presented with a "Text Import Wizard" by Excel. Click the [Next] button.

...assuming your options are as noted above (this is the default, normally).

Make sure you select a "Comma" delimited file (and uncheck "Tab"). This will present a limited amount of the file's data in a preview window. Then click the [Next] button.

Click on the column heading for the unit# (CUSTON), which says "General" (the data type) and change its data format to "Text". Now click the [Finish] button.

Another dialog box appears asking you where you want to put the data. Just click the [OK] button to put the data into the existing workskeet.

This will import the data into the spreadsheet leaving your unit#s alone, instead of converting them to dates.

Copyright © 1985-2023 - Advantos Systems, Inc. - All rights reserved.