Interesting problem: Reading an Excel 2003 file via an ODBC connection and noticed that all of my date values were coming in as NULL. I switched to a CSV file using a sequential file input and the dates come in as expected. In either case the text values read fine. Only the dates are corrupted.
Any ideas (other than avoiding all Excel files!)
ODBC Excel read returns NULL for dates
Moderators: chulett, rschirm, roy
ODBC Excel read returns NULL for dates
Evan Van Gelder
ODBC and Excel connections are problematical, at best. ODBC will determine the clumn datatypes dynamically, using the first couple of rows (this number can be adjusted in the ODBC settings). Thus a column that you think holds a date might be delivered to DataStage as a string or a number - and the next run might be sent as a different type.
I've gone the route of explicitly marking and declaring all columns as strings and then done the conversion inside datastage. Or adding 10 or so dummy lines at the beginning with specific values to force the driver to choose the correct datatype and then ignoring those lines in DataStage.
I've gone the route of explicitly marking and declaring all columns as strings and then done the conversion inside datastage. Or adding 10 or so dummy lines at the beginning with specific values to force the driver to choose the correct datatype and then ignoring those lines in DataStage.
</a>ODBC Dates
They are coming in as strings, but the cells that contain date information are null.Kryt0n wrote:Are you sure they are dates, or string representation of dates?
Evan Van Gelder
Are you getting any dates coming through? Or all dates are being dropped?
As with what ArndW said, are the first few rows actually dates in Excel? If the ODBC driver had determined the column to be date based on the first x rows, any that aren't explicitly dates in Excel will be dropped, whether they look like dates to the eye or not. Really you need to force ODBC to see the column as string/varchar if you have the potential for mixed date/string
As with what ArndW said, are the first few rows actually dates in Excel? If the ODBC driver had determined the column to be date based on the first x rows, any that aren't explicitly dates in Excel will be dropped, whether they look like dates to the eye or not. Really you need to force ODBC to see the column as string/varchar if you have the potential for mixed date/string
ODBC Excel Dates
That sounds like the problem. The initial rows have NULL values and then there is a block of text strings before any date values appear. Is there a way to force ODBC to view it as strings short of seeding the first several rows?
Evan Van Gelder
ODBC Excel Dates
When I forced the column to text by inserting a quote ('2012-09-12) The values all flow through as strings and show up. Without the quotes, the date values disappeared.ArndW wrote:Can you see if that changes when you explicitly make strings in the cells, i.e. '="2012-09-12"'? ...
Evan Van Gelder