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

A Mini Chart in Business Objects WEBI

This is a nifty little trick you can pull with Business Objects WEBI. Yes, only WEBI-users will be able to do this, the new functions were only added in the WEBI version of Business Objects (but that includes Rich Client of course).

This is the end-result. In the column to the right of your numbers, you get some sort of a chart that shows the proportional importance of a number (to get this to work with negative numbers, you’ll have to complicate things somewhat)
In Excel, the function is =rept(“|”, yournumber) – in Business Objects, that function is called : Fill()
In this case, I also divided my numbers by 10000 to keep the charts inside the column.
So: =fill(“|”;[Sales revenue]/10000).
Ah, I set the fontsize to 5, to make it even better.
Short and sweet, don’t you think
Enjoy,


Peter De Rop

An Exponential Trendline in Business Objects

This is going to be the third type of Trendline and I just noticed, Excel has six of them. I’m not sure how many really are used, or if there is a specific need for any in particular. So, for now, I’m gonna keep this at three, unless there is a demand for the other three. (Polynomial, Power and Moving Average).

I am going to add those three eventually, just for fun, but I’m going to keep them for a cold winter evening. Or when the question arises.

So, here’s the Exponential Trendline.

The magic number for the Exponential trendline is e. I’ve been reading about the constant e on Wikipedia and it’s a great story. You should read it too. One of the things I noticed is the amount of history that lies at the base of these trendlines. Amazing.

First, the formula:

Where c and b are constants. Each time when they say that (and here is where I find that info) I’m a bit pissed off. Especially, because they’re not constants at all. They’re calculated values. All of them.

c, as usual, is the intercept point but this time, it uses the Natural Logarithm of Y and the value (non Logarithm) of X. In fact, the calculation uses the EXP function, which is the inverse of a Natural Logarithm.

So, if i = ln(x), then exp(i) = x.

b is the coëfficient which we calculated in both other trendlines before, but this time, it uses the ln of  y.

(ah, on a sidenote, I wrote 2011 in my report, but on the test-databases that come with Business Objects, the numbers are identical for 2003 or 2006 –amazing huh )

So, I take the ln on Sales Revenue and I call it LnY

The formulas are:

=ln(<Sales revenue> in body) in report (which is called LnY)

=Average(<LnY> in body) in Report (which I call AverageLnY)

Like in the linear trendline, we calculate X-AverageX where X is a Runningcount of the Month.

=((RunningCount(<Month>) In <Month>) - <AverageX>) In <Month>

=(<LnY> - <AverageLnY>) In <Month> becomes the variable LnY-AverageLnY

and we multiply those two :

=<LnY-AverageLnY>*<X - average X>

After which we add them up. As before, we now have the top of our division:

=Sum((<LnY-AverageLnY> * <X - average X>) In  Body) In Report

For the bottom part of our division, we need the Square of X-AverageX added up.

=Power(<X - average X>, 2) In <Month>

Now we divide those numbers

There, that’s our b-variable which we can use to calculate our c variable.

=<LnY>-(<LnCoeff>*<xvalue>)

There we go, we have both variables to put into our formula.

e = 2.71828, I found that on Wikipedia.

Now, one more thing needs to be done, and it’s probably why this is called the exponential trendline, we need to take the EXP of our intercept point (our c-variable)

=Exp(<InterceptLn>) In Report

=<ExpIntercept>*(Power(2.71828 ,(<LnCoeff>*<xvalue>)))

this is the formulat shown at the top, turned Business Objects. We add that one and convert it to a table. That gives us the following chart:

And excel does this :

Again, a perfect match.

There. That gives you Linear, Logarithmic and Exponential trendlines to add to your charts.

Enjoy

Binabik

Logarithmic Trendline in Business Objects

Right, looks like I overestimated this one. After doing the previous one, Logarithmic just sounded a bit more scary. It really isn’t. In fact, it was the Microsoft site that got me a bit going. They mentioned something called “Linear Regression”. I looked up the math and it looked impressive. I had no idea, that was what I had already done in the previous article.

