是否有一种方法导入CSV到Oracle数据库与自动表创建?使用突击队

Question

我正在寻找一种方法来导入CSV到Oracle数据库没有手动表创建导入之前。有人知道解决办法吗?这是我用于预定义表的代码，它是.ctl- file的一部分，并与SQL Loader一起工作。有没有可能修改一下来解决这个问题?

load data
infile 'D:/random.csv'
into table konten
fields terminated by ';'
(kontonummer,firmenname,inhaber,rechtsform,email,kontostand)

Answer 1

试试csv2db吧。

generate选项将为您创建表。

# https://github.com/csv2db/csv2db
# drop user
echo 'drop user csvdata cascade;' | sqlplus -S system/oracle@localhost:1521/XEPDB1
# create user and grant privileges
sqlplus -S system/oracle@localhost:1521/XEPDB1 <<EOF
create user csvdata identified by load default tablespace USERS temporary tablespace TEMP quota unlimited on USERS;
grant create session, resource to csvdata;
EOF
# generate DDL
time csv2db generate --file=movies.csv --table=MOVIES | sed 's/1000/4000/' 
| sqlplus -S csvdata/load@localhost:1521/XEPDB1
# load data
time csv2db load --user=csvdata --password=load --host=localhost --port=1521 --dbname=XEPDB1 
--separator=',' --table=MOVIES --directpath 
--file=movies.csv
# what do we have?
echo 'select count(*) from movies;' | sqlplus -S csvdata/load@localhost:1521/XEPDB1

祝你好运!

Answer 2

一个选项是使用read_csvpandas的函数是python的库用于数据操作和分析的语言，如

import pandas as pd
import cx_Oracle
user    = 'hr'
password = 'hr'
host    = '192.168.56.102'
port    = '1521'
dbname  = 'myOracleDB'
con = cx_Oracle.connect(user, password, host+':'+port+'/'+dbname)
cur = con.cursor()
tab_name = 'konten'
cur.execute('SELECT COUNT(*) FROM user_tables WHERE table_name = UPPER(:1) ',[tab_name])       
exs = cur.fetchone()[0]
df = pd.read_csv(r'D:\random.csv')
col=df.columns[0].split(";")
crt=""
for k in col:
crt += ''.join(k)+' VARCHAR2(4000),'
if int(exs) == 0:
crt = 'CREATE TABLE '+tab_name+' ('+crt.rstrip(",")+')'
cur.execute(crt)
vrs=""
for i in range(0,len(col)):
vrs+= ':'+str(i+1)+','
cols=[]
sql = 'INSERT INTO '+tab_name+' VALUES('+vrs.rstrip(",")+')'
for i in range(0,len(df)):
cols.append(df.values[i][0].split(";"))
cur.executemany(sql,cols)
con.commit()
cur.close