[MySQL]: Load data infile
[root@dbversity tmp]# ll -lhtr CSV_*
-rw-r–r– 1 oracle dba 298K Nov 26 02:39 CSV_DBV_HLDNGS.csv
-rw-r–r– 1 oracle dba 489K Nov 26 02:40 CSV_DBV_DATA.csv
-rw-r–r– 1 oracle dba 86K Nov 26 02:40 CSV_PS_RELN_BSNL.csv
[root@dbversity tmp]#
[root@dbversity tmp]# dos2unix CSV_*
dos2unix: converting file CSV_DBV_HLDNGS.csv to UNIX format …
dos2unix: converting file CSV_DBV_DATA.csv to UNIX format …
dos2unix: converting file CSV_PS_RELN_BSNL.csv to UNIX format …
[root@dbversity tmp]#
[root@dbversity tmp]# head -2 CSV_DBV_HLDNGS.csv
BRN_CD,LIABILITY_CD,ISIN_NBR,RSFS_STK_EXCHG,CCY_CD,SETTLED_SHARES_NBR,UNSETTLE_BUY_SHARES_NBR,UNSETTLE_SELL_SHARES_NBR,TOTAL_SHARES,BLOCKED_SHARES_NBR,AVG_PRICE_RT,EG_TITLE,EG_RR_CD,WAARR_SCORE_CD,BAND_COLOR_CD,SEDOL_CD,STOCK_NAME,BLOOMBERG_TICKER,MKT_VALUE,STOCK_EXCHANGE_CD,RELN_KEY,ACCT_KEY,ENTL_KEY,EG_NBR,ISRT_TS,UPD_TS,ISRT_USER_ID,UPD_USER_ID,ACCT_STATUS,ASSET_CLASS_CD
760,773429,SG1X34941168,SP,SGD,400000,0,0,400000,0,100,SM200105907 SR004214067-0003 SR004214067-0003,6,3.72,Blue,,5.05% UOB LTD PE,,329503.9586,,2.05E+13,2.01E+13,2.03E+13,SR004214067-0003,21-MAR-14 10.48.57.000000000 AM,21-MAR-14 10.48.57.000000000 AM,TBD,TBD,00,Y
[root@dbversity tmp]#
[root@dbversity tmp]# head -2 CSV_DBV_DATA.csv
REUTER_TICKER,SB_RATING,INSTRUMENT_CCY,TARGET_PRICE,MARKET_CAP,LAST_RATING_UPDATE_DTTM,LAST_RISK_UPDATE_DTTM,LAST_TP_UPDATE_DTTM,PREV_SB_RATING_CD,PREV_SB_RATING_EFF_DT,PREV_TARGET_PRICE_RT,PREV_TARGET_PRICE_RT_EFF_DT,BLOOMBERG_TICKER,NAME,STOCK_MKT,CHG_TARGET_PRICE_RT,CLOSING_PRICE_RT,TARGET_CLOSING_RT,_DBV_DT,CLIENT_HOLDING_FLG,UPGRADE_DOWNGRADE_FLG,UPGRADE_DOWNGRADE_TYPE,PREV_CLOSING_PRICE_RT,CHG_CLOSING_PRICE_RT,MOMENTUM_SCORE,REAL_VALUE_SCORE,QUADRANT_LEVEL,COMPANY_DESC_TXT,INVEST_STGY_TXT,VALUATION_TXT,RISK_TXT,PE_RATIO,ISIN_NBR,LAST_CP_UPDATE_DTTM,CHG_TARGET_PRICE_FLG,CHG_SB_RATING_FLG,CHG_CLOSING_PRICE_FLG,SEDOL_CD,STOCK_EXCHANGE_CD,RSFS_STK_EXCHG
GFG.BA,3H,ARS,10.2,3185,29-NOV-11,31-DEC-09,09-JUN-14,3H,29-NOV-11,7.2,26-NOV-13,GGAL AR Equity,Galicia,BUE,41.67,20.7,-50.72,09-JUN-14,N,U,P,20.1,2.99,,,,”””Founded in 1905, XTXTX.”””,11.18,ARP495251018,26-SEP-14,N,N,Y,2613310,,
[root@dbversity tmp]#
[root@dbversity tmp]#
[root@dbversity tmp]#
[root@dbversity tmp]# head -2 CSV_PS_RELN_BSNL.csv
USER_GEID_ID,RELN_KEY_ID,RELN_NR,ISRT_TS,UPD_TS,ISRT_USER_ID,UPD_USER_ID
1010290953,2.05E+13,SR251732561,12-MAR-14 07.48.08.000000000 AM,12-MAR-14 07.48.08.000000000 AM,TBD,TBD
[root@dbversity tmp]#
[root@dbversity tmp]# mysql -u root -p -D database dbversit
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.5.21 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>
mysql>
mysql> show tables;
+——————+
| Tables_in_dbversit |
+——————+
| IV_DBV_HOLDINGS |
| IV_DBV_DATA |
| PS_RELN_BSNL |
+——————+
3 rows in set (0.00 sec)
mysql>
mysql> truncate table IV_DBV_HOLDINGS;
Query OK, 0 rows affected (0.03 sec)
mysql> truncate table IV_DBV_DATA;
Query OK, 0 rows affected (0.01 sec)
mysql> truncate table PS_RELN_BSNL;
Query OK, 0 rows affected (0.01 sec)
mysql> load data local infile “/tmp/CSV_DBV_HLDNGS.csv” into table IV_DBV_HOLDINGS fields terminated by ‘,’ ignore 1 lines;
Query OK, 1041 rows affected, 0 warnings (0.11 sec)
Records: 1041 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from IV_DBV_HOLDINGS limit 1\G
*************************** 1. row ***************************
BRN_CD: 760
LIABILITY_CD: 776464
ISIN_NBR: AU000000AMC4
RSFS_STK_EXCHG: AU
CCY_CD: AUD
SETTLED_SHARES_NBR: 70080.000000
UNSETTLE_BUY_SHARES_NBR: 0.000000
UNSETTLE_SELL_SHARES_NBR: 0.000000
TOTAL_SHARES: 70080.000000
BLOCKED_SHARES_NBR: 0.000000
AVG_PRICE_RT: 7.7101000
EG_TITLE: SM200948575 SR250629341-0001 SR250629341-0001
EG_RR_CD: 6
WAARR_SCORE_CD: 3.57
BAND_COLOR_CD: Blue
SEDOL_CD: 606660
STOCK_NAME: AMCOR LTD
BLOOMBERG_TICKER: AMC AT Equity
MKT_VALUE: 612483.7991000
STOCK_EXCHANGE_CD:
RELN_KEY: 20500000000000
ACCT_KEY: 20100000000000
ENTL_KEY: 20300000000000
EG_NBR: SR250629341-0001
ISRT_TS: 0000-00-00 00:00:00
UPD_TS: 0000-00-00 00:00:00
ISRT_USER_ID: TBD
UPD_USER_ID: TBD
ACCT_STATUS: 00
ASSET_CLASS_CD: EQUTY
1 row in set (0.00 sec)
mysql>
mysql>
mysql> load data local infile “/tmp/CSV_DBV_DATA.csv” into table IV_DBV_DATA fields terminated by ‘,’ ignore 1 lines;
Query OK, 129 rows affected, 55009 warnings (0.21 sec)
Records: 3233 Deleted: 0 Skipped: 3104 Warnings: 55009
mysql> load data local infile “/tmp/CSV_PS_RELN_BSNL.csv” into table PS_RELN_BSNL fields terminated by ‘,’ ignore 1 lines;
Query OK, 1 row affected, 1672 warnings (0.07 sec)
Records: 836 Deleted: 0 Skipped: 835 Warnings: 1672
mysql> show warnings;