Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pavan2020
Starting Member
5 Posts |
 Posted - 2011-03-04 : 02:00:30
|
| Is there any tool or method that can automatically convert the Excel formulas sheet to SQL Query's? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-03-04 : 04:08:21
|
Yes. Write a Function.
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
pavan2020
Starting Member
5 Posts |
Posted - 2011-03-04 : 04:26:49
|
| Could you please more explain by taking any example .. Please |
|
|
|
michael.appleton
Posting Yak Master
160 Posts |
Posted - 2011-03-04 : 05:28:23
|
| You wouldn't want something that automatically converts them as so often the logic used in excel can be completely different to a way you'd implement something with the same result in SQL, not to mention some excel functions could be doing things not even applicable in SQL. It would be far better to go through and write them manually and if possible write them in a more set based manner. One example is a v-lookup, that is commonly coded in excel to work like a join in SQL. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-03-04 : 08:00:31
|
| To add to Michael's reply, compared to Excel, MS-SQL has a very few built in functions in just about every category. Here is a list of functions that are available in SQL: http://msdn.microsoft.com/en-us/library/ms174318.aspx Compare that to Excel which has functions for everything you can think of - from Bessel functions and Hankel functions, FV and yield calculations, Arabic-Roman numeral conversions - you name it, it's there.In some cases it may be possible to write SQL UDF functions, but that would not be a trivial effort by any means. Sometimes that may not even be possible because of restrictions on UDFs. And, as Michael said, in some cases SQL may have better/different ways of accomplishing the same task.In some cases, if you have corresponding CLR functions (functions written in C# or a similar CLS compliant language), you might be able to install that on your SQL server as CLR stored procedures.Since Excel exposes the functions via a COM API, you may even be able to write a CLR wrapper around it and install it as a CLR stored procedure - but I am speculating here, I can't begin to imagine what the pitfalls might be.So, Pavan, to answer your question, there is no method to automatically convert excel formulas to SQL queries. If you are using anything beyond basic mathematical and statistical functions, transferring the corresponding functionality to SQL is not an easy task. |
|
|
|
pavan2020
Starting Member
5 Posts |
Posted - 2011-03-04 : 08:41:48
|
| That's a great Explanation :) Thank you All |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
|
|
Excel Formulas to SQL Querys