postgres导入excel_如何将Excel数据导入到postgresql数据库中.doc

如何将Excel数据导入到postgresql数据库中

如何将Excel数据(*.xls)导入到ostgresSQL数据库中

一、Excel数据(*.xls)数据文件：

numbersimidlatlonspeedangletimestaternc_time4852507鑻廈0T001 31.58487120.320812251.43E+09t5792015-05-04 07:35:30+085107494鑻廈0T001 31.55635120.293701801.43E+09t6592015-05-04 07:57:38+085303724鑻廈0T001 31.54643120.351403151.43E+09t7162015-05-04 08:14:30+085466887鑻廈0T001 31.57333120.323102251.43E+09t7602015-05-04 08:28:28+085720166鑻廈0T001 31.53852120.27690451.43E+09t8392015-05-04 08:49:56+085762630鑻廈0T001 31.53613120.2876341351.43E+09t8532015-05-04 08:53:32+084616750鑻廈0T002 31.55715120.307902701.43E+09t23532015-05-04 07:14:50+084973998鑻廈0T002 31.58148120.285302701.43E+09t25122015-05-04 07:46:05+085167618鑻廈0T002 31.60405120.30801801.43E+09t25932015-05-04 08:02:48+085700809鑻廈0T002 31.57598120.299101801.43E+09t28382015-05-04 08:48:18+08

二、导入步骤：

1、根据Excel文件另存为".csv”格式文件；

2、根据Excel数据的属性创建表

-- Table: public.texipos

-- DROP TABLE public.texipos;

CREATE TABLE public.texipos

(

number0 integer, simid character varying(20), lat numeric, lon numeric,

speed integer, angle integer, time0 bigint, state text, rn integer,

c_time character varying(100)

)

WITH (

OIDS=FALSE

);

ALTER TABLE public.texipos

OWNER TO postgres;

创建表如图下所示

3、打开psql.exe命令行工具

4、数据导入

在命令行中输入：copy 数据表名称 form '*.csv文件路径' with delimater ',';

回车；

数据导入的结果如下图所示：