Import Excel data into the pgAdmin3

Source: Internet
Author: User
Tags psql

1. Install the Perl software, install the configuration environment variable: Add the Perl/bin file path after the path path of the system variable, separated by a number, end

2. Place the data (without the field) with the Perl statement on the e-disk; The following Perl statement creates the table

#!/usr/bin/perl-wuse strict;my $host = ' localhost '; my $port = ' 5432 '; my $db = ' test1 ';//create a new test database in the database my $user = ' Postgre s '; my $connect _string = "psql-a-h $host-D $db-U $user"; my $cre _string= ' acct_no varchar, org_code varchar, cust_i D varchar (application_no), Market_active_code varchar (+), open_acct_date date,cancel_acct_date date, Invalid_date date,branch_code varchar (send_card_type_code), Issue_channel_code varchar (+), reference _code varchar (acct_date), Card_count varchar (+), 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 (+), last_term_balance Numeric (100,2), Payment_amount Numeric (100,2), Activity_status_code varchar (acct_status_code), varchar (overdue_status_code), Lock_staT_code varchar, lock_off_date date,year_fee varchar (+), Avoid_year_fee_flag varchar (+), Avoid_ratefee_flag varchar (+), Avoid_limit_fee_flag varchar (avoid_late_fee_flag), Avoid_transfer_fee_flag varchar (50), Avoid_domestic_atm_flag varchar (avoid_oversea_atm_flag), Share_cust_limit_flag varchar (+), override _cust_limit_flag varchar (auto_repay_type_code), Credit_limit varchar (+), Recent_limit_adj_date date, Curr_limit varchar (take_cash_limitvarchar), Net_charge_limit varchar (+), temp_credit_limit varchar, temp _limit_adj_count varchar (over_limit_count), Overdue_count varchar (+), overdue_0_29_day_balance Numeric (100,2), Overdue_0_29_day_count varchar (+), overdue_30_59_day_balance Numeric (100,2), Overdue_30_59_day_ Countvarchar (), Overdue_60_89_day_balance Numeric (100,2), Overdue_60_89_day_countvarchar (+), Overdue_90_119_day _balance Numeric (100,2), Overdue_90_119_day_count varchar (+), overdue_120_149_day_balance Numeric (100,2), overDue_120_149_day_count varchar (+), 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 (+), overdue_up_210_day_balance Numeric (100,2), Overdue_up_210_day_count varchar (+), 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 (acct_status_1), acct_status_2 varchar (+), Acct_status_3 varchar (+), Acct_status_4 varchar (50), Acct_status_5 varchar (+), acct_status_6 varchar (acct_status_7), acct_status_8 varchar ), Acct_status_9 varchar (acct_status_10), Acct_status_11 varchar (+), Acct_status_12 varchar (+), acct_ status_13 varchar (acct_status_14), acct_status_15 varchar (+), acct_status_16 varchar (+), Acct_status _17 varchar (acct_status_18), acct_status_19 varchar (+), acct_status_20 varchar (50), acct_status_21 varchar (+), acct_status_22 varchar (+), acct_status_23 varchar (+), acct_status_24 varchar (50), Acct_3_mm_overdue_value varchar (acct_6_mm_overdue_value), acct_check_cycle varchar (+), open (PSQL, " | $connect _string ");p rint psql<<endpsql;set client_encoding =gbk;--drop schema src cascade;--drop schema Temp Cascade Create TABLE stage.acct_6_30 ($cre _string);//new Stage mode copy Acct.acct1 from ' E:/data/acct1.csv ' with delimiter ', ' null ' ; Endpsqlclose (PSQL);

Then use the CMD console to execute the following code

1.c/d/e/f://find the root directory of the Perl statement script
2.cd perl1//Find the Perl folder
1.perl//Execute 1.perl PL file

Finally refresh in the database to see if a new table

Import Excel data into the pgAdmin3

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.