June 12, 2018 at 10:01 am
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 :
I'm going crazy, please help !
June 12, 2018 at 11:34 am
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
It's really a shame they got rid of sp_MakeWebTask. Man, that was a great tool.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2018 at 3:34 am
anyone has an idea?
June 13, 2018 at 5:55 am
s.dartis - Wednesday, June 13, 2018 3:34 AManyone 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
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
s.dartis - Wednesday, June 13, 2018 6:06 AMYes 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
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
s.dartis - Wednesday, June 13, 2018 6:41 AMHi 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
s.dartis - Wednesday, June 13, 2018 6:41 AMHi 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
sgmunson - Wednesday, June 13, 2018 6:45 AMs.dartis - Wednesday, June 13, 2018 6:41 AMHi 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
sgmunson - Wednesday, June 13, 2018 6:45 AMs.dartis - Wednesday, June 13, 2018 6:41 AMHi 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
Change is inevitable... Change for the better is not.
June 13, 2018 at 8:03 pm
Joe Torre - Wednesday, June 13, 2018 1:12 PMCheck 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
Change is inevitable... Change for the better is not.
June 14, 2018 at 8:54 am
Jeff Moden - Wednesday, June 13, 2018 7:43 AMsgmunson - Wednesday, June 13, 2018 6:45 AMs.dartis - Wednesday, June 13, 2018 6:41 AMHi 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