Excel to SQL

Right. This is still a work in progress -- and if any of you super-savvy coders lend a hand, I'll be most appreciative -- until such time, here is MY very own script to upload data from Excel to SQL.


Be careful when you use this though, it's not finished by a LONG shot.

Some pre-requisites:

1) Column names better not contain too many special characters
2) the tab of your excel document should be equal to the name of the table


Just paste this code into notepad and save as excelToSQL.vbs

modify the settings for the connectionString and you're all set.

Drag an excel file on top of this script and watch..

Shortcomings

The script has a VERY rudementary detection of column-type and length -- it's something I'll be developing in more detail later.

Dates are uploaded as-is -- that's a really bad one, because month and day inversion are quite likely.

Decimal values are uploaded as-is -- if there is a discrepancy between your excel and the sql -- good luck to you, the data will, or create an error, or upload wrongly.

And yes, I AM aware that I use global variables inside my functions -- I had some problems passing objects to functions or I wouldn't have.


so, handle with care and enjoy.


'-----------------------------

' excelToSQL.vbs

' Purpose : transfer an excel file to a SQL server.

' Use : drag 1 Excel file over the script and the data is transferred/

' Author: Peter De Rop

' Date: 2013/11/8

if WScript.Arguments.Count > 1 Then

  WScript.Echo "Error! Please specify the source path and the destination. Usage: excelToSQL SourcePath.xls"

  Wscript.Quit

else

    ' get timer before task begins:

    starttime = Timer()

'define variables

dim objExcel, objWorkbook, objWorksheet, iXMax, objConn, objRS, bAppend, strTablename

dim arrColNames()

dim arrColTypes()

'set objects

set objExcel = createobject("Excel.Application")

set objWorkbook = objExcel.workbooks.open(wscript.arguments(0))

set objWorksheet = objWorkbook.worksheets(1)

strTablename = replace(objWorksheet.name, " ", "")

set objConn = createobject("ADODB.CONNECTION")

bAppend = false

objConn.connectionstring="DRIVER=SQL Server;Server=yoursqlserver;Database=database;User Id=userid;Password=password;"

objConn.open

'obtain the names, types and lengths of the columns to create

        fnObtainColumnNames()

' check if the table exists or not -- ask to append, if the table already exists, if no, drop the table

set objRS = createobject("ADODB.Recordset")

objRS.activeconnection = objConn

q = "select count(table_name) as iTable from information_schema.tables where table_name='" & objWorksheet.name & "'"

objRS.open q,,1,1

if objRS.fields("iTable").value > 0 then

        if msgbox("Drop the existing table?",vbQuestion+vbYesNo,"") = vbYes  then

                q = "drop table " & objWorksheet.name

                objRS.close

                objRS.open q,, 1,3

               

                if msgbox("Store the data?",vbQuestion+vbYesNo,"") = vbYes then

                        fnObtainTypeAndLength()

                        q = fnAssembleQuery()

                        objRS.open q,, 1, 3

                        fnStoreData()

                end if

        else

                fnStoreData()

        end if

else

        fnObtainTypeAndLength()

        q = fnAssembleQuery()

        objRS.close

        file.writeline q

        objRS.open q,, 1, 3

        fnStoreData()

end if

' convert the data to the right type if needed

'close excel document

        objWorkbook.close

        objExcel.quit

        set objWorkbook = nothing

        set objExcel = nothing

        ' get timer after task completes:

    endtime = Timer()

    ' display results:

        msgbox "The task completed in " & endtime-starttime & " s"

end if

' fnObtainColumnNames(oWS)

' Purpose: this function will run through the first line of the excel worksheet and obtain each of the column names.

' it will clean them up and store them in an array.

function fnObtainColumnNames()

        dim iX, iY

        iX = 1

        iY = 1

        iXMax = 1

        do until objWorksheet.cells(iY, iX) = ""

                iXMax = iXMax + 1

                iX = iX + 1

        loop

        redim arrColNames(iXMax)

        iX = 1

        do until objWorksheet.cells(iY, iX) = ""

                arrColNames(iX) = replace(replace(replace(replace(objWorksheet.cells(iY, iX), " ", ""), "+", ""), "(", ""), ")", "")

                iX = iX + 1

        loop

end function

' fnObtainType

' Purpose: this function will run through all columns and determine what the type of each column needs to be.

' It will then store this information in an array of types

