I have a file that looks like this
"1106837", "Jabbar", "33.29106", "73.48639", "PK", "Asia/Karachi"
"1106838", "Dhangdev Saiyidān", "33.26424", "73.41412", "PK", "Asia/Karachi"
"1106839", "Dheriān", "33.255", "73.42722", "PK", "Asia/Karachi"
.
.
.
When import the file using phpmyadmin following statement is executed.
LOAD DATA INFILE 'C:\\wamp\\tmp\\phpC108.tmp' INTO TABLE `allcountries` FIELDS
TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '
'(
`geonameid` , `place_name` , `lat` , `lng` , `country_code` , `city`
The problem is that the double quotations( " ) are also inserted into the table columns. I don't need the double quotations
It's because your fields are not terminated by ,, they're terminated by , (with the extra space in the termination string). Try the following:
LOAD DATA INFILE 'C:\\wamp\\tmp\\phpC108.tmp' INTO TABLE `allcountries` FIELDS
TERMINATED BY ', ' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '
'(
`geonameid` , `place_name` , `lat` , `lng` , `country_code` , `city`)
Remove whitespaces from the text file, mysql client cannot parse them.
"1106837", "Jabbar", "33.29106", "73.48639", "PK", "Asia/Karachi"
=>
"1106837","Jabbar","33.29106","73.48639","PK","Asia/Karachi"