Are you only using certain columns for a reason? I use the following as my Load Data command, it loads all the columns, which may not be what you are loooking for.
"LOAD DATA INFILE '/home/xxx/tmp/file.txt' INTO TABLE table_name FIELDS TERMINATED BY '|' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'"
oh i see thx well since it works....do u have any sample txt file that works with that command? please send me a sample of the txt file if u have
thanks
Here's the text, create a file called file.txt and paste this in to it:
1|2|3|4|5|6|7
Add or take away numbers as it corresponds to the number of fields you have. My file happens to be pipe delimited, you can do tabs, commas, whatever. Simply change the the delimited by section.
Another thing to check is make sure the path name is right. It goes from your hosting setup. The way my path is set is /home/myuserid from my host/folder.
I had trouble getting the path right in mine. It may be trying to add it from a directory where the file does not exist, therefore giving you a 0 result. Just an idea.
Hey, something else just hit me. Are you using a literal /t in your text file? If so, it needs changed. The /t represents a tab, so it is looking for a tab delimited file, not a /t delimited file. As mentione before you can use a lot of different delimiters.
If I'm covering stuff you know forgive me, I'm just a relative newbie too.
thanks a lot for ur help mate....i ll try them and i hope its going to work....
see ya
hey there....when i try to use ur commnad to the MYSQL command line i guess the message that the field operator is now what is expected...
Hmm, not sure what the answer is. Try it without the double slashes.
Original:
"LOAD DATA INFILE '/home/xxx/tmp/file.txt' INTO TABLE table_name FIELDS TERMINATED BY '|' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'"
Modified:
"LOAD DATA INFILE '/home/xxx/tmp/file.txt' INTO TABLE table_name FIELDS TERMINATED BY '|' ESCAPED BY '\' LINES TERMINATED BY '\r\n'"
maybe its the path....where do u place the txt file?
into the directory of database that u use?
this is where i put it
The path needs to reflect the file location on the server. Something like this:
/home/your_user_id/public_html/directory_file_is_in/file_name.txt
Give it a shot.
hey mate sorry to bug u again....i didnt have time to look at that but i am trying it now....I have the txt file in the directory for example:
C:\Program Files\MySQL\MySQL Server 4.1\bin
do i need to specify that on the query?
thanks
I'm not sure of how you are set up, but the code that I've been giving you is for working with a database and file on the same machine. Are you trying to upload from your home machine to a remote server, i.e, your website? If so it can be easily accomplished using an interface like PHPMyAdmin. Another possibility is to upload the file by ftp, then use the code we've been working on to import the data, specifying the path to where the file resides.
If the database and file are on the same machine then yes, you do need to specify the path. Unfortunately, I'm not that familiar with working with files on a local machine. I can't imagine that it would be all that different than a remote machine. I imagine the code would be something like this:
$sql = "LOAD DATA INFILE 'C:/Program Files/MySQL/MySQL Server 4.1/bin/file.txt' INTO TABLE `table_name` FIELDS TERMINATED BY '|' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'"Give it a try, hopefully it will work for you. Trial and error is a great teacher, keep working on it.
i am building a project for my course.....and i have Apache and MySQL on my computer...so i am using localhost.....
i think i have tried that and didnt work either....i ll try again thxs mate