excel export import

Question

excel export import

jay-h

SSCoach

Points: 18816
More actions
March 2, 2005 at 8:52 am

#60238

We have a requirement for non IT users to update of chunks of tech data, so I am planning to use Excel files.
I use a stored procedure to create a flat file with specially named columns, and want a fairly automatic way to export it to Excel, and import it back in after the User is done. Building the export tables and parsing them back is not a problem. However, I can't seem to use DTS to handle the Excel export, because, depending on circumstances, these flat files have different columns and different column counts at different times, but DTS expects the same structure each time and fails if they don't match exactly.
I am looking for a way to script the export/import action, if necessary generating the SQL scripting on the fly to match the structure of table being exported. Does DTS have a scripting option instead of that visual editor? Is there another utility that can do this?
...
-- FORTRAN manual for Xerox Computers --

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

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

data_czar Ten Centuries Points: 1147 More actions · Answer 1

Excel is similiar to most OLE DB data source and so you can manipulate the destination fairly easily. I would use an Execute SQL task to DROP the existing table from the Excel file.

The syntax is: DROP TABLE table_name

Then use an Active X script to Build and Assign the new CREATE TABLE statement to another Execute SQL task. This will build the Excel table with the new definition. To assign the new statement to the Execute SQL Task code something like this in the ActiveX Script Task:

####################

Dim oPkg, oEST, sSQL

sSQL = "CREATE TABLE `table_name` (

`col1` VarChar (8) ,

`col2` DateTime ,

`coln` VarChar (7) )

' Get reference to Execute SQL Task Task

Set oPkg = DTSGlobalVariables.Parent

Set oEST = oPkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask

'Assign SQL Statement to Source of ExecuteSQLTask

oExecuteSQLTask.SourceSQLStatement = sSQLStatement

'Clean Up

Set oEST = Nothing

Set oPkg = Nothing

####################

The ticks around the table name are NOT single quotes. They are the key above the TAB (not the tilde). I'm not sure what its called.

You can find the name of the CREATE Execute SQL Task by looking in the Workflow properties on the Options tab.

HTH

Habib Zmerli Mr or Mrs. 500 Points: 559 More actions · Answer 2

Or use BCP to export the result to a comma separated flat file. Excel will have no problem reading the CSV file directly.

HABIB.

Kindest Regards,

Habib Zmerli (MVP)

silentnoise SSC Veteran Points: 292 More actions · Answer 3

You may also use the MS-Query option from within Excel to pull the data and then manipulate as you want.

katesl SSCommitted Points: 1743 More actions · Answer 4

I agree with tijoj-- look at the MS-Query option first to determine whether it meets your need. It saves me a TON of time. Start with a blank Excel sheet (multiple queries would each go to a separate sheet in the book). Data - Import External Data - Select Data Source - button New Source - Microsoft SQL Server. Navigate to the view that delivers your desired result and choose it. (Remember that views can be based on views based on views. You have to get all the data you want in the result/sheet defined into one view.)

I found it unsettling at first not to be able to see the query that was refreshing the data in the sheet more directly/openly that MS-Query allows, but I've developed techniques for keeping track of the refreshable Excels I have out there for users, and what views they depend on. A mysterious but useful feature is that you can move the .odc that stores the connection info from your "My Data Sources" default location to a more group-oriented location, and nothing breaks.

Now, about **importing** to the database from Excel-- I do not recommend it. We use simple web apps that give us easier and better control over data validation than is possible with Excel as the source.

_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."

jay-h SSCoach Points: 18816 More actions · Answer 5

Thanks to everyone who's contributed a reply so far.

1) To those wondering why I want to use Excel: These tables will be filled in with large chunks of catalog data by engineering and marketing personnel. There will be substantial cut/past and fill operations on blocks of information which Excel does so well, and with which these users are already familiar. Validity checking is less of an issue as these are the people most qualified to determine if the information is correct. Building that kind of interface into an app would not seem advisable.

2) The complicating issue, as I see it, is that the table to be exported (and subsequently imported) will have varying columns and column counts on different occasions. DTS and MS Query save scripts with the column structure explicity defined. I am looking to do a default export which will automatically export all columns of a table (however many) and a default import which will automatically import all columns of a spreadsheet (as if a robot user simply clicked on everything).

Perhaps an activeX object will allow this from VB or C++, though that's another learning curve -- but it may be the only way.

Thanks a lot for the input so far... keep those cards and letters coming...

...

-- FORTRAN manual for Xerox Computers --

silentnoise SSC Veteran Points: 292 More actions · Answer 6

Here is a thought you could probably use.

If you table ID does not change (you dont drop and create it everytime but just alter) to create new columns then

You could use the syscolumns table for that object ID (Table)

Select Name, ID, ColID, Colorder from syscolumns where ID = 'xxxx' will give you everything you require.

Throw this into a sheet in excel. Write a simple excel macro to generate your Select statements. Use this statement in the MSQuery for your requirements

Hope this helps.

data_czar Ten Centuries Points: 1147 More actions · Answer 7

If you use the MS Query functionality in Excel you will lose the scheduling capabilities of DTS.

You could use tijoj method of determining the columns within the ActiveX script I described earlier...

katesl SSCommitted Points: 1743 More actions · Answer 8

Our users (and they are the data owners) want to refresh the data when they want to refresh the data. SQL Server Agent scheduled jobs that include DTS packages handle our routine jobs, but don't try to tell a sales manager she has to wait for the scheduled job to refresh her data.

_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."

data_czar Ten Centuries Points: 1147 More actions · Answer 9

You don't have wait for a job to run. You can initiate the job by for example executing the sp_start_job proc from a Web Page. Another solution we use is to have a job running every X minutes that looks for a triggerring event. When the event occurs the job will start any number of other processes.