SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Sylvain Dd
Sylvain Dd
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 8
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?

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (883K reputation)SSC Guru (883K reputation)SSC Guru (883K reputation)SSC Guru (883K reputation)SSC Guru (883K reputation)SSC Guru (883K reputation)SSC Guru (883K reputation)SSC Guru (883K reputation)

Group: General Forum Members
Points: 883303 Visits: 47900
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Joe Torre
Joe Torre
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8428 Visits: 1481
Check this topic
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (883K reputation)SSC Guru (883K reputation)SSC Guru (883K reputation)SSC Guru (883K reputation)SSC Guru (883K reputation)SSC Guru (883K reputation)SSC Guru (883K reputation)SSC Guru (883K reputation)

Group: General Forum Members
Points: 883303 Visits: 47900
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. BigGrin

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sgmunson
sgmunson
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96031 Visits: 7204
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)
Smile Smile Smile
Health & Nutrition
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search