将 CSV 文件导入 oracle DB
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4085444/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Import CSV file to oracle DB
提问by nath
Is there a simple Java library or approach that will take a SQL query and load data in a CSV file to oracle database. Pls help
是否有一个简单的 Java 库或方法可以执行 SQL 查询并将 CSV 文件中的数据加载到 oracle 数据库。请帮忙
采纳答案by HeDinges
For similar tasks I usually use Groovy scriptsas it's really easy and quick to write and runs on the JVM off course.
对于类似的任务，我通常使用Groovy 脚本，因为它在 JVM 上编写和运行非常简单快捷。
...an example:
...一个例子：
import groovy.sql.Sql
def file1 = new File(/C:\Documents and Settings\USER\Desktop\Book1.csv/)
def reader = new FileReader(file1)
def sql = Sql.newInstance("jdbc:oracle:thin:@XXXXXX:XXXX:XXX", "SCHEMA",
"USER", "oracle.jdbc.driver.OracleDriver")
reader.each { line ->
fields = line.split(';')
sql.executeInsert("insert into YOUR_TABLE values(${fields[0]},${fields[1]},${fields[2]})")
}
It's a basic example, if you have double quotes and semi columns in your csv you will probably want to use something like OpenCSVto handle that.
回答by dogbane
You don't have to use Java to load a data file into a table unless it is absolutely necessary. Instead, I'd recommend Oracle's command-line SQL*Loaderutility which was designed specially for this purpose.
除非绝对必要，否则您不必使用 Java 将数据文件加载到表中。相反，我会推荐 Oracle 的命令行SQL*Loader实用程序，它是专门为此目的而设计的。
回答by gautamlakum
I think this tool will help you for any type of database import-export problem.
我认为这个工具可以帮助您解决任何类型的数据库导入导出问题。
回答by greuze
You could transform each line in the CSV with regular expressions, to make an insert query, and then send to Oracle (with JDBC).
您可以使用正则表达式转换 CSV 中的每一行，以进行插入查询，然后发送到 Oracle（使用 JDBC）。
回答by elwood
Do you have that CSV in a file on the database server or can you store it there? Then you may try to have Oracle open it by declaring a DIRECTORY object for the path the file is in and then create an EXTERNAL TABLE which you can query in SQL afterwards. Oracle does the parsing of the file for you.
您在数据库服务器上的文件中是否有该 CSV 文件，或者您可以将其存储在那里？然后，您可以尝试通过为文件所在的路径声明一个 DIRECTORY 对象来让 Oracle 打开它，然后创建一个 EXTERNAL TABLE，之后您可以在 SQL 中查询该表。Oracle 会为您解析文件。
回答by Alex B
If you are open to Python you can do bulk load using SQL*Loader
如果您对 Python 开放，则可以使用 SQL*Loader 进行批量加载
loadConf=('sqlldr userid=%s DATA=%s control=%s LOG=%s.log BAD=%s.bad DISCARD=%s.dsc' % (userid,datafile, ctlfile,fn,fn,fn)).split(' ')
p = Popen(loadConf, stdin=PIPE, stdout=PIPE, stderr=PIPE, shell=False, env=os.environ)
output, err = p.communicate()
It's will be much faster that row insert.
行插入会快得多。
I uploaded basic working example here.
我在这里上传了基本的工作示例。
