Import Informix to ACCESS

I'm not an ACCESS expert, but I quickly found that date/time fields are treated differently there than in Informix SE. I had to figure out what ACCESS would tolerate during import from a text file. Hope this helps someone out there or gives them inspiration. - Dan # xform1.awk by Dan Martin (DRM/CTG) Fri May 31 14:46:36 CDT 1996 # Purpose is to re-write Informix data-type "DATETIME YEAR TO SECOND" # UNLOADs (e.g., 1996-09-13 14:23:33) into a layout tolerated by the # "General Date" format of Microsoft's ACCESS Date/Time data-type. # (e.g., 05/02/1996 14:05:46). # INPUT: Any delimited ("^" recommended) textfile that has been UNLOADed # by Informix. The file must contain one (and one only) field of # data-type "DATETIME YEAR TO SECOND" (e.g., 1996-09-13 14:23:33). # ACTION: Does a loop which outputs each field (plus a "^" delimiter) # unchanged until the field counter "fc" equals variable "xf". # When it does, transform the field according to rules within # function xdt(). Continues outputting remaining fields unchanged. # OUTPUT: Field-Delimiters are hard-coded to "^". Output is the same as # input except that layout of one field has been transformed. # LIMITS: One (and one only) input-field must be of Informix data-type # "DATETIME YEAR TO SECOND" (e.g., 1996-09-13 14:23:33). The # variable "xf" must be set equal to the appropriate field-number. # LAUNCH: awk -F"^" -f xform1.awk xf="18" ifx_unl1.txt >acc_imp1.txt BEGIN \\ {#xf = # Field Number to Transform (must be passed in by shell) fc = 0 # Field-Counter } # main() {xout = "xxx"; fc = 1 while (fc < xf) {printf("%s^", $fc); ++fc} if (fc == xf) {xdt($xf); printf("%s^", xout); ++fc} while (fc <= NF) {printf("%s^", $fc); ++fc} printf("\\n") } # FUNCTION # function xdt(dstr) transforms a "Year To Second" Informix datetime string # (e.g., 1996-09-13 14:23:33) into a layout acceptable for Import into the # "General Date" format of ACCESS's Date/Time data-type. function xdt(dstr) {yr = substr(dstr, 1,4) # Year mo = substr(dstr, 6,2) # Month da = substr(dstr, 9,2) # Day tm = substr(dstr,12,8) # Time xout = sprintf("%s/%s/%s %s", mo, da, yr, tm) return xout } # EOF xform1.awk