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
1,659 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
  • 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 50 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 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

21 Experts available now in Live!

Get 1:1 Help Now