function fnObtainTypeAndLength

        dim iX, iY, iMaxLen, iType

        iX = 1

        iY = 2

       

        redim arrColTypes(iXMax)

        for iX = 1 to iXMax

                iMaxLen = 0

                iY = 2

                do until iY = 10       

                        iType = vartype(objWorksheet.cells(iY, iX))

                        if len(objWorksheet.cells(iY, iX)) < iMaxLen then

                                iMaxLen = len(objWorksheet.cells(iY, iX))

                        end if

                        iY = iY + 1

                loop

                if iType = 8 or iType = 0 or iType = 1 or iType = 12 then

                        arrColTypes(iX) = fGetType(iType) & "(" & iMaxLen + 100 & ")"

                else

                        arrColTypes(iX) = fGetType(iType)

                end if

        next

end function

Function fGetType(vType)

    Select Case vType  

        Case 0 fGetType = "varchar"

        Case 1 fGetType = "varchar"

        Case 2 fGetType = "int"

        Case 3 fGetType = "Float"

        Case 4 fGetType = "Float"

        Case 5 fGetType = "Float"

        Case 6 fGetType = "Float"

        Case 7 fGetType = "datetime"

        Case 8 fGetType = "varchar"

                Case 9 fGetType = "varchar"

                Case 10 fGetType = "varchar"

        Case 11 fGetType = "int"

        Case 12 fGetType = "varchar"

                Case 13 fGetType = "varchar"

        Case 14 fGetType = "Float"

        Case Else fGetType = "undetected"

    End Select

End Function

' purpose : assemble the table creation query

' fnAssembleQuery

function fnAssembleQuery

        strQuery = "create table " & strTablename & " ("

        iCount = 1

        do while iCount <= ubound(arrColNames)-1

                if iCount < ubound(arrColNames)-1 then

                        strQuery = strQuery & "F_" & arrColNames(iCount) & " " & arrColTypes(iCount) & ","

                else

                        strQuery = strQuery & "F_" & arrColNames(iCount) & " " & arrColTypes(iCount)

                end if

                iCount = iCount + 1

        loop

        strQuery = strQuery & ")"

        fnAssembleQuery = strQuery

end function

' purpose: run through the excel file line by line, column by column

' fnStoreData

' once the table has been created, the data needs to be uploaded.

function fnStoreData

        q = "select * from " & strTablename

        set oStoreData = createobject("ADODB.recordset")

        oStoreData.activeconnection = objConn

        oStoreData.open q,,1,3

        iXStore = 1

        iYStore = 2

        with oStoreData

                do until objWorksheet.cells(iYStore, 1) = ""

                        .addNew

                        for iCol = 1 to ubound(arrColNames)-1

                                .fields("F_" & arrColNames(iCol)) = objWorksheet.cells(iYStore, iCol)

                        next

                        .update

                        iYStore = iYStore + 1

                loop

        end with

end function

Business Objects XI R3 Service Pack 6 Weirdness

Before you install Service Pack 6, you should be aware of a few things.

1) They corrected something that has to do with Context operators.

Especially if you're filtering on a field that has Context operators in it, your filter isn't going to work anymore.

Example = [Hire Date] = Max([Hire Date]) in([Person ID]) used to be the way it worked prior to this ServicePack. Now, this needs to be corrected to:
= [Hire Date] = Max([Hire Date]in([Person ID])) in([Person ID])

The way that function works, actually should make it clear that it used to be bugged before -- but the result can be quite annoying -- the bug is corrected -- very good, but now some of your reports will not return correct data or return no data at all.


2) Variables can no longer have a name equal to one of the Data providers.

This might not seem a big thing, but it happens more than you'd think. One of the dataproviders is called employee.. and you accidently make a variable called employee. With SP6, this report won't even display anymore.

The solution we found is to open the report in an older version Rich Client, change the name of the dataprovider and publish the report.

But, Service Pack 6 does deliver some useful bugfixes. Java 1.7 works flawlessly and dates from excel no longer show up as 1/1/1970.

Cheers

Peter

Recovering SQL from old DeskI reports

Imagine the following situation:

You have some old BusinessObjects documents. The Universe is no longer available, you can't edit the query anymore, but you would like to obtain the SQL that's at the basis of it.

If this is the situation you are in, then here is your solution.

Go to Tools>Macro>Visual basic editor and create a new function:

