Solved

Import csv to temp table, name field import issue

Posted on 2008-10-16
6
720 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now