Hi everyone,
I extract a number from a csv file and load it into oracle. The oracle type is number, and I got this error message.
Transformer: ORA-01722: invalid number
I looked this up, it indicates that I am inserting something that is not a number to a numer field.
but the data i get from csv are numbers.
does anyone know what's going on?
your answer will be greatly appreciated!
load number
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Are they really numbers?
After all, the only data type in a text file is "text".
Try performing some arithmetic on them, to force them to be numbers (adding zero is the most efficient).
If you get errors ("non numeric where numeric required") you will know that what you have in the CSV file are not necessarily numbers.
Perhaps there is a leading space - some applications use a space for positive numbers and a sign for negative numbers. Performing arithmetic will remove these without generating errors.
After all, the only data type in a text file is "text".
Try performing some arithmetic on them, to force them to be numbers (adding zero is the most efficient).
If you get errors ("non numeric where numeric required") you will know that what you have in the CSV file are not necessarily numbers.
Perhaps there is a leading space - some applications use a space for positive numbers and a sign for negative numbers. Performing arithmetic will remove these without generating errors.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.