That has been my experience so far. A series of terms is unknown to me. This makes it fun to figure out how this stuff works because I really have to figure it out for myself.

Like the function in excel to calculate the Intercept point. On one site, I found this construction :

=index(linest(A1:A20;B1;B20);1)

Then, I found that the intercept function does exactly that. =intercept(A1:A20;B1:B20)

Ah well. it’s an amazing journey – and I’m enjoying every stop along the way.But without further ado, here’s the Logarithmic Trendline.

First off, the math is exactly the same as we had in the Linear trendline except that we don’t use x but ln(x). which is the natural logarithm of x.

for this, I use the function =Ln(runningcount(<Month>)) in <Month>

As with last time, I try to avoid using the month in a calculation, although it would work just the same.

Next up, we calculate the average of that column. In fact, that’s the only difference with the linear trendline.

=Average(LnX in Body) in Report is the function I use for that one.

Like with the linear trendline, we then need to make the difference between X and average X (this time with the logarithms of course) and Y- averageY.

=LnX – AverageLnX in Month

=Y – AverageY in Month

Next, we multiply those two with eachother and add them up.

=LnX-AverageLnX * Y-AverageY

=Sum(LnX-AverageLnX*Y-AverageY in body) in report

That last one is going to be the first part of our division.

Like last time, we now need to Square X-AverageX, but this time, it has to be the Ln version. So, the formula would be:

=sum(ln(x)-AverageLnX * ln(x)-AverageLnX  in body)in report

That’s going to be the second part of our division.

Next, we divide those two.

And we have our Direction Coëfficient.

Next up, we need the interception.

For this one, we need to subtract from AverageY

=<AverageY> – (<AverageLnX>*<LnCoëfficiënt>)

This formula, again, is the same as Linear trendline, but we use the one where we calculate the logarithm of X.

the next formula is : (Coefficient*LnX)+ InterceptLn

And then, we convert this baby to a chart.

And, it has the typical Logarithm curvature

Here is the version Excel comes up with – and again, they are identical.

There. A logarithmic trendline is perfectly possible in Business Objects. One less reason to convert your Business Objects Report into an Excel sheet.

Next up : Exponential Trendline

Have a nice day !!

Binabik

Trendlines in Business Objects

This is what we would want.A trendline in Business Objects.

 

It is not possible to let Business Objects insert a trendline in a chart automatically. It’s a feature that is simply missing. This is what I have been living with until now, it simply isn’t possible, end of story. And that makes me rebel. I want to make it possible.

Two problems : I’m not a real good mathematician. The math to achieve trendlines isn’t easy math either.

But, good news, I figured it out – and it works. Over the next few articles, I will hand you the math to add trendlines to your charts.

Our first type of trendline : A LINEAR TRENDLINE

The functions Excel uses to calculate that trendline looks like this :

and this

That’s the sort of math that makes my head spin – but after 5 minutes, I figured out how it works and then it’s easy. In case you don’t know, the x and y with a dash over their heads are averages.

The first function gets you the “direction-coëfficiënt”, the second gives you the interception point. Both are needed to calculate the trend.

Step1:

Calculate the average on both X and Y.

In this case, I used the formula : Average(<Sales revenue) in Report. If you don’t know what the “in Report” stands for, you should have a look at the articles about contexts. It’s not an easy subject.

With that, we have the average of Y and we also need the average of X. This might be a bit strange. Basically, you just need to find the middle of the X-axis of your chart.

So, we need to count how many items there are and find the middle of that. In fact, for our 12 months the middle is not 6 but 6,5 because it’s an even number of lines. So, what I end up doing is using a runningcount function :”=RunningCount(<Month)” Which I then take an average on.

the function at the end is : =Average((RunningCount(<Month>) )) In Report. which I define as variable <AverageX>

