Post reply

Problem numeric value to text value when i export table to Excel with OLEDB

  • June 12, 2018 at 10:01 am

    #351091

    Hello,

    I'm trying to export a table to Excel but whatever data type i choose when i create the table in Excel, it keeps creating columns in Standard type and my numbers are converted to text value, here is a simple exemple :

    DECLARE @objExcel INT,
    @hr INT,
    @command VARCHAR(255),
    @strErrorMessage VARCHAR(255),
    @objErrorObject INT,
    @objConnection INT,
    @bucket INT,  
    @DDL VARCHAR(2000),
    @DataSource VARCHAR(100),
    @Worksheet VARCHAR(100)=NULL,
    @ConnectionString VARCHAR(255),
    @document int
    Select @ConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%DataSource;Extended Properties="Excel 12.0 Xml"',   
    @DDL='Create table Feuil1(No_id int, valeur decimal(18,2)No_id int, valeur decimal(18,2))',   
    @DataSource ='d:\testExport.xlsx'
    SELECT @ConnectionString = REPLACE (@ConnectionString, '%DataSource', @DataSource)
    print @ConnectionString
    EXEC @hr = sp_OACreate 'ADODB.Connection', @objconnection OUT
    print @hr
    EXEC @hr=sp_OASetProperty @objconnection,'ConnectionString', @ConnectionStringprint @hr
    print @objconnection
    EXEC @hr=sp_OAMethod @objconnection, 'Open'print @hr
    print @DDL
    EXEC @hr=sp_OAMethod @objconnection, 'Execute',@Bucket out , @DDL
    print @hrexec sp_OADestroy @objconnection
    declare @cmd nvarchar(4000)
    set @cmd='INSERT INTO OPENDATASOURCE ( ''Microsoft.ACE.OLEDB.12.0'', ''Data Source=d:\testExport.xlsx;Extended Properties="Excel 12.0 Xml"'')...[Feuil1$]  SELECT 1,2.00' 
    print @cmd 
    exec (@cmd)

    Result in Excel :

    https://social.msdn.microsoft.com/Forums/getfile/1281802

    I'm going crazy, please help !

  • June 12, 2018 at 11:34 am

    #1993739

    I suggest SSIS for this type of task. It's easier to connect from Excel to SQL Server to get back data in the correct data types using the Data tab on the Ribbon.

  • June 12, 2018 at 1:08 pm

    #1993763

    It's really a shame they got rid of sp_MakeWebTask.  Man, that was a great tool.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 13, 2018 at 3:34 am

    #1993812

    anyone has an idea?

  • June 13, 2018 at 5:55 am

    #1993819

    s.dartis - Wednesday, June 13, 2018 3:34 AM

    anyone has an idea?

    Joe Torre gave you an idea, and it was a good one.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • June 13, 2018 at 6:06 am

    #1993820

    Yes but i really need to do it through a SQL request not SSIS or VBs or anything else.

  • June 13, 2018 at 6:30 am

    #1993828

    s.dartis - Wednesday, June 13, 2018 6:06 AM

    Yes but i really need to do it through a SQL request not SSIS or VBs or anything else.

    Well, you need to adopt a different way of doing it.   Your problem is because you don't have the data types of the columns set correctly to begin with.  Your assumption that the CREATE TABLE statement takes care of the problem is inaccurate.   The Excel sheet is going to treat those columns as text precisely because you closed the spreadsheet and then re-opened it, where it's going to guess at the data type by looking at the existing data, which is none, so it will go by the column data it has, which is text. If you insert the values in the same process that creates your table, you might get a different result.   Why don't you do the insert there instead?    Excel has no retention of column data types, so it figures it out at run-time and only when it thinks it needs to.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • June 13, 2018 at 6:41 am

    #1993831

    Hi Steve and thanks for your help.
    I ve closed the connexion because i've found someone who had the same problem with vb and he said there was a bug with the OLEDB and it has to close and open again to make it works.

    But in both case it doesn't work, if i close or not the issue is the same, and even if i dont do the isnert, only the create table, all my columns will be on data type Standard instead of the data type i define.

    I really don't understand what's happening, if i run the create table with an incorrect datatype like this :
    Create table Feuil1(No_id testdatadatatype, valeur decimal(18,2)No_id int, valeur decimal(18,2))

    the request will fail so it means it has to receive a good datatype to work, but whatever correct datatype i ask, it will become Standard in Excel file.

  • June 13, 2018 at 6:45 am

    #1993832

    s.dartis - Wednesday, June 13, 2018 6:41 AM

    Hi Steve and thanks for your help.
    I ve closed the connexion because i've found someone who had the same problem with vb and he said there was a bug with the OLEDB and it has to close and open again to make it works.

    But in both case it doesn't work, if i close or not the issue is the same, and even if i dont do the isnert, only the create table, all my columns will be on data type Standard instead of the data type i define.

    I really don't understand what's happening, if i run the create table with an incorrect datatype like this :
    Create table Feuil1(No_id testdatadatatype, valeur decimal(18,2)No_id int, valeur decimal(18,2))

    the request will fail so it means it has to receive a good datatype to work, but whatever correct datatype i ask, it will become Standard in Excel file.

    Then maybe you should try ODBC instead of OLEDB.   But even if that doesn't work, you really have to load the data all at the same time to have any shot.   I'd actually consider BULK OUTPUT first and then if that doesn't work, use SSIS instead.  Ultimately, though, if you can't use new tools, don't expect to accomplish much without copy and paste....

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • June 13, 2018 at 6:46 am

    #1993835

    s.dartis - Wednesday, June 13, 2018 6:41 AM

    Hi Steve and thanks for your help.
    I ve closed the connexion because i've found someone who had the same problem with vb and he said there was a bug with the OLEDB and it has to close and open again to make it works.

    But in both case it doesn't work, if i close or not the issue is the same, and even if i dont do the isnert, only the create table, all my columns will be on data type Standard instead of the data type i define.

    I really don't understand what's happening, if i run the create table with an incorrect datatype like this :
    Create table Feuil1(No_id testdatadatatype, valeur decimal(18,2)No_id int, valeur decimal(18,2))

    the request will fail so it means it has to receive a good datatype to work, but whatever correct datatype i ask, it will become Standard in Excel file.

    Datatypes do not exist in Excel, only display formats.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • June 13, 2018 at 6:53 am

    #1993837

    sgmunson - Wednesday, June 13, 2018 6:45 AM

    s.dartis - Wednesday, June 13, 2018 6:41 AM

    Hi Steve and thanks for your help.
    I ve closed the connexion because i've found someone who had the same problem with vb and he said there was a bug with the OLEDB and it has to close and open again to make it works.

    But in both case it doesn't work, if i close or not the issue is the same, and even if i dont do the isnert, only the create table, all my columns will be on data type Standard instead of the data type i define.

    I really don't understand what's happening, if i run the create table with an incorrect datatype like this :
    Create table Feuil1(No_id testdatadatatype, valeur decimal(18,2)No_id int, valeur decimal(18,2))

    the request will fail so it means it has to receive a good datatype to work, but whatever correct datatype i ask, it will become Standard in Excel file.

    Then maybe you should try ODBC instead of OLEDB.   But even if that doesn't work, you really have to load the data all at the same time to have any shot.   I'd actually consider BULK OUTPUT first and then if that doesn't work, use SSIS instead.  Ultimately, though, if you can't use new tools, don't expect to accomplish much without copy and paste....

    i can't bulk output to Excel, only CSV no?

    Have you a sample of export ODBC to Excel file?

  • June 13, 2018 at 7:43 am

    #1993847

    sgmunson - Wednesday, June 13, 2018 6:45 AM

    s.dartis - Wednesday, June 13, 2018 6:41 AM

    Hi Steve and thanks for your help.
    I ve closed the connexion because i've found someone who had the same problem with vb and he said there was a bug with the OLEDB and it has to close and open again to make it works.

    But in both case it doesn't work, if i close or not the issue is the same, and even if i dont do the isnert, only the create table, all my columns will be on data type Standard instead of the data type i define.

    I really don't understand what's happening, if i run the create table with an incorrect datatype like this :
    Create table Feuil1(No_id testdatadatatype, valeur decimal(18,2)No_id int, valeur decimal(18,2))

    the request will fail so it means it has to receive a good datatype to work, but whatever correct datatype i ask, it will become Standard in Excel file.

    Then maybe you should try ODBC instead of OLEDB.   But even if that doesn't work, you really have to load the data all at the same time to have any shot.   I'd actually consider BULK OUTPUT first and then if that doesn't work, use SSIS instead.  Ultimately, though, if you can't use new tools, don't expect to accomplish much without copy and paste....

    Apologies for my ignorance.  What is "BULK OUTPUT"?  Is that an ODBC command?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 13, 2018 at 1:12 pm

    #1993899

    Check this topic

  • June 13, 2018 at 8:03 pm

    #1993936

    Joe Torre - Wednesday, June 13, 2018 1:12 PM

    Check this topic

    Not sure if you're talking to me on that message, Joe.  I don't see anything on that topic about BULK OUTPUT.  'f course, I might be missing it too.  It's been one of those days. 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 14, 2018 at 8:54 am

    #1994012

    Jeff Moden - Wednesday, June 13, 2018 7:43 AM

    sgmunson - Wednesday, June 13, 2018 6:45 AM

    s.dartis - Wednesday, June 13, 2018 6:41 AM

    Hi Steve and thanks for your help.
    I ve closed the connexion because i've found someone who had the same problem with vb and he said there was a bug with the OLEDB and it has to close and open again to make it works.

    But in both case it doesn't work, if i close or not the issue is the same, and even if i dont do the isnert, only the create table, all my columns will be on data type Standard instead of the data type i define.

    I really don't understand what's happening, if i run the create table with an incorrect datatype like this :
    Create table Feuil1(No_id testdatadatatype, valeur decimal(18,2)No_id int, valeur decimal(18,2))

    the request will fail so it means it has to receive a good datatype to work, but whatever correct datatype i ask, it will become Standard in Excel file.

    Then maybe you should try ODBC instead of OLEDB.   But even if that doesn't work, you really have to load the data all at the same time to have any shot.   I'd actually consider BULK OUTPUT first and then if that doesn't work, use SSIS instead.  Ultimately, though, if you can't use new tools, don't expect to accomplish much without copy and paste....

    Apologies for my ignorance.  What is "BULK OUTPUT"?  Is that an ODBC command?

    Should have said BCP...   got used to calling it that a long time ago, and the label stuck.   Best that BCP can do is create a CSV, but that may be sufficient for something this simple...

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

Viewing 15 posts - 1 through 14 (of 14 total)

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