March 24, 2014 at 9:48 am
Hello All,
I think this should go under:
SQL Server 2008 Integration Services
But there is no such subject, so I use this General section. (sorry ?).
Here a colleague of mine has build a DTSX script which can import an Excel file with a number of tabs.
It imports each tab into a newly created table.
The number of tabs is not predetermined.
The format of each sheet is not predetermined.
This is working fairly well, but we run into a number of problems which we solve using a stored procedure afterwards.
My question:
If we can build this functionality there must be other solutions to solve this problem and very probably better solutions than what we have now. Can anybody point me into the direction of such a solution?
Sorry that I cannot share my colleagues solution, it is not mine to share.
But I am willing to anwser questions.
Thanks for your time and attention,
Ben
March 24, 2014 at 10:43 am
Hi.
1. What problems are you running into prior to using the SP?
2. Exactly what solution are you trying to accomplish that the DTSX is not accomplishing for you?
The question is too vague to give a cogent answer.
Thanks
John.
March 25, 2014 at 6:12 am
JohnFTamburo (3/24/2014)
Hi.1. What problems are you running into prior to using the SP?
2. Exactly what solution are you trying to accomplish that the DTSX is not accomplishing for you?
The question is too vague to give a cogent answer.
Thanks
John.
Because we want to input 'any' Excel sheet, we run into a number of problems.
1. All datatypes we import are mapped on Nvarchar(2000).
2. Date/Time fields are imported as a number (in Nvarchar(2000)).
3. Money is imported as a number (in Nvarchar(2000)).
4. Number format is dependend on regional settings. (of the server ?).
5. Input goes 'slow', depending on the machine we got 30 to 60 rows per second.
Other less important issues.
6. For large spreadsheets I think we will run into problems.
7. No progression indication while this is running.
8. Limit lengths on the tab names which are converted to table names.
9. Limit lengths on column names.
Converting a datatimefield uses a complex statement, tried a number of combinations came up with:
-- The following statement converts a numeric data obtained from an Excel sheet.
-- Into the following format ccyy-MM-dd hh:mm:ss.sss
-- Example 2014-03-25 11:08:01.540
-- (shorter versions gave conversion errors e.g. nvarchar to float)
--
-- TTTTTT is a tablename.
-- CCCCC is a column where the column contains a datetime.
--
UPDATE TTTTTT
SET CCCCCC =
CONVERT(VARCHAR(30)
-- Number of seconds is added.
, DATEADD(SS
,(convert(float,CCCCCC)-convert(bigint,convert(float,CCCCCC)))*3600*24
-- Number of days is added to the 0 date of Excel.
,(dateadd(dd
,convert(float,CCCCCC)
,'1899/12/30')
))
, 121
)
WHERE ISNUMERIC(CCCCCC) = 1
This statement lacks elegance. But this is what I came up with. Shorter versions gave conversion errors.
What we are trying to accomplish is a 'generic' tool for importing Excel sheets. Excel sheets are very often used for information exchange and information storage, but lack a number of db qualities. So we would like to use Excel, but store the information in a database. Excel can then be used as general 'frontend' application for a large number of different applications. Most users are familiar with Excel sheets.
I have not covered all aspects and problems.
Thanks for your time and attention,
Ben
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply