ETL and Data Integration tools - Modern ETL software for everyone

Good afternoon..

I am trying to move timestamps from an old SQL Anywhere database using an ODBC driver into a Postgres database. The Tables are identical in both databases. The timestamps are in the “YYYY-MM-DD HH:MM:SS.SSS” format. I am trying to use your Visual Importer and I have specified the from field as a char(23) in the old SQL Database as I can change the data on the fly and I have specified the postgres data field as timestamp without time zone and the field definition as above. All I am getting is the following error

“Record/Line:1 Record Rejected: Can’t apply format YYYY-MM-DD HH:MM:SS.SSS for field record_added(TIMESTAMP WITHOUT TIMEZONE) value 2006-05-23 22:34:42.096”.

I need to keep the microseconds of the timestamp….

Any suggestions???
Martin

can you email us table creation script for PostgresSQL database and example of your data as a text file please

Mike

Got all necessary data

You are using wrong date format you should be using YYYY-MM-DD HH:NN:SS.FFF

Have a look at the documentation there is a chapter describing formats supported.
Also have a look at the diagram at the beginning (2nd) it will explain the order of events

Which version have you got?
Latest standard version supports direct connection to Postgresql MySQL, Interbase
Professional and Enterprise to follow

Mike

Hi there Mike

I am using latest version of Visual Importer ETL and I cannot find any mention in the Visual Importer docs about FFF. What I see is the ZZZ for oracle time stamps. The postgres documentation does not seem to have anything about FFF either. I have tried it and all I get is the same error.

Martin

>>I am using latest version of Visual Importer ETL and I cannot find any mention in the Visual Importer docs about FFF. What I see is the ZZZ for oracle time stamps.

There is a mistake in documentation.

>>The postgres documentation does not seem to have anything about FFF either. I have tried it and all I get is the same error.

Important thing to understand that this format has nothing to do with your target database.

This the format of the source data.
It is there to help to covert string into datetime type inside of visual importer, so we can load it later into date or timestamp field

so if you see in the bottom grid

16/08/2009 your format is DD/MM/YYYY
1/31/2009 your format is M/D/YYYY
2006-05-23 22:34:42.096 your format is YYYY-MM-DD HH:NN:SS.FFF
1992/mar/12 00:00 your format is YYYY/MMM/DD HH:NN

Mike

Hi there

Yes…. That is what I thought. No problems …..
I have corrected all the issues with all the other dates but it is just the timestamp 2006-05-23 22:34:42.096 format is YYYY-MM-DD HH:NN:SS.FFF that I am having trouble with…… Everything else is coming over correctly and very quickly (test database is over 100,000 records just in the memberships table and it is coming over in about a 1 min 30 seconds.)
If I can get around this problem you will have saved me over 3 weeks of work!

Thanks,
Martin