Function test()

   a = Application.ActiveDocument.DataProviders(1).SQL
   Set fso = CreateObject("scripting.filesystemobject")
   Set file = fso.createtextfile("c:\temp\sqlcode1.txt")
   file.writeline a
End Function

next, press the triangle icon to run the function and you have query 1.. if more than 1 query exists, simply choose dataprovider(2).. etc and write them to separate textfiles.

Short .. and sweet :)

Peter

Publication workaround (Business Objects)

Due to a bug in Business Objects XI R3, you might occasionally get the following error when you try to publish a report:

"Invalid Report, please choose a different report."

Very annoying when that happens, and the only solution that I know of is a restart of the server. But there is a simple and effective workaround:

While building your publication, you have to specify a Webi or DeskI document that will serve as a distribution list for your publication.

On the SAP website, you 'll find that you have to set a restriction on that distribution document. You have to limit the number of rows returned in the query (options).

That didn't work for me, but it might work for you.

My workaround? Use a DESKI report as distribution map. The document you'll be sending around can be a WEBI document of course, but the document you use to split it, can be DeskI and you won't be getting that annoying error.

Good luck folks

Peter

The latest version of a Report (Business Objects)

Let's assume, you have this Dashboard. It runs every night and in the morning, when you open your browser, you want to see it pop up on your screen by default.

The issue you might run into, is that the Dashboard you scheduled, if you simply point to the report, you don't get the latest version, you get your original. Here's how you get around that:

First, you build your dashboard of course and you schedule it.
Go to Preferences and choose to view reports in an extra window:

Once the report ran at least once, you click right on the report.

From the menu, you should see

From the menu, pick : View latest instance.

The latest instance of the report should now open in a new window.

When the report is open, press (Function key) F11. This should put the report in Full-screen. Now, when you move the mouse over the top part of the screen, it should be possible to see the URL of the Report. Select that URL and press Ctrl-C (copy).

Next, From the New menu, we create a new InfoView Page Layout.

Click: Define content

The dialog box that now appears has a box that says: Web Address.

Paste the URL in the Web-address box.

The fun part of the trick I showed you (F11) is that that specific URL will ALWAYS give you the most recent version of the report.

Cool huh :)

Enjoy,

Peter

Ranking without duplicates in Business Objects

Here's a trick I got from James on the BOB-forum -- to a question from one of the visitors. Personally, I found the question a bit dodgy, but James worked out a nice two-step to get around the problem:

Lets asume we wanted to have a Top 10. But we wanted to disregard any duplicate values.


I'll get us a list of data to illustrate the idea:

In the above list, a bunch of duplicates exist. K = 9, but L is also 9 (we want to lose L in our top 10), G is 6 but H is also 6, we want to lose H.. etc.

The first step is to create a ranking variable [Rank]:

=Rank([Value];[Code])

Great, now we want to find out what the rank is of the previous line:

=Previous([Rank])

Nice, so now, when Previous and Rank are the same, we want to lose the line. So we create a Flag-variable:

=if([Rank]=[Previous Rank];0;1)

Watch out though, don't insert the variable into your table or:

And if you get that error, you'll have to close the document and re-open it to get this to work.

Next, we apply a filter on the table:

Now, if we apply a top 5 on the table, it will only rank the unique values.

Works like a charm.

Thanks James,

Peter

The Tip Jar

Today, my wife asked me, how my Tip Jar was coming along. (The button you might have seen on the right)

I answered her truthfully:

I love you very much dear.

She said: So, how many people visit your blog these days?

I answered her truthfully:
About 2000 a month.

Wow, she replied, then that tip-jar idea I gave you must have really payed off right?

I answered truthfuly:
Not a penny dear.

Right, she replied, I'll cancel our holiday on the Bahama's then.

I agreed.

Can I write another article? I asked with a small voice.

Sure, she replied, are you going to leave the button on?

You never know, I responded. One day, a tip on this blog will be so helpful, a visitor will make a generous donation and we can have that holiday on the Bahama's.

I suppose one can dream, she replied-- a bit miffed.

I could bundle the blog and turn it into a book -- I suggested hopefully.

How are you going to turn the stuff you write about into an intriguing murder mystery that everyone wants to read? She asked jabbingly.

Hm.. I could change the name of my blog to "The Davinci code" I replied.

It's a start. She said, as she left the room.


Cheers folks,

Peter

Compound Annual Growth rate in Business Objects

