I had to load some data into our Oracle test database server. The data was created in Excel, exported as CSV files, then read into Oracle using sqlldr.exe (SQL Loader). Pretty straightforward, but sqlldr.exe complained about the date format of some entries and some constraints being broken. It turns out that the date field in the CSV files were "dd/mon/YY" while sqlldr.exe expected "DD-MON-YY" by default. Also, Excel was writing two digit year values, so some start dates were occurring after end dates. For example, if the start and end dates were "1-Jan-2006" and "31-Dec-9999", then they were being written as "1-Jan-06" and "31-Dec-99". Solution was to set the NL_DATE_FORMAT environment variable to YYYY/MM/DD and ensure that the date fields in Excel match them.
I had to be a little careful with setting NL_DATE_FORMAT. In Windows shell, double quotes are treated literally in statements, so use SET NL_DATE_FORMAT=YYYY/MM/DD instead of SET NL_DATE_FORMAT="YYYY/MM/DD" to define the environment variable's value. I guess this will bite you if you come from a Unix environment where the shell treats double-quotes as a string delimiter.
No comments:
Post a Comment