1

How can I import data from a text file into a database without giving a primary key in the text file?

So I have a table where I have 3 columns: ID, firstName, lastName.

ID is auto incremented. I would like to read in the names from the text file like that:

John, Smith; Michael, Jordan;

I don't want to use the primary key, as I don't know what will be the next primary key in the table, that should be done by auto increment.

If I use the text file like this, than I get the error message: Invalid column count...

The settings:

Columns separated with: ,

Columns enclosed with: "

Columns escaped with: \

Lines terminated with: ;

If I use the text file like this:

21, John, Smith; 22, Michael, Jordan;

The file can be imported (with the strange behavior that it tries to read the 3 empty line too, and sends an error, this one I don't understand either, but its a different topic)

This is the dump from the table:

CREATE TABLE IF NOT EXISTS `LoginData2` (
  `FirstName` varchar(10) NOT NULL,
  `LastName` varchar(10) NOT NULL,
  `ID` int(4) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Francisco
  • 10,918
  • 6
  • 34
  • 45
user3435407
  • 1,019
  • 4
  • 15
  • 31
  • Take a look at: [this post](http://stackoverflow.com/questions/2463542/how-to-import-text-file-to-table-with-primary-key-as-auto-increment?rq=1) – mseifert May 09 '15 at 20:46
  • hi, i checked the post and the answers are not really helping, the accepted answer is for example not working for me, it doesn't matter whether the auto incremented prim key is the last or first column. – user3435407 May 09 '15 at 21:01

1 Answers1

2

Your spec says that Columns enclosed with: "

I added quotes around the columns (and took away the spaces):

"John","Smith";"Michael","Jordan";

AND put the auto increment ID column last. It imported fine with these settings.

This is a dump from my test table. Compare it with yours and see what is different. Also, try creating this table and import the data above to see how it works.

CREATE TABLE IF NOT EXISTS `users` (
  `firstname` varchar(100) NOT NULL,
  `lastname` varchar(100) NOT NULL,
  `id` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=7;

UPDATE: When you import with phpMyAdmin, make sure the import setttings are correct. Your settings are not the default. I believe you need to choose csv using LOAD DATA and then fill in all the delimiters as you have stated.

mseifert
  • 5,390
  • 9
  • 38
  • 100
  • Hi, I tried as you say but I still get Invalid column count. – user3435407 May 13 '15 at 08:09
  • @user3435407 I've modified my answer with a dump from the table. Compare it with yours and see what is different. You can also post a dump from your table so I can work with your structure. – mseifert May 13 '15 at 13:20
  • I copied your code and executed it in phpmyadmin and then I tried to import again, with the same settings as in the post, and I still get: Invalid column count. I don't know how to get the dump from my table, but I figure it out – user3435407 May 14 '15 at 16:49
  • and the text file contains now: "Alfa1","Alfa2";"Beta1","Beta2"; – user3435407 May 14 '15 at 16:55
  • 1
    When you import with phpMyAdmin, make sure the import setttings are correct. Your settings are not the default. I believe you need to choose csv using LOAD DATA and then fill in all the delimiters as you have stated. – mseifert May 14 '15 at 18:57
  • yes! it works with the LOAD DATA, thanks! please add this to the answer so i can accept it! thank you!! – user3435407 May 14 '15 at 22:28
  • Glad to hear it works. I've update the answer with the LOAD DATA information. – mseifert May 15 '15 at 02:05