Excel as a Target using Data Services 4.2

[Former Member]

Hi.

i would like to know if Data Services can generate a spreadsheet (excel) as target.

i know how can i use a flat file as source, but not sure i could be a target.

thanks in advance

[former_member27665 · Accepted solution]

Try

[former_member198401]

HI Gerardo,

As Dirk mentioned DS cannot generate an XLS or XLSX file but we can use the scripting technique to generate an excel file on the drive

/***Declare a Global Variable for the Location of Xcel file

$G_PROCESSED_LOCATION ='\\\XYZ_Location\ Excel_Location';

exec('cscript','[$G_PROCESSED_LOCATION]\Excel_create.vbs', 8);

The script for Excel_create.vbs should be something like below:

strFileName = "c:\test.xls"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add()
objWorkbook.SaveAs(strFileName)

objExcel.Quit

Regards

Arun Sasi

[Former Member]

Hi Arun:

Can you provide some more details regarding the script you mentioned.

1. Where this script be placed to read data from a query?

2. Where is Excel_create.vbs script to be saved?

Thanks in adv.

Avinash

[former_member198401]

Hi Avinash,

You need to call this in a script in Data Services job before the job finishes. Use a try catch block to catch exceptions and errors.

/***Declare a Global Variable for the Location of Excel file

$G_PROCESSED_LOCATION ='\\\XYZ_Location\ Excel_Location';

exec('cscript','[$G_PROCESSED_LOCATION]\Excel_create.vbs', 8);

Make sure that you place the VB script in the location \\XYZ_Location\ Excel_Location\\Excel_create.vbs. Location can be any where... I have just shown a sample location.

The Excel_create.vbs script as it is

strFileName = "c:\test.xls"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add()
objWorkbook.SaveAs(strFileName)

objExcel.Quit

Regards

Arun Sasi

[former_member187605]

DS cannot generate Excel files, but it can generate flat files (eg. .csv). If you search this forum, you'll find discussions on how to generate an Excel from a .csv using VB.