Export from SQL to Excel - dynamic transformations

Question

Export from SQL to Excel - dynamic transformations

Brendon

Ten Centuries

Points: 1252
More actions
December 9, 2002 at 9:46 pm

#105353

There is probably a simple solution to this, but here goes.
Operating on NT4 with SQL Server 7.
A weekly DTS job exports data from a SQL server database table to an Excel file.
However, the table columns can change each week (in both name and number of) ie. 20, 25, 23.
As a result, the DTS package has issues when exporting the data to Excel; namely the column transformations (source to destination) that were originally setup, keep changing each week.
I've ensured that the Excel file has the same "field" format as the table before the export, but the transformations keep causing it to fail (unless I manually go in and change them).
How can I get around this?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Try doing in a script object. Using VBScript with ADO you can loop thru the columns and move the data row by row and not have to worry about this. Simialr to doing in a VB or ASP.

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 2

Export data as rows and then use a pivot table in Excel.

Example

SQL data rows

Prod1, qty2

Prod2 qty2

After pivot

Prod1 Prod2

Qty1 Qty2

Brendon Ten Centuries Points: 1252 More actions · Answer 3

agreed, a pivot table in Excel would be easier, but I'm providing the data ready to go to the client, and I don't want them to have to process an extra step to get what they want. I could do this remotely through VB, however I wanted to keep everything simple and contained within SQL.

Antares, generated a VBScript to execute the process, and it worked successfully so far - I'll be keeping an eye on it for the next few weeks. However, you'd have to agree that Microsoft didn't make it easy for (what seems to me) a simple process. VBscript is useful, but slow work if you don't know the syntax.

Some useful links I found for writing the VBscript were:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_addf_ax_06er.asp

http://msdn.microsoft.com/msdnmag/issues/02/08/VBScriptandSQLServer2000/default.aspx

Antares686 SSC Guru Points: 125444 More actions · Answer 4

quote:
Antares, generated a VBScript to execute the process, and it worked successfully so far - I'll be keeping an eye on it for the next few weeks. However, you'd have to agree that Microsoft didn't make it easy for (what seems to me) a simple process. VBscript is useful, but slow work if you don't know the syntax.

Understandable, but if MS didn't leave some things to the developers and small companies we wouldn't have jobs or third party tools.

forsv Valued Member Points: 52 More actions · Answer 5

quote:
Antares, generated a VBScript to execute the process, and it worked successfully so far - I'll be keeping an eye on it for the next few weeks. However, you'd have to agree that Microsoft didn't make it easy for (what seems to me) a simple process. VBscript is useful, but slow work if you don't know the syntax.

Is it possible to please post the script. We have a similar situation and any help would be welcome.

Brendon Ten Centuries Points: 1252 More actions · Answer 6

sure, no problem.

This script opens the select statement from SQL, and then (on a row by row basis), builds an SQL insert statement for the record into Excel. Obviously need to swap database details and Excel details to your server name and excel file.

Function Main()

'---- CursorTypeEnum Values ----

Const adOpenForwardOnly = 0

Const adOpenKeyset = 1

Const adOpenDynamic = 2

Const adOpenStatic = 3

'---- CommandTypeEnum Values ----

Const adCmdUnknown = &H0008

Const adCmdText = &H0001

Const adCmdTable = &H0002

Const adCmdStoredProc = &H0004

dim countr

' Instantiate the ADO objects.

set mySourceConn = CreateObject("ADODB.Connection")

set mySourceRecordset = CreateObject("ADODB.Recordset")

'Set the connection properties to point to SQL Database

mySourceConn.Open = "Provider=SQLOLEDB.1;Data Source=<servername>; Initial Catalog=<database>;user id = '';password=''"

'Setup select statement

mySQLCmdText = "Select * from tablename"

'Execute the mySQLCmdText, and put the data into the myRecordset object.

mySourceRecordset.Open mySQLCmdText, mySourceConn, adOpenKeyset

If mySourceRecordset.RecordCount < 1 Then

Main = DTSTaskExecResult_Failure

Else

' Since we have records to insert into the table, create

' a Connection object to Excel via JET and do the INSERT.

dim EmpID, TerrID, myDestSQL

set myDestConn = CreateObject("ADODB.Connection")

myDestConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\excel.xls';Extended Properties='Excel 8.0;HDR=Yes'"

for countr = 1 to mySourceRecordset.RecordCount

myDestSQL = "INSERT INTO sheet1 VALUES ( "

For Index = 0 To mySourceRecordset.Fields.Count - 1

IF Index > 0 THEN

myDestSQL=myDestSQL & ","

end if

myDestSQL=myDestSQL & "'" & mySourceRecordset.Fields.Item(Index).Value & "'"

myDestConn.Execute myDestSQL

' msgbox myDestSQL

mySourceRecordset.MoveNext

End If

End Function

forsv Valued Member Points: 52 More actions · Answer 7

Thanks for the script. I was wondering if the reverse can be done too. What I mean is import EXCEL data into SQL Server, but here the EXCEL columns keep changing. Can this be tackled in A DTS package thro' a script?

Antares686 SSC Guru Points: 125444 More actions · Answer 8

Yes, you just have to open the Excel file as a recordset and loop thru the column count to find out what they all are and you can get the names that way too. Then push into a matching table in SQL. Sorry thou, I have nothing on hand doing this to give you an example.

scottsimpkins SSC Enthusiast Points: 137 More actions · Answer 9

So what if I wanted to export to pipe delimited file instead. How would this code be changed? I am struggling with exporting SP result to text file with constantly changing column names and lengths.