Topic: HELP: how to use ASP to migrate data from Excel to SQL server 2000. this is only way I can import data....please help. @佛州华人论坛:佛州枫下论坛 The Rolia Forum of Florida

工作学习 / IT技术讨论 / HELP: how to use ASP to migrate data from Excel to SQL server 2000. this is only way I can import data....please help. -shinning(dudu); 2003-4-25 (#1158047@0)

I remember SQL server 2000 can import data from Excel directly. If not, ASP can read Excel by creating an Excel object to read data, then insert into SQL. -ningxin0809(雁影行洲); 2003-4-25 (#1158059@0)

thanks sooooooooo much, I can not using DTS to import, that's why I have to find out how to create a ASP page to import a excell 4.0 file to a table..:-( -shinning(dudu); 2003-4-25 (#1158082@0)

If you have Access, more easy: create a link table of your SQL database, and Access has the function to import from Excel/txt to link tables. I work on Oracle and my collegues do the same way on SQL. -ningxin0809(雁影行洲); 2003-4-25 (#1158089@0)

our situation is: our clients create a excel 4.0 file everyday, and they ftp it to web server......(inside)our situation is: our clients create a excel 4.0 file everyday, and they ftp it to web server. I asked our ISP to set up a simple DST for us, and I can not edit this DTS package on the remote sql server, because ISP told me it's microsoft bug. they suggested to create ASP page to do transfer, then they set up schedule to run this script...:(.....I don't know how to do,,,It'll kill me..................................................... -shinning(dudu); 2003-4-25 {442} (#1158106@0)

Never heard of scheduling to run an ASP page at server side. Your ISP is trying to avoid work. You have to be more specific on what is needed to be done in order for other people to help you.Don' t just pin your head on the solution suggested by your ISP. Most of the time they know nothing about the business. -wonton_soup(Wonton Soup); 2003-4-25 {120} (#1158122@0)

thanks, I can login remote sql server using enterprice manager, but I can not see my DST package and design it, my ISP sail they can not fix it untile SP4 released by microsoft,thanks, I can login remote sql server using enterprice manager, but I can not see my DST package and design it, my ISP sail they can not fix it untile SP4 released by microsoft, and then they told me to create a web application...:(.............and only give me two web links, do you know about it???what kind of web application... -shinning(dudu); 2003-4-25 {331} (#1158162@0)

The reason why you can't see the DTS service in EM is because your login ID doesn't have some kind of server role, which has nothing to do with Service pack. -yangn(Raymond); 2003-4-25 (#1158178@0)

this is ISP sent it to me -shinning(dudu); 2003-4-25 (#1158185@0)
even if I can see it sometime, but I still can not edit it and chang some properties. because the data source I was using is on the remote web server, I got an error : I have not read permmition for that file...:( -shinning(dudu); 2003-4-25 (#1158191@0)

ok. It seems that you don't have the correct permission to compile the DTS package (ask ISP what server role they assigned to u) . If they can't assign you the permission to edit the package, one solution is:export the excel file to the text file and then use bulkcopy to import them into the tables -yangn(Raymond); 2003-4-25 {91} (#1158219@0)

If your feel most comfortable with DTS and think the DTS is the best way, then simulate the production environment locally, design your DTS package on your local machine,and then deploy your DTS packge to your ISP ( Save your DTS package as Structure Storage File ). Don't just take any suggestion that you don't feel comfortable. There are quite a lot ways to automate the job of importing data to SQL server. ASP pages are for human interaction, not for the automated job. -wonton_soup(Wonton Soup); 2003-4-25 {307} (#1158197@0)

Thanks a lot....this is my first time to set up automate transaction.(new police...) so , your means I can sent this Structure Storage File to ISP, and they can reload to their sql server??? -shinning(dudu); 2003-4-25 (#1158204@0)

but the location of data source is diffirent, when I give it to them, them need chang all the data souce address???? -shinning(dudu); 2003-4-25 (#1158213@0)

...simulate the production environment locally.... -wonton_soup(Wonton Soup); 2003-4-25 (#1158220@0)

汤汤, 我刚才试了个办法自动运行一个asp之类的...hoho, 但是server端必须登录. -expertune(非典型伪劣); 2003-4-25 (#1158199@0)

but this link is about how to write to excel file, My question is how to do a transfer like DTS.:( -shinning(dudu); 2003-4-25 (#1158091@0)

估计不行, 弄个存储过程试试. 我原来有个客户就是这样, 只好写个asp他自己上船excel再运行, 倒数据. 一定要用transaction, 否则一旦出错, 有谢非法字符之类的, 倒了一半就麻烦了 -expertune(非典型伪劣); 2003-4-25 (#1158203@0)

thanks every DX, I will post the specific question what's needed to be done in my project....thanks again, and go home first...hehe............:) -shinning(dudu); 2003-4-25 (#1158218@0)
ADO can access data stored in Excel format. So it is easy to get data from excel and saved in SQL Server. I do such kind things before. -rickibmpad(imbthinkpad); 2003-4-25 (#1158444@0)

@Florida