(8.0.31)
vim expcsv.conf
tab1
tab2
vim expcsv.sh
#!/bin/bash
OUTFILE="expcsv_tables.sh"
PASS="'password'"
USER="root"
DB="test"
DIR=$(pwd)
: > "${OUTFILE}"
echo "#!/bin/bash" >> "${OUTFILE}"
for TAB in $(cat expcsv.conf) ; do
echo "rm -rf ${DIR}/${TAB}.csv" >> "${OUTFILE}"
echo "MYSQL_PWD=${PASS} mysql -u ${USER} ${DB} <<'EOF' " >> "${OUTFILE}"
echo "select * into outfile '${DIR}/${TAB}.csv' fields terminated by ',' optionally enclosed by '"'"'"' from ${TAB};" >> "${OUTFILE}"
echo "EOF" >> "${OUTFILE}"
done
chmod +x "${OUTFILE}"
./"${OUTFILE}"
exit 0
vim impcsv.conf
tab1
tab2
vim impcsv.sh
#!/bin/bash
OUTFILE="impcsv_tables.sh"
PASS="'password'"
USER="root"
DB="test"
DIR=$(pwd)
: > "${OUTFILE}"
echo "#!/bin/bash" >> "${OUTFILE}"
for TAB in $(cat impcsv.conf) ; do
echo "MYSQL_PWD=${PASS} mysql -u ${USER} ${DB} <<'EOF' " >> "${OUTFILE}"
echo "truncate table ${TAB};" >> "${OUTFILE}"
echo "load data infile '${DIR}/${TAB}.csv' into table ${TAB} fields terminated by ',' optionally enclosed by '"'"'"';" >> "${OUTFILE}"
echo "EOF" >> "${OUTFILE}"
done
chmod +x "${OUTFILE}"
./"${OUTFILE}"
exit 0
(19c)
vim expcsv.conf
TAB1
TAB2
vim expcsv.sh
#!/bin/bash
OUTFILE="expcsv_tables.sh"
CONN="test/test@pdb1"
: > "${OUTFILE}"
echo "#!/bin/bash" >> "${OUTFILE}"
for TAB in $(cat expcsv.conf) ; do
echo "sqlplus -S ${CONN} <<'EOF' > /dev/null" >> "${OUTFILE}"
echo "alter session set nls_timestamp_format = 'YYYY/MM/DD HH24:MI:SS.FF9';" >> "${OUTFILE}"
echo "alter session set nls_date_format = 'YYYY/MM/DD HH24:MI:SS';" >> "${OUTFILE}"
echo "set head off" >> "${OUTFILE}"
echo "set trimspool on" >> "${OUTFILE}"
echo "set echo off" >> "${OUTFILE}"
echo "set feed off" >> "${OUTFILE}"
echo "set long 2000000000" >> "${OUTFILE}"
echo "set longchunksize 2000000000" >> "${OUTFILE}"
echo "set linesize 32767" >> "${OUTFILE}"
echo "set pagesize 0" >> "${OUTFILE}"
echo "set markup csv on" >> "${OUTFILE}"
echo "spool ${TAB}.csv" >> "${OUTFILE}"
echo "select * from ${TAB};" >> "${OUTFILE}"
echo "spool off" >> "${OUTFILE}"
echo "EOF" >> "${OUTFILE}"
done
chmod +x "${OUTFILE}"
./"${OUTFILE}"
exit 0
-- 制御ファイル作成用プロシージャ
set serveroutput on
create or replace procedure proc_make_ctl(p_tabname in varchar2)
authid current_user
as
cursor cur1 is select table_name,column_name,data_type,data_length,column_id
from user_tab_columns
where table_name = p_tabname
order by column_id;
wk1 number;
va1 varchar2(4000);
begin
dbms_output.put_line('LOAD DATA');
dbms_output.put_line('INFILE '''||p_tabname||'.csv'' "str ''\n''"');
dbms_output.put_line('truncate');
dbms_output.put_line('INTO TABLE '||p_tabname);
dbms_output.put_line('FIELDS TERMINATED BY '',''');
dbms_output.put_line('OPTIONALLY ENCLOSED BY ''"'' AND ''"''');
dbms_output.put_line('TRAILING NULLCOLS ( ');
select count(*) into wk1 from user_tab_columns where table_name = p_tabname;
for c1 in cur1 loop
if c1.column_id = wk1 then
va1 := ' ) ';
else
va1 := ' , ';
end if;
if c1.data_type in ('CHAR','VARCHAR2') then
dbms_output.put_line(c1.column_name ||' CHAR ('|| c1.data_length || ') ' || va1);
elsif c1.data_type in ('DATE') then
dbms_output.put_line(c1.column_name ||' DATE "YYYY/MM/DD HH24:MI:SS" ' || va1);
elsif c1.data_type like 'TIMESTAMP%' then
dbms_output.put_line(c1.column_name ||' TIMESTAMP "YYYY/MM/DD HH24:MI:SS.FF9" ' || va1);
else
dbms_output.put_line(c1.column_name || va1);
end if;
end loop;
end;
/
sho error
vim impcsv.conf
TAB1
TAB2
vim impcsv.sh
#!/bin/bash
OUTFILE="impcsv_tables.sh"
CONN="test/test@pdb1"
: > "${OUTFILE}"
echo "#!/bin/bash" >> "${OUTFILE}"
for TAB in $(cat impcsv.conf) ; do
echo "sqlplus -S ${CONN} <<'EOF' > /dev/null" >> "${OUTFILE}"
echo "set head off" >> "${OUTFILE}"
echo "set trimspool on" >> "${OUTFILE}"
echo "set echo off" >> "${OUTFILE}"
echo "set feed off" >> "${OUTFILE}"
echo "set long 2000000000" >> "${OUTFILE}"
echo "set longchunksize 2000000000" >> "${OUTFILE}"
echo "set linesize 32767" >> "${OUTFILE}"
echo "set pagesize 0" >> "${OUTFILE}"
echo "set serveroutput on" >> "${OUTFILE}"
echo "spool ${TAB}.ctl" >> "${OUTFILE}"
echo "exec proc_make_ctl('${TAB}');" >> "${OUTFILE}"
echo "spool off" >> "${OUTFILE}"
echo "EOF" >> "${OUTFILE}"
echo "sqlldr ${CONN} control=${TAB}.ctl log=${TAB}.log" >> "${OUTFILE}"
done
chmod +x "${OUTFILE}"
./"${OUTFILE}"
exit 0
(15)
vim expcsv.conf
tab1
tab2
vim expcsv.sh
#!/bin/bash
OUTFILE="expcsv_tables.sh"
DB="test"
DIR=$(pwd)
: > "${OUTFILE}"
echo "#!/bin/bash" >> "${OUTFILE}"
for TAB in $(cat expcsv.conf) ; do
echo "psql ${DB} <<'EOF'" >> "${OUTFILE}"
echo "\copy ${TAB} to '${DIR}/${TAB}.csv' with ( format csv, null '', quote '"'"'"' );" >> "${OUTFILE}"
echo "EOF" >> "${OUTFILE}"
done
chmod +x "${OUTFILE}"
./"${OUTFILE}"
exit 0
vim impcsv.conf
tab1
tab2
vim impcsv.sh
#!/bin/bash
OUTFILE="impcsv_tables.sh"
DB="test"
DIR=$(pwd)
: > "${OUTFILE}"
echo "#!/bin/bash" >> "${OUTFILE}"
for TAB in $(cat impcsv.conf) ; do
echo "psql ${DB} <<'EOF'" >> "${OUTFILE}"
echo "truncate table ${TAB};" >> "${OUTFILE}"
echo "\copy ${TAB} from '${DIR}/${TAB}.csv' with ( format csv, null '', quote '"'"'"' );" >> "${OUTFILE}"
echo "EOF" >> "${OUTFILE}"
done
chmod +x "${OUTFILE}"
./"${OUTFILE}"
exit 0
(2019)
OS: CentOS7
vim expcsv.conf
tab1
tab2
vim expcsv.sh
#!/bin/bash
OUTFILE="expcsv_tables.sh"
HOST="localhost"
DB="test"
USER="sa"
PASS="'password'"
: > "${OUTFILE}"
for TAB in $(cat expcsv.conf) ; do
echo "bcp dbo.${TAB} out ${TAB}.csv -S ${HOST} -d ${DB} -U ${USER} -P${PASS} -c -t," >> "${OUTFILE}"
done
chmod +x "${OUTFILE}"
./"${OUTFILE}"
exit 0
vim impcsv.conf
tab1
tab2
vim impcsv.sh
#!/bin/bash
OUTFILE="impcsv_tables.sh"
HOST="localhost"
DB="test"
USER="sa"
PASS="'password'"
: > "${OUTFILE}"
for TAB in $(cat impcsv.conf) ; do
echo "sqlcmd -S ${HOST} -d ${DB} -U ${USER} -P${PASS} -Q "'"'"truncate table ${TAB}"'"' >> "${OUTFILE}"
echo "bcp dbo.${TAB} in ${TAB}.csv -S ${HOST} -d ${DB} -U ${USER} -P${PASS} -c -t," >> "${OUTFILE}"
done
chmod +x "${OUTFILE}"
./"${OUTFILE}"
exit 0
