Using nzload to upload a file with two differing date formats

Question

I am trying to load onto Netezza a file from a table in an Oracle database, the file contains two separate date formats - one field has the format DD-MON-YY and the second field has the format DD-MON-YYYY hh24:MI:SS, is there any with in NZLOAD to cater for two different date formats within a file

Thanks rob..

Answer 1

If your file is fixed-length, you can use zones

However, if its field delimited, you can use some of the preprocessing tools like sed to convert all the date / timestamp to one standard format, before piping the output to nzload.

for ex.,

1. 01-JAN-17
2. 01-JAN-2017 11:20:32

Lets convert the date field to same format

cat output.dat |\
sed -E 's/([0-9]{2})-([A-Z]{3})-([0-9]{2})/\1-\2-20\3/g' |\
nzload -dateStyle DMONY -dateDelim '-' 

sed expression is pretty simple here, let's break it down

# looking for 2 digits followed by 
# 3 characters and followed by
# 2 digits all separated by '-'
# elements are grouped with '()' so they can be referred by number
's/([0-9]{2})-([A-Z]{3})-([0-9]{2})  

# reconstruct the date using group number and separator, prefix 20 to YY    
/\1-\2-20\3

# apply globally
/g'

also in nzload we have specified the format of date and its delimiter.

Now we'll have to modify the regular expression depending upon different date formats and what they are getting converted to, this may not be an universal solution.