从 csv 文件导入数据

发布于 2024-10-21 18:01:07 字数 341 浏览 5 评论 0原文

我正在尝试从 .csv 文件导入一些数据（使用 Oracle sql Developer），但收到错误如下：-

验证日期列是否具有日期格式失败日期列 ...我的 .csv 文件中的列名称

日期是：

2008-01-09 15:59:23.187

我已尝试提供此格式，但它不起作用（在数据导入向导中）

yyyy-mm-dd HH24:mi:ss.ff3

我试图弄清楚提出一个解决方案，期待一些帮助。

谢谢。

原文

I'm trying to import some data (using oracle sql developer) from a .csv file but I'm getting an error as :-

Verifying if the Date columns have date formats FAILED Date columns ... column names

the date in my .csv file is :

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

煮酒 2024-10-28 18:01:07

我现在无法测试，但我猜测：格式不是日期格式而是时间戳格式。考虑：

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

如果这与 SQL Dev 遇到的错误相同，您可以导入时间戳列。

I can't test right now but I'm taking a guess: the format is not a DATE format but a TIMESTAMP format. Consider:

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.

回复收藏 0 原文

感悟人生的甜 2024-10-28 18:01:07

Oracle 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

Oracle Date's can only store to seconds, you will need to use the timestamp.

Using a timestamp works fine (date fails with error of "Fractional seconds format element not allowed in DATE formatting")

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

回复收藏 0 原文

~没有更多了~