Hi all,

I am new to mysql. I just want to know how to upload a text file into a mysql table. I read about that we can use blob/text type to upload images and big text files, I tired for online tutorials, but all of them too advance for me.

Can you people just give me an insert command to upload a test file or recommend me some good links.

this is the description of my table
+-------+---------------------+-------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| pdb | text | YES | | NULL | |
+-------+---------------------+-------+-----+---------+-------+
2 rows in set (0.00 sec)

this is my sample file.

name = 1234.pdb

ATOM 1632 C THR A 208 29.473 1.192 44.944 1.00 24.82 C
ATOM 1633 O THR A 208 29.022 1.418 46.069 1.00 24.52 O
ATOM 1634 CB THR A 208 31.894 1.569 44.285 1.00 24.43 C
ATOM 1635 OG1 THR A 208 33.184 0.953 44.333 1.00 24.28 O
ATOM 1636 CG2 THR A 208 32.055 2.738 45.266 1.00 24.47 C
ATOM 1637 N LEU A 209 28.796 1.441 43.827 1.00 24.52 N
ATOM 1638 CA LEU A 209 27.464 2.060 43.860 1.00 25.84 C
ATOM 1639 C LEU A 209 26.432 1.187 44.565 1.00 26.45 C
ATOM 1640 O LEU A 209 25.498 1.697 45.196 1.00 26.84 O
ATOM 1641 CB LEU A 209 26.993 2.414 42.447 1.00 24.84 C

thanks

so what defines the columns in your data file
usually a file would arrive with some clearly defined seperator (often a comma), sometimes text is delimited by a "
if you used a space you would have 12 columns, you've defined 6
I think you need to revisait the source for this data and try to resolve what columns are required, and which aren't


a really really clunky way of doing it would be to import the whole file, using a space as a seperator. and then use a series of SQL update queries to massage the data into the form that you need.

failing that you will probably have to write some form of program or script (in what ever development environment you use) to do the reformatting you require

actually i defined only 2 columns (the above one is description of my table), my table looks like this

+---------------+---------------------------------------------------------------------+
| name | pdb |
+---------------+---------------------------------------------------------------------+
| 1234.pdb | ~path/1234.pdb |
+---------------+---------------------------------------------------------------------+

i just tried by using INSERT command

insert into pdb(name, pdb) values('1234.pdb', '~/path/1234.pdb');

but after this I can only able to see the path of the table in the pdb column. I thought I can upload a file in a single row itself with its name in one column and actual file in another column.

Do I need to split my file by column and row and then I have to store it in database ?

Try the load data infile statement.

Must admit though, you appear to have issues with either your input file or your table - the file has 12 columns and your trying to import the data into a table with 2 columns.

Mike

please consider my text file example as an image. now how can I upload an image into the table.

wannac

so you want to add the filename as the first column, and then import everythign else into the other column?

so do an import then add the filename with an update query
I'd suggest you declare your table as other first, then filename


import the data load data infile works, or look at the MySQL Migration toolkit from the MySQL site.

then immedaitely update the filename

update mytable set filename="blahdi.blah" where filename="";

Hi wannac,

I have the same problem (also new to dbs). Have you got anywhere with this?

Also, people are confusing your request. You want to store a text file INSIDE a table field (using one of the TEXT datatypes) and they keep answering how to load data FROM a text file to populate a table. Is that correct?

I've posted a similar request to yourself on the MySQL forum, but as yet, no answers. I've tried it myself, but only get errors.

This is what I posted on the MySQL forum, and also the error I'm getting.

====
Hello!

This is driving me mad! I'm trying to store a number of small (<100kb) text files inside a MySQL database. It's the first time I've used any of the TEXT data types. I'm pretty new to db's in general. Although the reference manual shows you the data types and the associated sizes, it doesn't give any clues on how to populate the field. I've tried googleing for the info, but that mainly returns people asking how to populate a database from a text file, and a few links on places like expert exchange where you can't see the answers unless you join.

Do I need to prep the text file before I insert it? Does it need to be a single long line? Can it have <CR> and <LF> in it? What about quotes and single quotes (guessing that's a no-no).

Just as an example, I've tried inserting part of my /etc/hosts file. Here's the error I'm getting and also a desc of the table I'm using:
====
mysql> insert into test (displayname, vmx) values 'JL_Windows', '10.10.1.50 bl25p-s1 bl25p-s1.example.local
'> 10.10.1.147 cisco_3020_left
'> 10.10.1.148 cisco_3020_right
'> 10.10.1.5 cisco_6500_left
'> 10.10.1.6 cisco_6500_right
'> 10.10.1.141 cisco_rack1
'> ';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''JL_Windows', '10.10.1.50 bl25p-s1 bl25p-s1.example.local
10.10.1.147' at line 1
mysql> desc test;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| displayname | varchar(50) | YES | | NULL | |
| vmx | longtext | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)

mysql>
====

Any help gratefully received!!
Jules.

you forgot the parentheses around the values

insert into test (displayname, vmx) values ( 'JL_Windows', '10.10.1.50 bl25p-s1 bl25p-s1.example.local' )