oracle 从 csv 文件导入数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5309450/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Importing data from csv file
提问by Switch
I'm trying to import some data (using oracle sql developer) from a .csvfile but I'm getting an error as :-
我正在尝试从.csv文件中导入一些数据（使用 oracle sql developer），但出现以下错误：-
Verifying if the Date columns have date formats FAILED Date columns... column names
Verifying if the Date columns have date formats FAILED Date columns... 列名
the date in my .csvfile is :
我.csv文件中的日期是：
2008-01-09 15:59:23.187
I have tried giving this format but it doesn't work (in the data importing wizard)
我试过提供这种格式，但它不起作用（在数据导入向导中）
yyyy-mm-dd HH24:mi:ss.ff3
I'm trying to figure out a solution expecting some help.
我试图找出一个解决方案，期待一些帮助。
Thanks.
谢谢。
回答by Vincent Malgrat
I can't test right now but I'm taking a guess: the format is not a DATE format but a TIMESTAMP format. Consider:
我现在无法测试，但我在猜测：格式不是 DATE 格式而是 TIMESTAMP 格式。考虑：
SQL> select to_date('2008-01-09 15:59:23.187',
2 'yyyy-mm-dd HH24:mi:ss.ff3') from dual;
ORA-01821: date format not recognized
SQL> select to_timestamp('2008-01-09 15:59:23.187',
2 'yyyy-mm-dd HH24:mi:ss.ff3') ts from dual;
TS
-------------------------------------------------
09/01/08 15:59:23,187000000
If this is the same error that SQL Dev encounters, you could import in a timestamp column.
如果这与 SQL Dev 遇到的错误相同，您可以导入时间戳列。
回答by Harrison
Oracle Date's can only store to seconds, you will need to use the timestamp.
Oracle Date只能存储到秒，您将需要使用时间戳。
Using a timestamp works fine (date fails with error of "Fractional seconds format element not allowed in DATE formatting")
使用时间戳工作正常（日期失败，错误为“DATE 格式中不允许使用小数秒格式元素”）
create table test2(cola number(1), colB varchar2(5), colD timestamp);
csv file:
colA, colB, colD
"1","a","2008-01-09 :15:59:23.187"
"2","b","2009-02-10 :16:48:32.188"
"3","c","2012-03-11 :17:37:41.189"
"Import Data" in SQL Developer 3.0.03 using colD format of yyyy-mm-dd HH24:mi:ss.ff3
select * from test2;
COLA COLB COLD
---------------------- ----- -------------------------
1 a 09-JAN-08 03.59.23.187000000 PM
2 b 10-FEB-09 04.48.32.188000000 PM
3 c 11-MAR-12 05.37.41.189000000 PM
