Import excel custom formatting cell to sql server 2008

Question

Import excel custom formatting cell to sql server 2008

naseer78_pk

Mr or Mrs. 500

Points: 592
More actions
May 31, 2013 at 6:05 am

#278121

I have an excel sheet . having different data column One column having data like
Effiency
=======
70%
80%
50%
Column format = [<0]"";0%
when I importing data from excel to sql server this column shows blank values in sql server I am using
OPENROWSET
SELECT Effiency
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=E:\BI\Excel_Source_File\CS_Source_File\Camp201201231.xlsx',
'SELECT * FROM [Detailed_report$B7:AP39]'
)
I also use option IMEX=1 and used OPENDATASOURCE no change same issue.
Is there anyway that I can transfer data from excel to SQL Server and formatting ignored during transformation.
Urgent and thanks in advance.
Regards,
Naseer Ahmed

Viewing 7 posts - 1 through 6 (of 6 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Formatting has nothing to do with your problem. Most likely you defined a wrong range.

You can try SSIS for getting date from Excel to SQL. Using SSIS, you will see straight away what data is included from the source.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

naseer78_pk Mr or Mrs. 500 Points: 592 More actions · Answer 2

THANKS FOR REPLYING,

I will check with SSIS also same problem is there. I also check the range the range is fine.

Regards,

Naseer Ahmed

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

Is there any formula in this column?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

naseer78_pk Mr or Mrs. 500 Points: 592 More actions · Answer 4

Hi,

No, simple value with the above format. when I convert this column to General format it working fine in both format means ( by query and with SSIS) and column of values become

change. like.

Efficiency

----------

0.72345

0.54354

0.23678

Regards,

Naseer Ahmed

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

can you see these values in SSIS when configuring data source?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

naseer78_pk Mr or Mrs. 500 Points: 592 More actions · Answer 6

Yeah, Same problem when I using SSIS Package.

Regards,

Naseer Ahmed