Audimation Services has been acquired by Caseware International Learn More.

X
Icon


Blog Image

Importing Date Fields


There are times when you import a date field and it just does not work out as expected. Here are ways to address the problem before or after the import.
 
REPORT READER import requires a MASK when you classify a field as a date. If the data shifts slightly, you need to configure the field as character in order to import. If the date is formatted as MM/DD/YYYY (2-digit month and day) it is easy enough once imported to MODIFY FIELD converting it from character to date by supplying the correct “mask”.
 
However – there are times that it varies, mixed 1 and 2 digits for month and day.

Check out the CaseWare Passport for Custom Functions for IDEA.
 

Convert Date – DMYYYY to IDEA Date Format
Converts a string in D/M/YYYY or D-M-YYYY format to the IDEA date format (YYYYMMDD).
D can be one or two digits (1 or 01). M can be one, two or three characters or digits (01, 1, Jul). Any date prior to 19000101 is invalid.

 
Download the custom function file: DMYYYYToIdea.ideafunc and save to the CUSTOM FUNCTIONS.ILB folder of your current project. Equation to APPEND a new DATE field would be:
 

#DMYYYYToIdea(CHAR_DATE_FIELD)

 
EXCEL imports can cause a different problem. IDEA scans the data in each column for a fixed number of records to determine the data type. If you forget to check FIRST ROW IS FIELD NAMES, all columns are imported as CHARACTER. If you fail to check IMPORT EMPTY NUMERIC CELLS AS 0, columns with blank rows are imported as CHARACTER. In addition, for some reason, the mask for the imported dates is DD/MM/YYYY if you choose to MODIFY FIELD to convert from character to date by supplying the correct “mask.”

Import Assistant - Import Options for an Excel Spreadsheet

Want to avoid the need to append or modify a field? Simply check both options prior to import. Both options are automatically checked starting with IDEA 10.4.

Import Assistant - Option to Import Empty Cells as 0

Both columns imported as date fields!

View of Update Date Fields in IDEA

If you have questions about this or other IDEA functions, please give our help desk a call at:
888-641-2800 Option 4
Or email us at [email protected]


CaseWare IDEA , Tech Tip



Posted By

By Kris Willison
Kris joined the Professional Services team in January of 2015 as a Solutions Specialist. She has an extensive background in Software and Database Development accumulated from thirty years in IT support with twenty years’ experience in database development, cleanup, audit and migration using Microsoft Access. In her time with Audimation, she has received client praise for her “Top Tier” engagement on Monitor and Scripting projects. Kris enjoys looking at problems from new angles to determine the most efficient means of meeting the clients’ needs. Kris has been breeding/showing purebred Balinese cats since 1972 and Oriental Longhairs since 1996. She also hosts one of the largest online pedigree database sites for Siamese and related breeds with nearly 600 users worldwide.


Related Posts
Cannabis Industry Using Data Analytics to Reduce Risks & Maximize Profits
Nov 05 Imagine taking a job as an internal auditor in a rapidly-expanding, highly-lucrative business, with the best intentions to help stakeholders identify and manage...
Tech Tip: Transforming Your Data
May 15 We are inundated with data on a daily basis. Here are a few time-saving tips for transforming your raw or jumbled data into useful information.   Adding...
The Rules of Nature and Audit
Jun 06 What’s missing is often as important as what’s present. The rules of nature apply very well to audit if you consider, “Everyone knows it, but do you test ...
BROWSER NOT SUPPORTED

This website has been designed for modern browsers. Please update. Update my browser now

×