Solved

Import csv to temp table, name field import issue

Posted on 2008-10-16
6
723 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
  • 4
  • 2
6 Comments
 
LVL 26

Expert Comment

by:ushastry
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:
ushastry earned 500 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

679 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