procedure to upload excel into sql server

Question

Hallo All,

Please help in determining the best procedure for the following problem.

In a user interface, there is a provision to upload excel file. This is done by admin, once/twice in a day on a regular basis.

The code that i have used to upload excel file into sql server.

select * 
into #temp FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\Files\29.09.10\working_290910.xls;HDR=YES;IMEX=1', 
    'SELECT * FROM [2XX$]')

Once the upload is successful, i am using the logic and deleting all the unnecessary records..finally i will get a set of records, which i move into the corresponding tables. Finally i DELETE the temp table that i used for uploading. Here #temp.

This procedure has many problems. As the structure of #temp is not always same as excel assumes the datatype of that particular field based on the analysis of the data it has in first n records. For a particular field sometimes it assumes, the datatype as nvarchar(255) and some times text. So few functions throw an error message on these particular fields on the execution of the stored procedure..

Last time i had a similar problem, LTRIM is not working on text field..As excel assumed the datatype for a field in table #temp as text..

So i modified the code in such a way by creating the table,

CREATE TABLE temp_invoice(
    [Concession Number] varchar(30),
    [Status] char(10),
    [Sort] char(10),
    [Task code text] varchar(60),
    [Task resp#] varchar(10),
    [Person who complete the task] varchar(50),
    [Completed] datetime
) 

so that i can fix the datatypes and then move the data from excel...

EDIT IN QUERY:-

insert into temp(Completed)  
    SELECT CASE Completed when '00.00.0000' THEN null else Completed END  
       FROM OPENROWSET  
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\Files\12.10.10\MC01_PCTA00012_20101012.xls;HDR=YES;IMEX=1', 'select * from [Tabelle1$]') AS A;

Now i am having error message based on data. If in the first few rows, the field completed has '00.00.000'. Then its working fine. If it does not have the value in first few rows, it throws an error message.

Now in my excel file the data for the field [Completed] is 00.00.0000, so the error message is "Arithmetic overflow error converting expression in the datetime data type. The statement has been terminated."

If i remove the records with date as 00.00.0000 then its working good. But i can't manuelly do it everytime. Now i need to find a work around this problem.

thanks a lot for your patience in reading such a long mail..I really have no clue, whether the procedure that i am using is correct or not. I am just changing the code to overcome the errors...

Please suggest me an appropriate procedure for this this kind of requirements.

Answer 1

Well I guess you could change your select query to this, if you allow Completed to be nullable which you should based on the problem you've described:

SELECT *,
       CASE {Completed Column name}
         when 00.00.0000 THEN null
         else {Completed Column name}
       END
FROM [2XX$]

But you are still running into the issue that Jet has where it tries to assign datatypes to columns incorrectly. Take a look at why this is happening and how to get around it in this SO post.

Answer 2

What you are describing relies on very buggy technology. You were lucky to get an error message at all. In many cases it will silently skip some values and report success. After being burned a few times, I always load such data manually from C#. Open your spreadsheet like this:

    ApplicationClass excelApp = new ApplicationClass();
    string workbookPath = string.Format(@"C:\yourfile.xls");
    Workbook workbook = excelApp.Workbooks.Open(lots of parameters here);

    Sheets sheets = workbook.Sheets;
    Worksheet UsSheet = (Worksheet)sheets.get_Item("US");

Work your way through the cells like this:

                        Range rowrange = UsSheet.get_Range("A3", "A102");
                        System.Array values = (System.Array) rowrange.Cells.Value2;
                        foreach (object value in values)
                            if (value != null)
                            {
                                string s = value.ToString();
                                //do something here
                            }

Answer 3

Hi All,

Thanks for all your responses..I have found a dirty trick for the problem, i have defined completed column data type as varchar(20). Uploading as it is..

CREATE TABLE temp_invoice(
    [Concession Number] varchar(30),
    [Status] char(10),
    [Sort] char(10),
    [Task code text] varchar(60),
    [Task resp#] varchar(10),
    [Person who complete the task] varchar(50),
    [Completed] varchar(20)
) 

INSERT INTO temp_invoice ([Concession Number],[Status],[Sort],[Task code text],[Task resp#],[Person who complete the task],[Completed])
EXEC('SELECT A.[Concession Number],A.[Status],A.[Sort],A.[Task code text],LTRIM(str(A.[Task resp#],10,0)) as [Task resp#],
A.[Person who complete the task], A.[Completed] 
FROM OPENROWSET  
(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=' + 'D:\Files\12.10.10\XCTH00759_20101012.xls' + ';HDR=YES;IMEX=1'','+'''select * from [Tabelle1$]'') AS A') ;

After the upload, i am setting the value to NULL, if it has completed date value as '00.00.0000'. Then finally changing the datatype.

UPDATE temp_invoice SET Completed = NULL
where Completed = '00.00.0000'

ALTER TABLE temp_invoice ALTER COLUMN Completed datetime

Because all the other ways are failing at a stage based on the data..