Load auto-incrementing 2-column table

Something wrong with SQL in phpMyAdmin

[kiwibrit]

I have a 2-column table "identnumber". The first column (and primary key) called "pagerefno" auto-increments. The second colun is called "url". It already has 1 index.htm loaded in the first row.

I have a text file in the main directory called "identurl.txt"

It consists of a series like:

aurl.htm,\n
anotherurl.htm,\n
yetanotherurl.htm\n

etc

I need to load the table into the database, so I go to phpMyAdmin SQL, and enter:

LOAD DATA LOCAL INFILE '/identurl.txt'
INTO TABLE identnumber
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(url);

phpMyAdmin appears to run this with no error alerts. But the table does not load.

Yet when I try (to test the table is working)

insert into identnumber (pagerefno, url) values
(NULL, 'aurl.htm');

it assigns the next value in column "pagerefno", and inserts "aurl.htm" beside it in column "url".

So, obviously I am doing something wrong when I attempt to load my table as outlined at the beginning. But what?

[coopster]

I'm assuming at this point you have two entries in your table now, the first being the original "index" you described and the second being the hand-entered entry.

The problem you are having with your LOAD DATA INFILE statement is that you have left out the values for the first column of your table (the auto-increment column) in your text file you are loading. You can use \N as a NULL value.

More info here ...

[dev.mysql.com...]
[dev.mysql.com...]

[kiwibrit]

Hmm. Not having much luck with this.

I have tried

I altered my text file, so that I had

Using 
[code]
LOAD DATA LOCAL INFILE '/pageident.txt'
INTO TABLE pageref
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(pagerefno,url); 

I altered my text file, so that I had

NULL,aurl.htm,\n
NUL,anotherurl.htm,\n

I even tried

20,aurl.htm\n
21,anotherurl.htm\n

More than that, since I am trying this all out on my local lap top using the Apache2/php/MySQL server in the computer, I tried the file its full address on the C drive.

SQL in phpMyAdminran -no error alerts - but no change to the table.

Frustrating

[kiwibrit]

Finally cracked it - sort of.

rewrote text file so that it read:

1,"aurl.htm",\r\n
2,"anotherurl.htm",\r\n

then used phpMyAdmin "Insert table" facility.

Table loaded. The SQL code was:

LOAD DATA LOCAL INFILE 'C:--as assigned by phpMyAdmin---' INTO TABLE `identable` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'

Learned a bit. Among other things that white space such as blank lines and LH margin seems to matter in a text file like this