ksfok
asked on
SQL 2000 DTS import Excel data
Given an Excel Workbook with several sheets, how can the following be best done using SQL2000 DTS?
1) Find the target worksheet
2) Take out irrevelant rows that contain blank filler or header/footer text
3) Feed a SQL2000 table with the usable Excel rows.
Can the above be accomplished with data transformation tasks. Here's what I plan to do:
1) Create a Excel datasource
2) Create a one large column staging table
3) Create a data transformation task that import numeric rows from the Excel sheet to the staging table
4) Create the result table with appropriate columns.
5) Create a data transformation task that import map/feed columns from the staging table to the result table using string manipulations.
Please advise.
Thanks.
1) Find the target worksheet
2) Take out irrevelant rows that contain blank filler or header/footer text
3) Feed a SQL2000 table with the usable Excel rows.
Can the above be accomplished with data transformation tasks. Here's what I plan to do:
1) Create a Excel datasource
2) Create a one large column staging table
3) Create a data transformation task that import numeric rows from the Excel sheet to the staging table
4) Create the result table with appropriate columns.
5) Create a data transformation task that import map/feed columns from the staging table to the result table using string manipulations.
Please advise.
Thanks.
You should know that Excel ODBC is Single-user at all times, before you will be trapped in access problems.
ASKER
Please kindly elaborate steps with details. Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In your workbook you have to mark data area and assign it name. Such area is than "table" on ODBC side. test it in DTS designer with help of Preview data.