?
Solved

I am trying to load .bcp file (exp out from Sybase) to mysql 5.0 but the datetime datatype in Sybase cant load into mysql datetime~~ Help!!

Posted on 2006-06-19
5
Medium Priority
?
1,694 Views
Last Modified: 2008-01-09
I am trying to load .bcp file (exp out from Sybase) to mysql 5.0 but the datetime datatype in Sybase cant load into mysql datetime~~ Help!!

Can u also provide syntax for LOAD DATA (into mysql)?  I should use tab delimiter or space or comma since I bcp out from sybase?  How about line separater?  and char/varchar datatype?

 I assume u know how bcp export out the data into a flat file - it doesnt really have quote (") or single quote (') for string~ doesnt have comma~ doesnt have /n for line separater~  

Thank you.

0
Comment
Question by:mlin125
[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
  • 2
5 Comments
 
LVL 19

Expert Comment

by:Kim Ryan
ID: 16938817
Are you able to get Sybase to export in standard SQL date stamp format? There may be an option for this.

I would suggest using mysqlimport where you can specify the field and line terminators
mysqlimport  --fields-terminated-by=','  -lines-terminated-by='\n' db_name text.sql

Also, this could help: http://www.mysql.com/products/tools/migration-toolkit/
0
 

Author Comment

by:mlin125
ID: 16938886
Thank you for ur answer but it was somehow not quite something i am looking for~

I am export out (I should say bcp out) from Sybase~ so the delimiter is not comma~ if u know Sybase bcp format ~ that is also my question~

1. what is the delimiter and line separator in Sybase .bcp file?

2. I bcp out the whole table from Sybase (there is a datetime datatype in that table) but cant import/load into mysql (datetime datatype)

3. In my case, what will be the syntax for LOAD DATA or MYSQLIMPRT (note: the flat file is .bcp file~~ please pay attention to .bcp format)


Thank you.
0
 
LVL 19

Accepted Solution

by:
Kim Ryan earned 200 total points
ID: 16938939
the default field seperator for bcp is a tab, and defailt line terminator is linefeed (+carriage return for windows)  so you could say
mysqlimport  --fields-terminated-by='\t'  -lines-terminated-by='\n' db_name text.bcp
mysqlimport cannot do a date conversion, so my suggestion is you see if there is a bcp option to control the date stamp (maybe get help on this in the Sybase area of EE)
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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
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…

762 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