SQL Server 2005 OPENROWSET insert into Excel - tex

So it's not a huge deal, but with Google not returning anything more recent (mostly 2007 or so) about it possibly being an Excel driver issue...I came here to ask the question as what I found as an "answer" was not acceptable without further research.

My problem:

Writing a dynamic OPENROWSET query clear and insert new data to send off a report to specific users - the base excel report contains some formulas in which it calculates off and sums based off the inserted data and the multiplication formulas seem to work fine, but unless the totals are formatted properly in Excel as "numbers" instead of text the sum will not function...

Solution must be free-form based (if there is a solution at all), I don't want to transfer all my code to SSIS...

Update/Insert code:

    SELECT @sqlu = 'UPDATE OPENROWSET(' +
    '''Microsoft.ACE.OLEDB.12.0'', ' +
    '''Excel 12.0;Database=' + @File + ';HDR=YES'', ' +
    '''SELECT * FROM [Base$B1:G601]'')
    SET 
    col1 = '''', col2 = '''', col3 = '''', col4 = '''', col5 = '''', col6 = '''' '


    EXEC (@sqlu)


    SELECT @sqli = 'INSERT INTO OPENROWSET(' +
    '''Microsoft.ACE.OLEDB.12.0'', ' +
    '''Excel 12.0;Database=' + @File + ';HDR=NO'', ' +
    '''SELECT * FROM [Base$B1:G2]'')

    SELECT col1, col2, col3, col4, col5, col6
    FROM [DATABASE].[dbo].tbl_CIM
    WHERE col1 IN(''' + @area + ''')'


    EXEC (@sqli)

The second problem and it may not be a problem for now but later - after the insert, it seems to create borders around the results. Is there also a potential way to remove those during the result transfer?

Any help or sources to research if it is a driver issue is appreciated as I could find none.

回答1:

Have you tried adding IMEX=1 to your connection string? It should force the driver to give you everything in the columns as text regardless of their interpretted format.

There's a full example of a connection string using it here.

回答2:

The only way I know of is to create a dummy row of data in the Excel sheet you are writing to. Put fake data in this row in the same format that you want the data to export in. You can hide this row if required.

This should solve both problems, however you have to deal with the dummy row, which can be annoying if you are using the data for a pivot table