首页 > 代码库 > 把excel数据导入pgAdmin3
把excel数据导入pgAdmin3
1.安装perl软件,安装完配置环境变量:在系统变量的path路径后加入perl/bin的文件路径,用;号分隔、结尾
2.把data(不带字段的数据)跟perl的语句放在E盘;以下perl语句创建表
#!/usr/bin/perl -w use strict; my $host = ‘localhost‘; my $port = ‘5432‘; my $db = ‘test1‘;//在数据库中新建test数据库 my $user = ‘postgres‘; my $connect_string = "psql -a -h $host -d $db -U $user"; my $cre_string=‘acct_no varchar(50), org_code varchar(50), cust_id varchar(50), application_no varchar(50), market_active_code varchar(50), open_acct_date date, cancel_acct_date date, invalid_date date, branch_code varchar(50), send_card_type_code varchar(50), issue_channel_code varchar(50), reference_code varchar(50), acct_date varchar(50), card_count varchar(50), curr_balance Numeric(100,2), curr_cash_balance Numeric(100,2), curr_retail_balance Numeric(100,2), curr_instalment_balance Numeric(100,2), credit_amount Numeric(100,2), debit_amount Numeric(100,2), hand_fee_amt Numeric(100,2), late_fee_amt Numeric(100,2), year_fee_amt varchar(50), over_limit_amt varchar(50), last_term_balance Numeric(100,2), payment_amount Numeric(100,2), activity_status_code varchar(50), acct_status_code varchar(50), overdue_status_code varchar(50), lock_stat_code varchar(50), lock_off_date date, year_fee varchar(50), avoid_year_fee_flag varchar(50), avoid_ratefee_flag varchar(50), avoid_limit_fee_flag varchar(50), avoid_late_fee_flag varchar(50), avoid_transfer_fee_flag varchar(50), avoid_domestic_atm_flag varchar(50), avoid_oversea_atm_flag varchar(50), share_cust_limit_flag varchar(50), override_cust_limit_flag varchar(50), auto_repay_type_code varchar(50), credit_limit varchar(50), recent_limit_adj_date date, curr_limit varchar(50), take_cash_limit varchar(50), net_charge_limit varchar(50), temp_credit_limit varchar(50), temp_limit_adj_count varchar(50), over_limit_count varchar(50), overdue_count varchar(50), overdue_0_29_day_balance Numeric(100,2), overdue_0_29_day_count varchar(50), overdue_30_59_day_balance Numeric(100,2), overdue_30_59_day_count varchar(50), overdue_60_89_day_balance Numeric(100,2), overdue_60_89_day_count varchar(50), overdue_90_119_day_balance Numeric(100,2), overdue_90_119_day_count varchar(50), overdue_120_149_day_balance Numeric(100,2), overdue_120_149_day_count varchar(50), overdue_150_179_day_balance Numeric(100,2), overdue_150_179_day_count varchar(50), overdue_180_209_day_balance Numeric(100,2), overdue_180_209_day_count varchar(50), overdue_up_210_day_balance Numeric(100,2), overdue_up_210_day_count varchar(50), first_trans_post_date date, last_cons_post_date date, last_cash_post_date date, last_pay_post_date date, last_balan_chg_date date, last_overdue_date date, transfer_date date, transfer_acct_no varchar(50), acct_status_1 varchar(50), acct_status_2 varchar(50), acct_status_3 varchar(50), acct_status_4 varchar(50), acct_status_5 varchar(50), acct_status_6 varchar(50), acct_status_7 varchar(50), acct_status_8 varchar(50), acct_status_9 varchar(50), acct_status_10 varchar(50), acct_status_11 varchar(50), acct_status_12 varchar(50), acct_status_13 varchar(50), acct_status_14 varchar(50), acct_status_15 varchar(50), acct_status_16 varchar(50), acct_status_17 varchar(50), acct_status_18 varchar(50), acct_status_19 varchar(50), acct_status_20 varchar(50), acct_status_21 varchar(50), acct_status_22 varchar(50), acct_status_23 varchar(50), acct_status_24 varchar(50), acct_3_mm_overdue_value varchar(50), acct_6_mm_overdue_value varchar(50), acct_check_cycle varchar(50) ‘; open(PSQL,"|$connect_string"); print PSQL<<ENDPSQL; set client_encoding =GBK; --drop schema src cascade; --drop schema temp cascade; CREATE TABLE stage.acct_6_30($cre_string);//新建stage模式 copy acct.acct1 from ‘E:/data/acct1.csv‘ with delimiter ‘,‘ null ‘‘; ENDPSQL close(PSQL);
再用 cmd控制台执行以下代码
1.C/D/E/F://找到perl语句脚本的根目录
2.cd perl1//找到perl文件夹
1.perl//执行1.perl的pl文件
最后在数据库中刷新,看看是否新建表
把excel数据导入pgAdmin3
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。