This means, that at this point, we have the two numbers with the dashes over their heads. Next, we need to subtract the X and Y values from it.

For X, this is : =((RunningCount(<Month>) In <Month>) - <AverageX>) In <Month> which I define as a variable X – AverageX

Next, we need Y – AverageY

=(<Sales revenue> - <AverageY>) In <Month> which I define as Y – AverageY

Next, we need to multiply those : =<X - average X>*<Y - AverageY> and add them up.

This gives us the first part of our division, specified at the beginning of this document.

For our second part, we need to Square x-divisionX and add them up.

that’s :=Sum(Power(<X - average X> , 2) In  Body) In Report

Last but not least, we need to divide them to get the direction Coëfficient.

Now that we calculated b, we can proceed to calculate

This translates to :

=<AverageY> - (<AverageX>*<coefficient>)

and that’s our “Intercept” which amounts to the Intercept function in Excel.

Now we can calculate the values for our Trendline. The function in this case (for a linear Trendline is:

y = mx + b

m = direction coëfficient

b = intercept

the x-Value is our runningCount on the month (remember)

So, now we transform this to a chart :

I had a great time trying to figure this one out, so I hope it’s useful to anyone. Next up Logarithmic trendlines. It won’t be easy, I can promise you that

 

ah.. last but not least, this is what that trendline looks like in Excel

Isn’t that – entirely – identical Lovely.

Cheers

Binabik

Logarithmic Scales in Charts

This is an underused feature, as far as I’m concerned. Every time when I mention these things, people’s eyes go all glazy, but it is a pretty cool feature of charts.
First off, what are logarithms, for those of you who don’t already know.
The logarithm (and I mean those that are based on the number 10) are the power to which number 10 needs to be raised to reach a certain number.
So, Log(10) = 1 because 10^1 = 10 and Log(100) = 2 because 10² = 100. In fact, until the 1970’s, logarithms were used to multiply and divide. Because if you subtract two logarithms, you divide and when you add two logarithms, you do, in fact, a multiplication.
So, it can be a way to write down large numbers.
When used in charts, they can be very useful when you’re looking at numbers that are very far apart.
Take for example this table:

It contains Sales revenue and Margin for a period of three years, with numbers varying from -173 to 205000. If we would put this on a decimal chart, the result would be this :

The good months stand out quite clearly. We did extremely well in September. But as a result, the bad results are masked. They just don’t show up, because near the 0-level, the scale is too large. A Logarithmic scale solves this :

September still stands out, but now, it is in proportion. August, which is extremely low, also shows up a lot clearer.
So, when looking at your numbers, remember to use Logarithmic scales if you want to get a clearer picture of what your data is really trying to tell you.
Binabik

Logarithmic Scales

At the moment, I'm learning a bit more about Logarithms. It's an interesting concept, especially because Business Objects has it and so far, I have never explained it, except how to switch it off.

First off, I never had much math in school, so I had to find out what Logarithms are and so I did, and it's great stuff.

I didn't know that Logarithms used to be the way (up to the 1970's) how people multiplied or calculated Powers. All done by using Logarithm tables.

In case you are one of those people who don't know what Logarithms are. Here's the deal.

