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
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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