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

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

Bubbeling data

Here's an interesting problem I ran into last week:


Date                Code    Emplid Name of the Employee
2012/12/01 --  DTA -- 123 --  John
2010/12/10 -- XFR -- 123 -- John
2012/12/05 -- DTA -- 456 -- Bert
2012/05/08 -- DTA -- 567 -- Pascal
2000/06/04 -- DTA -- 567 -- Pascal
2000/05/01 -- XFR -- 567 -- Pascal



Right. My boss asked me, in the above list, to show him the transfers for people that got transferred, and for everyone else, he needed their most recent situation.

The tricky part is : you can't filter on a code and you can't filter on a date.

Part of the solution is of course to be found in the article where I showed you how to get the two most recent items. That is, we're going to turn our date into a number :

=(year([Date])*10000)+(monthnumberinyear([Date])*100) + daynumberinmonth([Date])

First, we need to create a break on Emplid of course.

CalcDate        Date             Code    Emplid Name of the Employee
20121201 -- 2012/12/01 --  DTA -- 123 --  John
20101210 -- 2010/12/10 -- XFR -- 123 -- John
20121205 -- 2012/12/05 -- DTA -- 456 -- Bert
20120508 -- 2012/05/08 -- DTA -- 567 -- Pascal
20000604 -- 2000/06/04 -- DTA -- 567 -- Pascal
20000501 -- 2000/05/01 -- XFR -- 567 -- Pascal


And then, we can create a rank on CalcDate and get our most recent items. Easy. BUT. There was an additional requirement. If the employee had a transfer, we need that person's most recent transfer.

Easy as pie :

=CalcDate * if([Code]="XFR") then (1000) else (1)

Of course, this messes up the date-look of Calcdate a bit, but it gets the job done. When you rank on it, you get :


CalcDate            Date             Code    Emplid Name of the Employee
20101210000 -- 2010/12/10 -- XFR -- 123 -- John
20121205       -- 2012/12/05 -- DTA -- 456 -- Bert
20000501000 -- 2000/05/01 -- XFR -- 567 -- Pascal

Mission accomplished :)

Cheers

Peter

Random selection of 300 in Business Objects

Now that was cool.

My boss said. I need you to make me a Business Objects Query. The KUL University asked us to provide a random selection of employees.

They need the selection to have the right percentage of Age groups in it.
The group should have the right amount of Men and Women in it
It should represent our departments and job functions proportionately.

You'll probably need two days to set this up.

I don't know much about statistics, but I do know that a random selection of 300 out of 9000, if the selection is truly random, I don't have to bother with the rest of that stuff, the groups will be properly represented.

So, I went ahead and made a query that gave me the Company's population.

9000 people
47% Women
53% Men

Then, I applied the following little trick -- and this is the purpose of this very article:

In Business Objects Query panel, I chose to write custom SQL and I then put this little piece of code there: at the very bottom of the SQL.

ORDER BY dbms_random.value (only for Oracle)

This will randomize the list.

Next, in the query's options, I set the option to only return 300 results.

15 minutes after my boss told me to start doing a 2-day job, I finished it.

Of course, I made the same percentage calculations and obviously, they returned the same results as the master list.

If you need to do this in other DBMS systems, the code is:



MySQL:

ORDER BY RAND()
LIMIT 1


Microsoft SQL:
ORDER BY NEWID()

Enjoy

@DPValues and @DPEnd

A nice little piece of nothing this one.
I noticed on the forums, someone asking about these functions. So, I did some digging.
I built a first query that list stores :
The query became something like :
SELECT
 
Agg_yr_qt_mt_mn_wk_rg_cy_sn_sr_qt_ma.Store_name
FROM
  Agg_yr_qt_mt_mn_wk_rg_cy_sn_sr_qt_ma
Then, I based a query on it:

the query became :
SELECT
 
Article_lookup.Family_name
FROM
  Article_lookup,
  Outlet_Lookup,
  Shop_facts
WHERE
  ( Outlet_Lookup.Shop_id=Shop_facts.Shop_id  )
  AND  ( Article_lookup.Article_id=Shop_facts.Article_id  )
  AND  (
      @dpvalues('
Outlet_Lookup.Shop_name',9,1)@dpend
  )
at the next iteration, it became:
@dpvalues('
Outlet_Lookup.Shop_name',9,7)@dpend
and when I tried filtering for a number, it became:
@dpvalues('
sum(Agg_yr_qt_rn_st_ln_ca_sr.Sales_revenue)',536870916,12)@dpend

This tells me, it’s not a user-function. It’s an internal function. The user is expected to use a subquery instead.
Or, as Andreas stated on the forums :  query on query, which allows you to filter based on a different datasource.

Binabik

Soundex

At the moment, I’m brushing up a bit on my SQL knowledge. It’s paper-thin and building reports in Reporting Services does require a pretty decent bag of tricks.

The latest trick I learned is Soundex.

select firstname, LastName, SOUNDEX(lastname)
from SalesLT.Customer
where SOUNDEX(lastname) = SOUNDEX('Brown')

There you have it, the Soundex function is able to find names in a database that sound the same.

In this case, SQL will come back with names like : Brown, Brian and Bruno.

Nifty.. really nifty