How do I import data from Excel into SQL Anywhere?

[Breck_Carter]

From: Count Tatte

Newsgroups: sybase.public.sqlanywhere.general

Subject: Copying Excel into database

Date: 8 Nov 2010 02:17:27 -0800

Hi

I have some excel sheets i'm going to copy into a sybase database.

I thought of using visual basic code written inside the excel (Send the sheet data via ADODB connection).

Which approach do you suggest i use, is there some ready-made solution?

I heard there is a sqlbulkcopy command that works with MS SQL SERVER. But i guess this doesn't work with Sybase.

Can you give me a hint what is the best solution for a work like this?

[Breck_Carter · Accepted solution]

You can use CREATE SERVER and CREATE EXISTING TABLE inside SQL Anywhere to use the "proxy table" feature to "pull" data from Excel via SELECT, and insert into a real table via

   INSERT real-table SELECT * FROM excel-proxy-table;

This feature has the official name "remote data access" in the Help:

http://dcx.sybase.com/index.html#1200en/dbusage/ug-accessrd.html

Some information about Excel...

http://dcx.sybase.com/index.html#1200en/dbusage/excel-odbc-omni-server.html

Some sample code...

http://sqlanywhere.blogspot.com/2008/12/select-from-excel-spreadsheets.html

The following discussion of a problem with Excel also contains an end-to-end example:

http://sqlanywhere-forum.sap.com/questions/515/how-do-i-fix-the-ability-to-edit-data-in-a-linked-exc...

[Breck_Carter · Accepted solution]

I wasn't sure about posting this Q&A because it was so "derivative", but I did so because it was an opportunity to enter a straightforward "How do I..." question on this subject (which keeps coming up). And judging from the votes it looks like it's a hit :)... so OTHER FOLKS are welcome to do the same: If you have searched and searched and answered your own "How do I..." question, PLEASE POST THE RESULTS HERE! Thanks!

[VolkerBarth · Accepted solution]

I fully second that invitation to publish one's self-learning efforts. (And that makes so much more fun in SQLA than in the newsgroups, as you get valueable feedback through upvotes and comments and therefore get to know if others could share your insight...)