To calculate the Compound Annual Growth rate, Excel has a function called XIRR. BO doesn't have that, but that's easy to remedy. All you need to do, is work out the following formula:

So, now we need a bunch of numbers and see where we get:

Right -- this has everything to do with investments, something I know nothing about, but here's some numbers to get us started :

On Jan 1 2010, we invest 10000€
On Jan 1 2011, our investment has grown to 13000€
On Jan 1 2012, our investment has grown to 15000€
and
finally, On Jan 1 2013, our investment has now a value of 19500€

The table looks like this in BO:

First, we'll divide our ending value by our beginning value -- interesting challenge.

Our last date would be : =Max([Date]) In Report = 1/1/2013

Our first date would be : =Min([Date]) In Report = 1/1/2010

So, our formula for that division becomes :

=[Value] where ([Date]=Max([Date]) In Report) / [Value] where ([Date]=Min([Date]) In Report)

I'll call this one basefigure

Now, we need to find out how many years it took:

The formula for that one is rather easy :

=Year(Max([Date]) In Report) - Year(Min([Date]) In Report)

And we divide 1 by that figure:

=1/(Year(Max([Date]) In Report) - Year(Min([Date]) In Report))

this one, I'll call exponent

There. Now, the next step would be to raise our first figure to the power of that second figure and subtract 1.

=Power([Basefigure];[Exponent])-1

Our Compound Annual Growth rate would be : 0.25 -- I guess those figures occurred in a parallel universe :)

Cool

Cheers folks

Peter

A running balance in Business Objects

I think it's quite clear by now, that it is my objective, never ever to need Excel -- well, almost. The charts in Excel do exceed BO's, but math -- naaaah.

Our next venture, is calculating a running balance.

In a running balance, we have a series of deposits and a series of withdrawals and we want to
keep track of the total of our balance at all times.

Here's how:

First, we need two columns. One has our deposits, the other has the withdrawals.

Now, we want to show a third column, with our balance -- on 1/1/2013, this should be : 180, on 2/1/2013, this should be -620 etc.

This is laughably easy really -- since BusinessObjects has a function Excel doesn't have: RunningSum

=runningsum([Deposit]) - runningsum([Withdrawal])

The result is :

In Excel this would be something like :

A	B	C
Deposits	Withdrawals	Balance
$1,000	$625	=SUM(A2,-B2)
$1000	740	=SUM(C2,A3,-B3)

Looks like our balance is in the red -- but with the economic times, that seems right :)

Cheers folks

Peter

Trimmed mean in Business Objects

This is a challenging one.

This is what the formula looks like:

But all this means is:

You pick a value k. And the % k number of values, is dropped while calculating the mean.

So, if k = 10% and you have 100 values, you'll be dropping the bottom 10 and the top 10 values and calculate the mean on the remaining 80.

I think it will be useful, to use ranking to calculate these.

First, lets calculate, from our list of products, with k = 30%, how many values we would need to drop.

We have 12 values. 30% of 12 = 4, so we'll be dropping the top 4 and the bottom 4 values.

Let's calculate this in Business Objects.

V_k = 0.3

=round(count([Lines]) * V_k,0)

which I will name V_ProductsToKeep

We need an integer of course, so round it is.

So, now we need to find out, what the 4th highest value is and filter everything out that is higher or equal. And we need to find out what the 4th lowest value is and filter everything out that is lower or equal.

=NoFilter(Rank([Sold at (unit price)];Top))

This gives us the Top  -- returning a value -- if V_ProductsToKeep is higher or equal than that value, we filter the value out.

Same with the lowest:

=NoFilter(Rank([Sold at (unit price)];Bottom))

This gives us the Bottom -- returning a value -- if V_ProductsToKeep is lower or equal, we filter that value out.

=If([V_Bottom] <= [V_ProductsToKeep]) Then (0) ElseIf ([V_Top]<=[V_ProductsToKeep]) Then 0 Else 1

