How can I write data from txt file to database?

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?

Example of the txt file:

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 

---

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.

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

---

Using PostgreSQL's COPY, command, something like:

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

---

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.

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.

---

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

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.