如何将数据从txt文件写入数据库？

If I have a txt with a certain number of rows and column (number of columns unknown at the beginning, columns are separated by tab), how can I export the data into the database? I have managed to iterate through the first row to count the number of columns and create a table accordingly but now I need to go through each row and insert the data into the respective column. How can I do that?

如果我有一个具有一定数量的行和列的txt(开头未知的列数,列由制表符分隔),我如何将数据导出到数据库中?我已设法迭代第一行来计算列数并相应地创建一个表,但现在我需要遍历每一行并将数据插入相应的列。我怎样才能做到这一点?

Example of the txt file:

txt文件的示例:

Name Size Population GDP
aa 2344 1234 12
bb 2121 3232 15
... ... .. .. 
.. .. .. ..

The table has been created:

该表已创建:

CREATE TABLE random id INT, Name char(20), Size INT, Population INT, GDP INT

4 个解决方案

#1

The difficult part is reading in the text fields. According to your definition, the field titles are separated by spaces. Is this true for the text fields?

困难的部分是阅读文本字段。根据您的定义,字段标题由空格分隔。这对于文本字段是否正确?

A generic process is:

一般过程是:

Create an SQL CREATE statement from the header text.
Execute the SQL statement.
While reading a line of text doesn't fail do
    Parse the text into variables.
    Create an SQL INSERT statement using field names and values from the variables.
    Execute the SQL statement.
End-While

Another solution is to convert the TXT file into tab or comma separated fields. Check your database documentation to see if there is a function for loading files and also discover the characters used for separating columns.

另一种解决方案是将TXT文件转换为制表符或逗号分隔的字段。检查数据库文档以查看是否存在用于加载文件的函数,还发现用于分隔列的字符。

If you need specific help, please ask a more specific or detailed question.

如果您需要特定帮助,请询问更具体或详细的问题。

#2

Using PostgreSQL's COPY, command, something like:

使用PostgreSQL的COPY命令,例如:

COPY random FROM 'filename' WITH DELIMITER '\t'

#3

something like this might work. basic idea is to use print statements to transform the line into SQL commannds. then you can execute these commands using a sql command interpreter.

这样的事情可能有用。基本思想是使用print语句将行转换为SQL命令。然后你可以使用sql命令解释器执行这些命令。

cat textfile.txt | sed 's/^\([^ ]*\) /'\1' /; s/[ \t]+/,/g;' | awk '($NR!=1) {print "INSERT INTO random (Name,size,population,gdp) VALUES (" $0 ");" }' > sqlcommands.txt

for the unknown number of columns, this might work.

对于未知数量的列,这可能会起作用。

cat textfile.txt | sed 's/^\([^ ]*\) /'\1' /; s/[ \t]+/,/g;' | awk '($NR!=1) {print "INSERT INTO random VALUES (ID," $0 ");" }' > sqlcommands.txt

replace ID with the id value needed. but you will need to execute it separately for each ID value.

用所需的id值替换ID。但是您需要为每个ID值单独执行它。

#4

I work with Sybase where "bcp" utility does this. Quick google on "postgres bcp" brings up this:

我使用Sybase,其中“bcp”实用程序执行此操作。关于“postgres bcp”的快速谷歌提出了这个:

http://lists.plug.phoenix.az.us/pipermail/plug-devel/2000-October/000103.html

I realize its not the best answer, but good enough to get you going, I hope.

我希望,我意识到这不是最好的答案,但足以让你前进。

Oh, and you may need to change your text format, make it comma or tab-delimited. Use sed for that.

哦,您可能需要更改文本格式,使其逗号或制表符分隔。使用sed。

#1