In fact, there are several types of Logarithms, but the most common one is 10-based. (If you ever hear someone mention Natural Logaritms, that's another one, based on 'e').

A logarithm is the power 10 has to be calculated with, in order to reach a given number.

So, Log(100) = 2 because 10 to the power of 2 = 100.

What this gives you is a way to reduce numbers and keep their proportions. But that was not entirely what I was after. I wanted to know how a Logarithmic scale worked.

On a decimal scale, 1, 2, 3 all have the same interval: 1. So you would get a straight line.

On a Logarithmic scale, the difference between 1,2 and 3 is calculated by proportion.

When a number goes from 1 to 2, it has actually doubled, when it goes from 2 to 3, it has been multiplied by 1,5 from 3 to 4, it has been multiplied by 1,3. So in stead of a straight line, you would get a curve that indicates the proportional difference between numbers.

This is especially useful when you're looking at numbers that are far apart. Stock market information for instance can be displayed much more accurately with Logarithmic scale charts.

But I'll be looking into this a lot more in the near future.

Binabik

stdDev and stdDevP turned practical

Like many of you, I took statistics in school. And, if you’re like me, stDev sounded interesting, but you were not yet able to turn it into a practical use. Here’s how :

 

First, there’s two stdDev functions : stdDev and stdDevP.

If the list of data you’re performing this function on is complete, then you use stdDevP, otherwise, when you only selected a part of the data, use stdDev.

The way it is calculated is this :

First, every number is subtracted from the average (mean) and squared.

5,3,7 average = 5

5 – 5 = (0)² = 0

3 – 5 = (–2)²= 4

7 – 5 = (2)² = 4

Next, an average is taken and square-rooted.

sqrt(8/3) = 1,632 and that’s how much, on average, those three numbers vary from eachother.

So, if a number is higher than the average + stddev, it is statistically significatly higher than the average.

If the number is lower than the average – stddev, it is statistically significantly lower than the average.

So, now you can create charts, alerters etc using those two numbers :

Above Avg+stddev = Green

Between Avg+stddev and Avg-stddev = white

Below Avg-stddev = Orange

Scripted BO 5 excel export

 

A customer asked me to make the following script -- some time at google and some writing later, here's what I put together. It takes all the files in a given directory and converts all the BO reports into excel files (no charts though) each tab in each report is of course copied into the appropriate excel file.

Use with care..

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

dim busobj
dim strnomFichier
dim objrep
dim objExcel
dim boEditPopup
dim xlworksheet
dim strname
Dim BOApp
Dim strFilename

set BOApp= createobject("BusinessObjects.Application")
BOApp.LoginAs "USER", "PASS", False
BOApp.visible = True

Set fso=Wscript.CreateObject("Scripting.FileSystemObject")
Set f=fso.GetFolder("i:\")
Set fc=f.files

For each file in fc
    strFilename = file.name
    If Right(strFilename,3) = "rep" Then
    Set objrep = BOApp.Documents.Open("i:\"&strFilename)
    Set boEditPopup = BOApp.Application.CmdBars(2).Controls("&Edit")
    Set objExcel = createobject("Excel.Application")
    objExcel.Workbooks.Add
        objExcel.visible = True
        intreports = 1
        startnumber = BOApp.ActiveDocument.reports.count
     For  i = startnumber To 1 Step -1
        Set myrep = BOApp.ActiveDocument.reports.item(i)
        myrep.activate()
        boEditPopup.CmdBar.Controls("Cop&y All").Execute

        Set xlWorkSheet = objExcel.Worksheets.Add()
        strname = BOApp.ActiveDocument.reports.item(i).name
        strname = Replace(strname, ":", "") ' Can't contain this character
        strname = Replace(strname, "\", "") ' Can't contain this character
        strname = Replace(strname, "/", "") ' Can't contain this character
        strname = Replace(strName, "?", "") ' Can't contain this character
        strname = Replace(strName, "*", "") ' Can't contain this character
        strname = Replace(strName, "[", "") ' Can't contain this character
        strname = Replace(strname, "]", "") ' Can't contain this character
        strName = Left(strname, 31)
        xlWorkSheet.Name = strname
        xlWorkSheet.Paste
        Set xlFormatPopup = objExcel.Application.CommandBars(1).Controls("F&ormat")
        Set xlColumnPopup = xlFormatPopup.CommandBar.Controls("&Column")
        xlColumnPopup.CommandBar.Controls("&AutoFit Selection").Execute
     Next
     End If
     If Right(strFilename,3) = "rep" Then
         xlWorkSheet.Saveas "i:\"&strFilename&".xls", True
     End If
     Next

 

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