Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Import csv to temp table, name field import issue

Posted on 2008-10-16
6
Medium Priority
?
728 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:MrNiss99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 26

Expert Comment

by:Umesh
ID: 22738484
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
 
LVL 6

Author Comment

by:MrNiss99
ID: 22752505
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
 
LVL 26

Accepted Solution

by:
Umesh earned 2000 total points
ID: 22755264
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
How To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

 
LVL 6

Author Comment

by:MrNiss99
ID: 22757772
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
 
LVL 6

Author Comment

by:MrNiss99
ID: 22757873
Do I need to add the column names for the table in that portion of the command?
0
 
LVL 6

Author Closing Comment

by:MrNiss99
ID: 31506902
Works great!!! Thanks so much for your input!!!!!!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question