Import csv to temp table, name field import issue

I am trying to import a csv into a temp table in a MySQL DB. I am able to successfully import to the proper location via this command:

load data local infile '/home/user/accounts_test.csv'
into table accounts_temp2
fields terminated by ','
lines terminated by '\n' (field1, field2, field3);

The data is getting into the table, however the name column in my csv data looks like Doe, John. So when I am importing it puts the last name in the name column and the first name into the next column. I have tried using Doe, John and that had the same effect as without the 

Question: How do I get Doe, John to import into the same name column?

Also, when the date_entered and the date_modified  gets imported it only shows 0000-00-00 00:00:00 as the results. See the screen shot attached for column.

dateissue.jpg
LVL 6
MrNiss99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

UmeshMySQL Principle Technical Support EngineerCommented:
This is a very common problem when you are trying to import a csv file and your input is having a comma in a column value.  Look at your command..

load data local infile '/home/user/accounts_test.csv'
into table accounts_temp2
fields terminated by ','  ==>>>>>>>>  
lines terminated by '\n' (field1, field2, field3);

This assumes that ',' is a separator and unfortunately name field have a value something "Doe, John" which it treats as two different columns...

Try to save that csv file as tsv (tab sep values) or include the values in double quote..


"1","Doe, John" ,"hello"  etc etc

Also you can try something to avoid data field showing 000:000 etc


load data local infile '/home/user/accounts_test.csv' into table accounts_temp2
fields terminated by ','
optionally enclosed by '"'
lines terminated by '\r\n'
(
 @col1
,@col2
,@col3
)
set
col1         = @col1
col2         = @col2
col3         = @col3
;




0
MrNiss99Author Commented:
Thanks a bunch ushastry I was able to get the date thing figured out, it seems that my csv did not have the date formated in the way that MySQL needed it to be. Unfortunatly, I am still not able to get the name field in there correctly. I tried the double quotes, and I also tried the tsv, still the same effect.

I have on question though, when I do import the data in either format (csv, tsv) there is alway a " in front of the lastname and there is a " at the end of the first name, all other fields/ columns the data is returned the way it should be.

If I remove the , from the names it imports fine and I think that i might just have to deviate from the current formating for the names and they will just have to be displayed in the UI as Firstname Lastname. If you have any further suggestions those would be apprciated.

Thanks for your help.
0
UmeshMySQL Principle Technical Support EngineerCommented:
Personally I feel there is no need to remove , from the names.. better do one thing.. make sure the input is tab delimited i.e  

1    Doe, John    hello    etc etc

and your keyword should be

fields terminated by ',' -> fields terminated by '\t'

load data local infile '/home/user/accounts_test.csv' into table accounts_temp2
fields terminated by '\t'
optionally enclosed by '"'
lines terminated by '\r\n'
(
 @col1
,@col2
,@col3
)
set
col1         = @col1
col2         = @col2
col3         = @col3
;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

MrNiss99Author Commented:
Thanks I will give that a shot, one question though. What does this portion of the command mean:

(
 @col1
,@col2
,@col3
)
set
col1         = @col1
col2         = @col2
col3         = @col3

Thanks for working with me on this.

0
MrNiss99Author Commented:
Do I need to add the column names for the table in that portion of the command?
0
MrNiss99Author Commented:
Works great!!! Thanks so much for your input!!!!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.