I'm importing from an Excel spreadsheet that has been saved as a .WKS file and I'm getting 'BAD DATE' error messages for dates that are in the year 2000 but the dates look fine on the spreadsheet. What's the problem?
The date cells in the spreadsheet have been formatted as 'Custom' instead of a standard 'Date' format. You can check this by highlighting one of the cells, right click, and select 'Format Cells'.
A common way that a cell gets formatted this way is when a text file (which you may have created using Report Writer) is opened in Excel, you are given the option to format cells as the text is converted to a spreadsheet. If you select 'Date', Excel will format them as 'Custom'. It's better to leave all the columns formatted as 'General' which is the default.
Another way that dates get formatted as 'Custom' is to just type a date, for example 8/22/00, into a cell. Excel will assume that because of the '/', it's a date and will format it as a 'Custom'. To prevent this from occurring, format the column as a standard date format first. To do this first, left click on the letter at the top of the column to block the entire column then, right click and choose 'Format Cells' and then choose one of the standard 'Date' formats.
Once a cell gets formatted as 'Custom', there seems to be no way to change the format. To import the data, save the spreadsheet as a .CSV (comma separated values) file by choosing File -> Save As -> Save as type and selecting .CSV. Then go to the Data Import Assistant which is located in the Utilities Menu of the Pension System (Cycle B, Choice I) and change the File Type to Record type = Text separated by CR/LF and Field type = Separated by comma. The records and fields definitions do not have to be changed. PLEASE NOTE: if you've used the Row Start/Row End parameters in your .DIS file, these will not be supported when you change the Record and Field types as described above. That means if you have header rows that you've taken into account with a Row Start = XX in the .DIS file for example, you'll need to delete those rows before saving the file as a .CSV.
|
For more information about this or any other Knowledge Base
Article, please contact
[email protected] |
|
|
|
DATAIR Home Page
Article DKB00070