Solved

Import csv to temp table, name field import issue

Posted on 2008-10-16
6
722 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL: launch actions one before the other 10 23
Sql server function help 15 30
MySQL Query Using Up Memory 6 29
How to use three values with DATEDIFF 3 24
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Creating and Managing Databases with phpMyAdmin in cPanel.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
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…

777 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