Import CSV problem
I am attempting to import a text file into a 2 field table using "load data infile".
This is the file:
"01","Atlantic"
"02","Bergen"
"03","Burlington"
"04","Camden"
"05","Cape May"
"06","Cumberland"
"07","Essex"
"08","Gloucester"
"09","Hudson"
"10","Hunterdon"
"11","Mercer"
"12","Middlesex"
"13","Monmouth"
"14","Morris"
"15","Ocean"
"16","Passaic"
"17","Salem"
"18","Somerset"
"19","Sussex"
"20","Union"
"21","Warren"
Each line is terminated by a carriage return and line feed.
this is the code
INTO TABLE countiestest
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\r\n'
This code results in: Error Code: 1083. Field separator argument is not what is expected; check the manual 0.000 sec
with ESCAPED BY '\r' I get a single record back
'01', 'Atlantic\"\n\"02'
with ESCAPED BY '\n' I get a single record back
'01', 'Atlantic\"\r\"02'
with ESCAPED BY '\\' I get
'1', '0', 'Berg', 'Burlington\"\r\n\"04'
'5', '0', 'Cumb', 'Essex\"\r\n\"08'
'9', '0', 'Hunt', 'Mercer\"\r\n\"12'
'13', '0', 'Morr', 'Ocean\"\r\n\"16'
'17', '0', 'Some', 'Sussex\"\r\n\"20'
'21', '0', '', ''
Dose anybody have a clue what is going on here?
Bill Cook
This is the file:
"01","Atlantic"
"02","Bergen"
"03","Burlington"
"04","Camden"
"05","Cape May"
"06","Cumberland"
"07","Essex"
"08","Gloucester"
"09","Hudson"
"10","Hunterdon"
"11","Mercer"
"12","Middlesex"
"13","Monmouth"
"14","Morris"
"15","Ocean"
"16","Passaic"
"17","Salem"
"18","Somerset"
"19","Sussex"
"20","Union"
"21","Warren"
Each line is terminated by a carriage return and line feed.
this is the code
INTO TABLE countiestest
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\r\n'
This code results in: Error Code: 1083. Field separator argument is not what is expected; check the manual 0.000 sec
with ESCAPED BY '\r' I get a single record back
'01', 'Atlantic\"\n\"02'
with ESCAPED BY '\n' I get a single record back
'01', 'Atlantic\"\r\"02'
with ESCAPED BY '\\' I get
'1', '0', 'Berg', 'Burlington\"\r\n\"04'
'5', '0', 'Cumb', 'Essex\"\r\n\"08'
'9', '0', 'Hunt', 'Mercer\"\r\n\"12'
'13', '0', 'Morr', 'Ocean\"\r\n\"16'
'17', '0', 'Some', 'Sussex\"\r\n\"20'
'21', '0', '', ''
Dose anybody have a clue what is going on here?
Bill Cook
Dave Baldwin
I posted a working solution in the first version of this question.
ASKER
No you did not.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am new to mySQL , but have coded many other things and I knew it was in the escape commands, just didn't know which one. Am using the Murach book on mySQL and it never mentions the line terminate command. I wonder how many others jerked around with this for a few hours?
Thanks
Bill Cook
Thanks
Bill Cook