Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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,705 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
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…

604 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