importing txt file to mysql db
I've figured out how to import a text file thru phpadmin (havent manually yet) but it seems i havent set the text file up right, how do i set it up? I got lost in the mysql manual, a bit like the dictionary, its a bit hard looking for something you dont know what it is
the db has 6 fields, but the text file only has 1
the db fields are:
pid = id number (auto-increment, primary ...)
pname = image name and diminsions - width/height
pdesc = description of image
phit = times pic viewed
pcat = catagory of image
pdate = date posted
and the text file is 1807 lines of image details (pname), do i have to do something like
'',,'','','','';
to each line before i insert?
i really hope someone understands this and canhelp me, i really dont want to manually add them 1807 times lol.
mairving posted this at 13:40 — 1st June 2002.
They have: 2,256 posts
Joined: Feb 2001
Are there spaces or commas between the fields? If not, I would try opening it in a spreadsheet like Excel. Then save the the file as a tab delimited text file. After you save the file open it in Notepad and make sure that Excel didn't add quotes " around the fields. If so, use the replace in Notepad to delete the ". Next you can import into MySQL by this statement:
<?phpload data local infile \"pathto/filename.txt\" into table tablename
(pid, pname, pdesc, phit, pcat, pdate);
?>
then do a select statement to verify data:
<?phpSELECT * from tablename backslash G
?>
For some reason the backslash won't show up here. I usually use backslash G instead of ; since it displays it in an easier to read format.
Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states
Busy posted this at 22:44 — 1st June 2002.
He has: 6,151 posts
Joined: May 2001
there are spaces, but no commas.
I tried the excell method, it did add quotes to everything, had to use editpad, file was to big for notepad. tried inserting a smaller version and the fields are there, just no content. I removed the spaces first btw lol, not that it would serve my needs but could help trying to figure it out, maybe it could be the '.' and '=' etc.
I might try another approach, less info, more fields
I think I have to hit the books some more, try understand this mess
Cheers
cds posted this at 00:46 — 2nd June 2002.
They have: 359 posts
Joined: Mar 1999
If you are using phpMyAdmin to import, save the file as a .csv file and try it with the quotes. When importing, phpMyAdmin gives you the option for fields to be optionally enclosed with quotes.
Dan
Recycle Video Games Network
Stupidity killed the cat, curiosity was framed!
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.