如何将EXCEL导入HGDB

目录

环境

文档用途

详细信息

环境

系统平台：Linux x86-64 Red Hat Enterprise Linux 7

版本：5.6.5

文档用途

本文用于指导如何使用copy命令将excel导入HGDB，安全版企业版数据库均可参照使用。

详细信息

一、copy from命令

1、语法

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]
其中 option 可以是下列之一：
    FORMAT format_name
    OIDS [ boolean ]
    FREEZE [ boolean ]
    DELIMITER 'delimiter_character'
    NULL 'null_string'
    HEADER [ boolean ]
    QUOTE 'quote_character'
    ESCAPE 'escape_character'
    FORCE_NOT_NULL ( column_name [, ...] )
    FORCE_NULL ( column_name [, ...] )
    ENCODING 'encoding_name'

2、参数详解

--table_name
现有表的名称（可以是模式限定的）。
--column_name
可选的要被复制的列列表。如果没有指定列列表，则该表的所有列都会被复制。
--filename
输入或者输出文件的路径名。一个输入文件的名称可以是一个绝对或相对路径， 但一个输出文件的名称必须是绝对路径。Windows 用户可能需要使用一个 E''字符串并且双写路径名称中使用的任何反斜线。
--PROGRAM
要执行的命令。在COPY FROM中，输入 将从该命令的标准输出读取，而在COPY TO中，输出会 写入到该命令的标准输入。
注意该命令是由 shell 调用，因此如果你需要传递任何来自不可信来源的 参数给 shell 命令，你必须小心地剥离那些可能对 shell 有特殊意义的特殊 字符。出于安全原因，最好使用一个固定的命令字符串，或者至少避免传递 任何用户输入到其中。
--STDIN
指定输入来自客户端应用。
--FORMAT
选择要读取或者写入的数据格式： text、 csv（逗号分隔值）或者binary。 默认是text。
--DELIMITER
指定分隔文件每行中各列的字符。文本格式中默认是一个制表符， 而CSV格式中默认是一个逗号。这必须是一个单一 的单字节字符。使用binary格式时不允许这个选项。
--HEADER
指定文件包含标题行，其中有每一列的名称。在输出时，第一行包含 来自表的列名。在输入时，第一行会被忽略。只有使用 CSV格式时才允许这个选项。
--QUOTE
指定一个数据值被引用时使用的引用字符。默认是双引号。 这必须是一个单一的单字节字符。只有使用 CSV格式时才允许这个选项。
--ESCAPE
指定应该出现在一个匹配QUOTE值的数据字符之前 的字符。默认和QUOTE值一样（这样如果引用字符 出现在数据中，它会被双写）。这必须是一个单一的单字节字符。 只有使用CSV格式时才允许这个选项
--FORCE_NOT_NULL
不要把指定列的值与空值串匹配。在空值串就是空串的默认情况下， 这意味着空串将被读作长度为零的字符串而不是空值（即使它们没有 被引用）。只有在COPY FROM中使用 CSV格式时才允许这个选项。
--FORCE_NULL
将指定列的值与空值串匹配（即使它已经被加上引号），并且在找到 匹配时将该值设置为NULL。在空值串就是空串的默认 情况下，这会把一个被引用的空串转换为 NULL。 只有在COPY FROM中使用 CSV格式时才允许这个选项。
--ENCODING
指定文件被以encoding_name编码。如果省略 这个选项，将使用当前的客户端编码。

 

二、数据库导入

1、创建测试表

[highgo@mode2 ~]$ psql highgo=# create table movie_star(user_id int,user_name varchar(40),age int,gender text); CREATE TABLE highgo=# \q

 

2、转换存有数据的excel格式为csv格式，编码原则为UTF-8,逗号分隔上传至数据库服务器家目录下，如下图所示

 

3、登录数据库，通过psql工具将excel表格数据导入到数据库对应表中。

（1）使用copy命令

highgo=# copy movie_star from '/home/highgo/movie_star.csv' with csv header; COPY 9 highgo=# select * from movie_star ;  user_id |   user_name   | age | gender ---------+---------------+-----+--------        1 | Jackie Chan   |  45 | male        2 | Gong Li       |  38 | female        3 | Brigitte Lin  |  46 | female        4 | Maggie Cheung |  39 | female        5 | Jet Li        |  41 | male        6 | Jacky Cheung  |  35 | male        7 | Chow Yun Fat  |  48 | male        8 | Donnie Yen    |  36 | male        9 | Stephen Chow  |  40 | male (9 rows)

（2）使用\copy元命令

