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,671 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
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…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

861 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