TobinLewis
asked on
Import excel file into Coldfusion
I am currently running a Coldfusion 5.0 application with a SQL 2000 backend database.
I am interested in creating a tool that will import student records from excel file into my user login table. Details below.
We have developed an career search application that we market to high schools. We have developed tools that allow the school to set up counselor accounts and then set up student accounts beneath each counselor. This allows the school to run reports for the entire schools or by individual counselor. We have been receiving requests over the past year to provide some sort of import tool that would allow the school to import their student user list in an excel or some type of delimited list format. This would help them to not have to enter all of the student accounts by hand.
Is this doable in ColdFusion 5.0, if so how? Would it be accomplished better using another tool or application, if so what application? Are there any ColdFusion applications that have been created that do this?
Thanks for you help!
I am interested in creating a tool that will import student records from excel file into my user login table. Details below.
We have developed an career search application that we market to high schools. We have developed tools that allow the school to set up counselor accounts and then set up student accounts beneath each counselor. This allows the school to run reports for the entire schools or by individual counselor. We have been receiving requests over the past year to provide some sort of import tool that would allow the school to import their student user list in an excel or some type of delimited list format. This would help them to not have to enter all of the student accounts by hand.
Is this doable in ColdFusion 5.0, if so how? Would it be accomplished better using another tool or application, if so what application? Are there any ColdFusion applications that have been created that do this?
Thanks for you help!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
TacoBell is right - it sounds like you have no need to use CF to do this. So you should just import directly into SQL via DTS
Bah, "the only correct solution". There will always be more than one way to skin a cat. But, that is three suggestions now for DTS. Although from his question, I think he wants to at least use CF to accept the files to process from the user. So CF will be involved for uploading the file and possible pushing the file to SQL and kicking off the DTS job.
Bah??
Shooksm, if you think there is anything that can handle this problem better than DTS please do say so, I always like to learn new things.
Shooksm, if you think there is anything that can handle this problem better than DTS please do say so, I always like to learn new things.
Again, many ways to skin a cat. Even with SQL server. Besides DTS you can use the Bulk Copy Utility, Bulk Insert, Openrowset and openquery to pull in heterogenous data. So to say that DTS is "the only correct solution" would be incorrect. Better to give the person all the options so they can figure out which one works best for their situation.
One problem that comes to mind with DTS is the running of a DTS job after the file is supplied. How would you run it so that a low level web user account can kick it off. You can not directly run DTS jobs unless you start giving the account used by the Cold Fusion server some pretty high level access to SQL. For instance, to kick off a DTS job you could use Job Scheduler to accomplish this but that requires high level of access to the sp_job series of system stored procedures. You can use the DTSRUN command line utility but that requires opening up access to the xp_cmdshell system stored procedures. Do you really want a web server to have the ability to run command line tools on your database server? To some, that risk is not worth the conveinence of DTS if the web server ever become comprimised. You could schedule the DTS to run every minute and check for new files but that requires a lot of customization in the DTS job.
Also, take into account the entire picture. The question was originally presented with the option of making delimited files. Bulk Insert of delimitted text is by far faster than DTS of Excel data. This can also be run right from a stored procedure or SQL query using bulkinsert vs having to schedule a job or run a command line utility with DTS.
One problem that comes to mind with DTS is the running of a DTS job after the file is supplied. How would you run it so that a low level web user account can kick it off. You can not directly run DTS jobs unless you start giving the account used by the Cold Fusion server some pretty high level access to SQL. For instance, to kick off a DTS job you could use Job Scheduler to accomplish this but that requires high level of access to the sp_job series of system stored procedures. You can use the DTSRUN command line utility but that requires opening up access to the xp_cmdshell system stored procedures. Do you really want a web server to have the ability to run command line tools on your database server? To some, that risk is not worth the conveinence of DTS if the web server ever become comprimised. You could schedule the DTS to run every minute and check for new files but that requires a lot of customization in the DTS job.
Also, take into account the entire picture. The question was originally presented with the option of making delimited files. Bulk Insert of delimitted text is by far faster than DTS of Excel data. This can also be run right from a stored procedure or SQL query using bulkinsert vs having to schedule a job or run a command line utility with DTS.
ASKER
Hey Everyone...Thanks for all of the replys.
It sounds like DTS is the way to go if I was the one that was going to manipulate the uploaded file and import it. It may turn out that this is the way that the process is handled due to the technical knowledge of most of my users. To start though, I'd like to try to develop a simple (for the user) process to upload a file through Coldfusion, have Coldfusion load the information into a view so that the user can verify the information is loading up into the correct columns (First Name, Last Name, Username, Password, ClassID). Once they are satisfied that the information is correct, they could click an add button and the information would be loaded into the user database table.
Is DTS still a viable option in this scenario? I am probably looking at anywhere from 1500 to 2000 records of 5 or 6 fields each being added to the database.
It sounds like DTS is the way to go if I was the one that was going to manipulate the uploaded file and import it. It may turn out that this is the way that the process is handled due to the technical knowledge of most of my users. To start though, I'd like to try to develop a simple (for the user) process to upload a file through Coldfusion, have Coldfusion load the information into a view so that the user can verify the information is loading up into the correct columns (First Name, Last Name, Username, Password, ClassID). Once they are satisfied that the information is correct, they could click an add button and the information would be loaded into the user database table.
Is DTS still a viable option in this scenario? I am probably looking at anywhere from 1500 to 2000 records of 5 or 6 fields each being added to the database.
DTS won't help you with showing the view. And if you only have 1500-2000 records, the hassle of getting DTS to work with this will probably be outweighed by the ease of just looping through the data and inserting especially if this file is infrequently uploaded.
hassle with DTS? Its a hassle to write an import script in cf.
Taco, I have already discussed the difficulties associated with calling DTS packages. Try providing the person who asked the question with examples and instruction on solving the problem instead of complaining about everything I write as that is the point of this forum.
Sure I will provide samples as soon as I know that DTS is the way he wants to go, I'm not wasting my time with a long explanation while he might not go for this option, he might go for some of the options that are really not that great and still provided in this expert forum, why waste my time?
BTW. CFEXECUTE AND DTSRUN work fine together, no special permissions needed. there you go.
PS. I don't complain mate, I am just stating the facts.
G'day
BTW. CFEXECUTE AND DTSRUN work fine together, no special permissions needed. there you go.
PS. I don't complain mate, I am just stating the facts.
G'day
You could always code something. Like create an ODBC connection to your excel file (make sure the file is not opened before) and do some query to it! If you need help on how to access the data in excel, let me know! :)
hi TobinLewis any luck with this? Need more help?
ASKER
Sorry it took me so long to get back to this, a few other projects have reared their ugly heads. I appreciate all of your responses.
Since this is something that is only going to be used by a handful of users (lots are asking for it, but oinly a few will take advantage of it), I think I'd like to try doing this in Coldfusion if possible.
I am envisoning a wizard type process that would flow as follows. The school site administrator logs in and selects the class account that the users will be imported into(i.e. class of 2008). Then the site administrator will upload the file, possibly excel format, but could be another format. Coldfusion then processes the file and outputs the results in a view in the appropirate columns and allows the site administrator to review for correctness. Once satisfied that the information is correct, the site administrator clicks on an import button and the information is imported into the appropriate SQL table.
Can I do all of this in Coldfusion? How do I get started? Does anyone have any examples of having done this?
Again, thanks for all of the help and responses.
Tobin
Since this is something that is only going to be used by a handful of users (lots are asking for it, but oinly a few will take advantage of it), I think I'd like to try doing this in Coldfusion if possible.
I am envisoning a wizard type process that would flow as follows. The school site administrator logs in and selects the class account that the users will be imported into(i.e. class of 2008). Then the site administrator will upload the file, possibly excel format, but could be another format. Coldfusion then processes the file and outputs the results in a view in the appropirate columns and allows the site administrator to review for correctness. Once satisfied that the information is correct, the site administrator clicks on an import button and the information is imported into the appropriate SQL table.
Can I do all of this in Coldfusion? How do I get started? Does anyone have any examples of having done this?
Again, thanks for all of the help and responses.
Tobin
Again I feel lots of valid solutions are posted.
Ahh, your messages have changed, I guess the message confused me a bit. So next time no need to respond?
DTS is written to do exactly that, it could import a big excel file in less than a second, where as coldfusion (which is not made to do these things) would take several minutes.
Its fairly simple to write a dts package, go into MS SQL -> right click Data Transformation Services -> New Package -> throw a Excel connection on there and follow the prompts -> throw a Microsoft OLE DB provider connection on there and follow the prompts -> then put a Transform Data Task on there (going from excel to SQL) double click this task and see if you can figure out what to do ;-) if you need any help let me know.