Nice, now all we need is a formula, that determines what the sum is of [Sold at (unit price] where V_Evaluate = 1

=Sum([Sold at (unit price)])Where([V_Evaluate]=1) / ([V_NProducts] - ([V_ProductsToKeep]*2))

There. Not easy at all, but it works.

Our Trimmed mean with k = 33% is 166.6 -- higher than our regular mean, because we dropped the bottom and top 33% of the values.

If we change k to 0.1, the trimmed mean is:165.8

Good fun -- not that easy to do. I think that's going to be it for averages for now :)

Quadratic average in Business Objects

Next up: the Quadratic mean.

As usual, first the function:

This is quite a different animal. The application of it isn't really sales, on wikipedia it even says that this applies to electricity, but I don't have those kind of figures -- it'll work just as good with my numbers. It no longer takes into account the number of items sold, so our starting table is going to look a bit different:

From the looks of it, we first need a count of the number of entries and divide 1 by that number. In our case, this gives us the following formula:

=1/count([Lines])

In our case, this is 1/12 or : 0.08.

Next, we have to take the second power of each unit price.. easy peasy.

=power([Sold at (unit price)];2) 

And next, we need to add those up:

=Sum(Power([Sold at (unit price)];2))

Next, we have to multiply those and take the square-root.

=sqrt(Sum(Power([Sold at (unit price)];2))*(1/Count([Lines])))

The Quadratic mean of the unit price is 167 -- so far, that's the highest mean we got.

More averages coming up.

Peter

Weighted Harmonic Average in Business Objects

So, here's the second part of the Averages series. And this one might prove to be just a bit more challenging.

This time, we're going to calculate the Weighted Harmonic Average.

The formula I found on Wikipedia looks like this:

The table we're starting with, again, looks like this:

Our Average is 165.8, the median is 165.05 and the Weighted average is 163. Lets see what the Harmonic Weighted average gives us.

Lets add up our Unit Prices.

=sum([Quantity sold])

Now, we divide each quantity sold by the unit price and we add them up. Sounds weird to me, but, why not.

(Sum([Quantity sold]/[Sold at (unit price)])

I think I'm starting to like this particular average :)

Last step, we divide.

=Sum([Quantity sold]) / (Sum([Quantity sold]/[Sold at (unit price)]))

Tada, our Weighted Harmonic Average : 161.52

More averages to come :)

Peter

Calculating averages in Business Objects

Time for some averages.

Obviously, I'm not going to deal with the average function, that seems straightforward enough. This article and following articles will be about some other averages:


Weighted mean, Weighted harmonic mean, Quadratic mean, Winsor mean, Truncated mean, Mean of Angles, and so on and so forth. I didn't know there were that many means, so it's gonna be fun figuring some of them out. As I already pointed out in previous articles, I'm not a mathematician, so I'm learning this stuff as I write it.

Let's start with the Weighted mean.

The formula on Wikipedia looks like this:

Fortuntately, I had the chance of learning to read these syntaxes when I wrote the articles on trendlines. So, it's going to be just a little bit easier.

Basically, it's just going to be adding up a bunch of multiplications and dividing it again. Lets get a bunch of numbers:

Our starting table is this one -- as usual, I'm taking eFashion to start from:

We're interested in the weighted average of the Unit Price. The average is 165.80 and the median would be:165.05.

First, we multiply Quantity sold and unit price and we add them up.

In a column to the right, I'm adding =[Quantity sold]*[Sold at (unit price)]

Nice. Next, we divide that big number by the total of the quantities:

=sum([Quantity sold]*[Sold at (unit price)])/sum([Quantity sold])

This results in:

Our weighted mean is: 163

More means coming up.

Peter

ForceMerge

Quite a useful function I should say. I think it just might be the solution to much of the Multivalue errors I've been having in my job as a Reporting Officer.

Here's how ForceMerge works :

First, you make two queries. One contains the dimension you don't have in your second universe and a common field,the other one contains that common field and some calculated data.

The left table contains states and cities, the right one contains cities and Sales figures. What we would really want, is State and Sales figures.

So, we move the sales figures in with the left table ( first we merge the city object of course).

There.. looking great. Just, we don't want the city. we want the state. But when we remove it:

We get ourselves a cartesian product. The solution to this issue is: Forcemerge().

When we change the formula for the Sales figures to : =ForceMerge([Sales Revenue]), the following table emerges:

Ain't that a beauty :)

That's all folks.

Enjoy,

Peter

business objects operator "????"

I noticed that someone searched for these terms.

This is probably going to be short and sweet:

business objects operator "????"


Whenever you find ???? inside a variable, it simply means that the object that was used 

to create this particular variable, is no longer in the query.

Let's say that you create a variable Year. And you use the following formula:

=year([Sales date])

and then, remove the object [Sales date] from your query.

The object Year will then contain =year(????)

Not good :)

Cheers