Hi everyone,

I'd like to import a .txt file or even a .csv file into a phpMyAdmin table.

My table in phpma is set up as follows:

I have three fields set up in the table: candidate_id, first_name and last_name.

The canditate_id is set up as follows:

Field: candidate_id, Type: INT, Attributes: unsigned, Null: not null, Extra: auto_increment, Primary key: checked

The first_name and last_name fields each have a VARCHAR type, a length/value of 40, and are set to not null.

The abovementioned information is the only info that was entered - all other possible information for each of the fields mentioned have been left blank, ie. default, collation, etc.

I'd like to enter the data in a Word file then import it, but I don't know how to set the table up in Word so it'll import correctly. I mean should I set up a 3 column table with the columns called author_id, first_name and last_name, then enter the data in the rows? But I'm not sure about this because since the author_id field is an auto-incremented primary key won't this be populated by phpMyAdmin?

Next after I've figured out how to set up the table, I was going to convert the table to text with tabs being the separator. I was then going to save the word file as a .txt file.

After doing a search on this site, I found some instructions on how to import a .csv file into phpma as follows:

click the SQL tab in phpMyAdmin and type the following command:
LOAD DATA INFILE 'C:\myFile.csv'
INTO TABLE mytable
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
Click Go.

The first problem is that this is a .csv file that's imported - can you import a .txt file? Also, my file would be separated with tabs instead of commas so how would I change this part of the code to indicate a tab mark?:

FIELDS TERMINATED BY ','

Would really appreciate any advice on offer.

Thanks

    thanks for that info.

    I still wasn't sure if I set up the word file correctly, but I gave it a go and set up a test file by typing in three lines of data in a word file:

    1 (tab) john (tab) doe
    2 (tab) jane (tab) doe
    3 (tab) james (tab) doe

    I saved the file as a .txt file in the correct directory, then in phpMyAdmin I typed the following command in the SQL tab:

    LOAD DATA INFILE 'test.txt'
    INTO TABLE candidate_id
    FIELDS TERMINATED BY 't'
    LINES TERMINATED BY '\r\n'

    When I clicked 'Go' it only imported one record and it only imported the "number 1" into the first field - nothing else.

    Can you tell me what I might be doing wrong?

      1. It's \t for a tab character, not t.

      2. Sounds like you're using Windows, so I don't see why \r\n shouldn't work for line endings, but try shortening it to just \n if fixing the \t above doesn't work.

        I changed the code to the following as you suggested:

        LOAD DATA INFILE 'test.txt'
        INTO TABLE candidate_id
        FIELDS TERMINATED BY '\t'
        LINES TERMINATED BY 'n'

        ...but it only brings in the first record - albeit more information this time:

        1 john doe, but it also adds the number 2 after the surname 'doe'. It doesn't bring in the other two records at all.

        I'm not even sure if I'm supposed to be using the numbers for the primary key since phpMyAdmin should add these automatically right?. If I'm not supposed to, then would I just leave out this column and just have the second and third columns?

        Also I'm using mac OS 10.3.9 if that helps troubleshoot at all.

        Thanks for the continued support.

          gwh wrote:

          I'm not even sure if I'm supposed to be using the numbers for the primary key since phpMyAdmin should add these automatically right?. If I'm not supposed to, then would I just leave out this column and just have the second and third columns?

          D'oh! I didn't even think of that. Yes, if that's an auto_increment column, you shouldn't be specifying it.

          gwh wrote:

          Also I'm using mac OS 10.3.9 if that helps troubleshoot at all.

          Double d'oh! Yes, this does help - Mac line endings are different that Windows' which are different from Unix's. For Mac, you want the LINES TERMINATED by \r (aka a carriage return, or CR). Don't forget the backslash, too.

            I left the primary key column empty (but with a tab character in its place) and also made sure the new line character was \r, so I was able to bring everything in successfuly. The only thing I noticed though was that phpMyAdmin was inserting all the entries randomly. I mean it wasn't in the same order as I had it in the Word file. I know there's an option in there to sort by primary key but I just wondered is it normal for it to bring everything in so randomly?

              4 years later
              gwh;10798041 wrote:

              Hi everyone,

              I'd like to import a .txt file or even a .csv file into a phpMyAdmin table.

              My table in phpma is set up as follows:

              I have three fields set up in the table: candidate_id, first_name and last_name.

              The canditate_id is set up as follows:

              Field: candidate_id, Type: INT, Attributes: unsigned, Null: not null, Extra: auto_increment, Primary key: checked

              The first_name and last_name fields each have a VARCHAR type, a length/value of 40, and are set to not null.

              The abovementioned information is the only info that was entered - all other possible information for each of the fields mentioned have been left blank, ie. default, collation, etc.

              I'd like to enter the data in a Word file then import it, but I don't know how to set the table up in Word so it'll import correctly. I mean should I set up a 3 column table with the columns called author_id, first_name and last_name, then enter the data in the rows? But I'm not sure about this because since the author_id field is an auto-incremented primary key won't this be populated by phpMyAdmin?

              Next after I've figured out how to set up the table, I was going to convert the table to text with tabs being the separator. I was then going to save the word file as a .txt file.

              After doing a search on this site, I found some instructions on how to import a .csv file into phpma as follows:

              click the SQL tab in phpMyAdmin and type the following command:
              LOAD DATA INFILE 'C:\myFile.csv'
              INTO TABLE mytable
              FIELDS TERMINATED BY ','
              LINES TERMINATED BY '\r\n'
              Click Go.

              The first problem is that this is a .csv file that's imported - can you import a .txt file? Also, my file would be separated with tabs instead of commas so how would I change this part of the code to indicate a tab mark?:

              FIELDS TERMINATED BY ','

              Would really appreciate any advice on offer.

              Thanks

              query worked for me. perfect..

                Write a Reply...