Adding data from text file to mysql

[agent007]

hi,

How do I add data from a text file to a database in mysql? The site uses phpmyadmin as the frontend...Do I have to specify the name of the column in the text file? and what are the settings for the line terminators? Am using vi as the text editor.

thanks..

Right now, the text file looks something like this....I've not mentioned the column name. I just cannot figure out phpmyadmin.....

data
data
data

[Void Main]

You don't have shell access? If you do I usually use the "LOAD DATA" SQL statement which phpMyAdmin actually incorporates into the interface. In phpMyAdmin click on the table name in the database menu on the left, then on the right scroll to the bottom of the page where you will find a link called "Insert data from a textfile into table". This is nothing more than a GUI wrapper for the "LOAD data" statement and allows you to select a local file to import.

[agent007]

hi void,

No, I dont have shell access....I clicked on the "insert data from a text file" so now what? what should the line terminators be? This stupid fronted has so many options unlike the command line tool....I submitted the data, but something must have gone wrong...cause the data did not show in the query. I then emptied the table...

I just need to know the settings for the line terminators and other options...

thanks..

In phpMyAdmin click on the table name in the database menu on the left, then on the right scroll to the bottom of the page where you will find a link called "Insert data from a textfile into table".

[Void Main]

I clicked on the "insert data from a text file" so now what? what should the line terminators be? This stupid fronted has so many options unlike the command line tool....I submitted the data, but something must have gone wrong...cause the data did not show in the query. I then emptied the table...

I just need to know the settings for the line terminators and other options...

Only you can answer those questions since only you have the data file and the database. The page has very good descriptions for everything and if the short descriptions are not clear enough then the "Documentation" link at the bottom is quite detailed. If you created your data file in Linux with VIM then your line terminator would be "\n" (*NIX format). If you created your data file in DOS/Windows with something like notepad then your line terminator would be "\r\n" (DOS format). But if your data and table are laid out as you say then you shouldn't have to change any of those, just select the file and press "Submit". If you have multiple columns and only want the data to go into one column then specify the column name on the form where it says "Column Names".

If you can't get it, upload your data file to my FTP server (voidmain.is-a-geek.net, /upload directory) and your table SCHEMA and I'll tell you how to fill out the blanks. To get the SCHEMA click on your table on the left, then click the "Export" tab on the right. Leave everything default except uncheck the checkbox labeled "Data", then click "Go". Copy/Paste the output.

[agent007]

Void,

The docs dont explain clearly on the foll parameters...What values should I
give them?

Fields terminated by:

Fields enclosed by:

Fields escaped by:

Lines terminated by: \n (since I'm using vi)

The text file to import looks like this...There are no quotes or commas to
terminate the fields. Are these entries correct? Should I add the quotes for
every entry?

computers
internet
networks
security
hardware
software

thanks..

[Void Main]

Do you know what a Comma Separated Value (*.csv) file is? It is a file that has fields separated by commas. The field separator quetion is asking you what the fields in your data file are separated by, the ";" is the default. But you shouldn't have to worry about those fields as long as your data doesn't contain any of the separater characters listed. You only have one field, that is to populate one column right? If so, leave them default. As long as you don't have any ';' or '"' chars in your data and it is *NIX formatted then it should import without changing any of those things.

If you only have like 10 lines in the file you can insert the lines one at a time if you just can't get the import to work. I can't imagine why it doesn't work unless your column in the table is not of the right data type and is not large enough to hold the data. Again, if you can give me the schema and data file I can tell you exactly what the problem is.

[agent007]

hi Void!

Well after *a lot * of permutations & combinations, I finally got lucky!! All the 342 terms have been successfully loaded.

However, there are a couple of un-answered questions. The table had 2 columns. The 1st I'd give NULL values and the 2nd contained the terms.

\N computers
\N internet
\N networks

Importing the above via the command like on my home system worked fine. However, when using phpmyadmin on the site, I had to remove the NULL values, the tab spacing and bring all the terms to the extreme left (ie: without any spacing before the term, otherwise everything would get messed up!)..Apart from that, I had to also *specify* the column the data had to be imported into....no wonder, it wasn't working before.

computers
internet
networks

Thanks for all the help & pointers VoidMain....nothing like these forums....

PS: Regarding the shell access, were you referring to SSH?

[Void Main]

Yes, ssh or telnet (ssh much preferred). Also, sorry about you not knowing to specify which column in your table you wanted the single field of data to go. I thought if you had more than one column that you knew to specify which one. Again, if only I had your schema and data I could have helped you out right away. Oh well, you learn more when you figure it out on your own (hopefully).