Geek Answers Handbook

SQL reserved keywords causing errors when importing data from a text file

I have the following code. I am trying to import a text file into a sql table using php, as suggested by one of the users on this site.

Unfortunately, my import got half errors due to the words "Max and Min" in the file in the text file.

I tried to figure out what I can do to avoid this. Most of the material that I found was to use reserved words in the column name. But my name is not the column that it is inserted into the columns as data.

Can this be avoided since I don’t know how many other reserved words are in the text file, and I need to run my code every time. I can’t interrupt him every time. This is a huge text file, so I can not manually replace the keywords every time.

mysqli_query("CREATE TABLE IF NOT EXISTS `add_feature_id` (
`id_f` INT(10) unsigned NOT NULL AUTO_INCREMENT,
`id_product` INT(10) unsigned NOT NULL,
`id_feature` INT(10) unsigned NOT NULL, 
`value` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL
DEFAULT   NULL,PRIMARY KEY ( `id_f` ) )",$conn);

$fd = fopen('trial.txt', 'r');
$fheader = fgets($fd); 

while (($data = fgetcsv($fd,0, "~")) !== FALSE) {
$id_product = $data[0];
$id_feature = $data[1];
$unitval = $data[2];
$value = mysql_real_escape_string($unitval);

mysqli_query("INSERT INTO   `add_feature_id`(`id_product`,`id_feature`,`value`) 
VALUES ($id_product,$id_feature,'$value')",$conn) or die(mysql_error());
}

fclose($fd);

$result = mysqli_query("SELECT * FROM `add_feature_id`",$conn);
//I print my result here but i get error while insert is executed

The error I get is

You have an error in the SQL syntax; check the manual that matches the version of your MySQL server for the correct syntax. Maximum and minimum requirements

Minimu 'low entry requirement on line 2

This is the part of my text file where the Max error occurs . and minimum requirements , as seen in the text file below

IMSKU~AttributeID~Value~Unit~StoredValue~StoredUnit(header row)

1006854 ~ 16257 ~Licensing Program: Max and Min Requirements<ul><li>Low entry level  requirement</li><li>Minimum 1 server (Band S) OR 5 desktop (Band A)</li></ul> ~  ~ 0.00 ~
+4
source share
2 answers

You have invalid data on line 32325:

1062708~16257~Express Licensing Program:<ul><li>Targeted at small - medium companies (1-500 units)</li><li>Minimum purchase requirements for licenses</li><li>Includes all Symantec software products</li><li> Certificated-based program - requires no legal review</li><li>Band identified via number of units per transaction</li></ul>
<br />Max and Min Requirements<ul><li>Low entry level requirement</li><li>Minimum 1 server (S-Band) OR 5 desktop (A-H Band)</li></ul>~~0.00~
1062708~16260~2~~0.00~

.

, , , $id_product $id_feature .

, / ( ).

+2

:

LOAD DATA INFILE '/tmp/trial.txt' 
INTO TABLE add_feature_id  
    FIELDS TERMINATED BY ',' 
           OPTIONALLY ENCLOSED BY '"'
    LINES  TERMINATED BY '\n' -- or \r\n

https://dev.mysql.com/doc/refman/5.1/en/load-data.html

Edit:

create table fred
(
fullName varchar(255) not null,
age int not null
);

fred2.txt:

"jason"~50
"fred max min max smith"~87
"jason avg abs sum smith"~12


load data infile 'c:\\dev\\fred2.txt'
into table fred
fields terminated by '~'
optionally enclosed by '"'
lines terminated by '\r\n'

select * from fred

yep they came in :)
+1

More articles:


All Articles