i am using this command:

LOAD DATA INFILE 'some.txt' INTO TABLE student (stud_id, stud_fname, stud_lname, email, status, level) FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '\"\"' ESCAPED BY '\' LINES STARTING BY '%%' TERMINATED BY '\n';

and the text is that

%%"22222"\t"Nick"\t"Gem"\t"someaddress@domain.com"\t"O"\t"a"
%%"22223"\t"Josh"\t"Gold"\t"someaddy2@domain.com"\t"O"\t"a"

i get an error message.

if i just give the command:

LOAD DATA INFILE 'some.txt' INTO TABLE student (stud_id, stud_fname, stud_lname, email, status, level)

it insert only the into the primary key (stud_id) the value 0 and of course then it says duplicated entry and doesnt go on

anybody who could know?

    well if i put this
    LOAD DATA INFILE 'some.txt' INTO TABLE student (stud_id, stud_fname, stud_lname, email, status, level) FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '\"\"' ESCAPED BY '\' LINES STARTING BY '%%' TERMINATED BY '\n';

    its just say that there is an error in the syntax

    and if i just put

    LOAD DATA INFILE 'some.txt' INTO TABLE student (stud_id, stud_fname, stud_lname, email, status, level);

    it inserts only 0 to the first field (stud_id)

    do u know why?

    is it the fault on the txt file?

    thanks

      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.

                          13 days later

                          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

                                Write a Reply...