highgo=# \copy movie_star from '/home/highgo/movie_star.csv' with csv header; COPY 9 highgo=# highgo=# highgo=# select * from movie_star ;  user_id |   user_name   | age | gender ---------+---------------+-----+--------        1 | Jackie Chan   |  45 | male        2 | Gong Li       |  38 | female        3 | Brigitte Lin  |  46 | female        4 | Maggie Cheung |  39 | female        5 | Jet Li        |  41 | male        6 | Jacky Cheung  |  35 | male        7 | Chow Yun Fat  |  48 | male        8 | Donnie Yen    |  36 | male        9 | Stephen Chow  |  40 | male        1 | Jackie Chan   |  45 | male        2 | Gong Li       |  38 | female        3 | Brigitte Lin  |  46 | female        4 | Maggie Cheung |  39 | female        5 | Jet Li        |  41 | male        6 | Jacky Cheung  |  35 | male        7 | Chow Yun Fat  |  48 | male        8 | Donnie Yen    |  36 | male        9 | Stephen Chow  |  40 | male (18 rows)

4、如果excel内数据各字段的分隔符为|，则在导入时指定DELIMITER参数为'|',excel数据如下图所示：

highgo=# create table movie_star1(user_id int,user_name varchar(100),age int,gender text,address varchar(100),remark text); CREATE TABLE highgo=# \copy movie_star1 from '/opt/HighGo5.6.5/data/movie_star_1.csv' csv header DELIMITER'|'; COPY 9 highgo=# select * from movie_star1;  user_id |   user_name   | age | gender |     address      |                 remark                  ---------+---------------+-----+--------+------------------+-----------------------------------------        1 | Jackie Chan   |  45 | male   | HK,CHINA,ASIA    | police story project A rush hour        2 | Gong Li       |  38 | female | "Singapore",ASIA |                                        +          |               |     |        |                  | Farewell My Concubine Lifetimes Living        3 | Brigitte Lin  |  46 | female | HK,CHINA,ASIA    |        4 | Maggie Cheung |  39 | female | HK,CHINA,ASIA    |        5 | Jet Li        |  41 | male   | "Singapore",ASIA | Fist of LegendOnce Upon a Time in China        6 | Jacky Cheung  |  35 | male   | HK,CHINA,ASIA    |        7 | Chow Yun Fat  |  48 | male   | HK,CHINA,ASIA    |        8 | Donnie Yen    |  36 | male   | HK,CHINA,ASIA    |        9 | Stephen Chow  |  40 | male   | HK,CHINA,ASIA    | (9 rows)

5、如果数据导出时未添加csv header参数，则导入时可忽略此参数。

highgo=# \copy movie_star1 to '/opt/HighGo5.6.5/data/movie_star_5.csv'   DELIMITER'|'; COPY 9 highgo=# truncate movie_star1; TRUNCATE TABLE highgo=# select * from movie_star1;  user_id | user_name | age | gender | address | remark ---------+-----------+-----+--------+---------+-------- (0 rows) highgo=# \copy movie_star1 from '/opt/HighGo5.6.5/data/movie_star_5.csv'  DELIMITER'|'; COPY 9 highgo=# select * from movie_star1;  user_id |   user_name   | age | gender |     address      |                 remark                  ---------+---------------+-----+--------+------------------+-----------------------------------------        1 | Jackie Chan   |  45 | male   | HK,CHINA,ASIA    | police story project A rush hour        2 | Gong Li       |  38 | female | "Singapore",ASIA | Farewell My Concubine Lifetimes Living        3 | Brigitte Lin  |  46 | female | HK,CHINA,ASIA    |        4 | Maggie Cheung |  39 | female | HK,CHINA,ASIA    |        5 | Jet Li        |  41 | male   | "Singapore",ASIA | Fist of LegendOnce Upon a Time in China        6 | Jacky Cheung  |  35 | male   | HK,CHINA,ASIA    |        7 | Chow Yun Fat  |  48 | male   | HK,CHINA,ASIA    |        8 | Donnie Yen    |  36 | male   | HK,CHINA,ASIA    |        9 | Stephen Chow  |  40 | male   | HK,CHINA,ASIA    | (9 rows)

 

4、注意事项

(1)copy命令只有超级用户可以使用，\copy元命令普通用户也可以使用；

(2)数据库内表的字段跟excel的列字段名称相同；

(3)excel每列内数据的类型不能有错误；

(4)excel的格式要保存为csv格式，编码类型为UTF-8，逗号分隔，如果使用的office版本较低，无法选择保存时的编码类型，可将excel保存为csv格式后使用记事本打开，然后文件另存为的时候选择编码类型为UTF-8。

更多详细信息请登录【瀚高技术支持平台】查看https://support.highgo.com/#/index/docContent/f11c3d6904e59f3c