Imorting Excel 07 data into SQL Server 2005

I have been trying to use the dtswizard.exe in SQL Server 2005 to import Excel 07 spreadsheets with no success.

Any idea how this can be done?

As a work around I have been importing the Excel 07 data into Access 07 and saving the database as the older mdb extension, THEN importing the mdb file into SQL Server 2005. Seems silly though.

To access native excel 2007 data, create an OLEDB connection manager (not an Excel connection) with the following connection string specific to the source file.

Data Source=c:\data\myfilehere.xlsx;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties="Excel 12.0;HDR=YES";

Access the spreadsheet in a dataflow using an OLEDB source with its select statement set to "select * from [Sheet1$]", replacing Sheet1$ with the name of the appropriate worksheet.

You will need the OLEDB provider, naturally, which comes with the install of Office 2007.

IMEX=1 in OpenDataSource

I'm having an issue with selecting data from an Excel 2002 spreadsheet using
OpenDataSource (or DTS for that matter). Excel will sometimes identify the
column type as numeric and ignore all character data in the column,
returning nulls instead of the data (known issue -
http://support.microsoft.com/?id=194124). I've editted my registry to force
the Jet drive to always use text as the data types for columns; however, the
registry is ignored unless the "IMEX=1" switch appears in the connection
string. All the examples of using the switch that I've found have been for
using it with the command object in VBScript. Can this switch be used with
the OpenDataSource TSQL command? If so, what is the syntax and do I need to
have Office installed on the sql server?
Thanks!!!
WalterHi
It can be used according to http://www.connectionstrings.com/ e.g.
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended
Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
There is also a MAXSCANROWS extended property
http://support.microsoft.com/defaul...kb;en-us;278973
John
"Walt Mallon" wrote:

> I'm having an issue with selecting data from an Excel 2002 spreadsheet usi
ng
> OpenDataSource (or DTS for that matter). Excel will sometimes identify th
e
> column type as numeric and ignore all character data in the column,
> returning nulls instead of the data (known issue -
> http://support.microsoft.com/?id=194124). I've editted my registry to for
ce
> the Jet drive to always use text as the data types for columns; however, t
he
> registry is ignored unless the "IMEX=1" switch appears in the connection
> string. All the examples of using the switch that I've found have been fo
r
> using it with the command object in VBScript. Can this switch be used wit
h
> the OpenDataSource TSQL command? If so, what is the syntax and do I need
to
> have Office installed on the sql server?
> Thanks!!!
> Walter
>
>|||Got it!!! Had some quotes in the wrong place:
SELECT *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\CEXP-Mixed-Analyze.xls;Extended properties="Excel
8.0;HDR=YES;IMEX=1"')...[data$]
That one worked!!!
Thanks.
"Walt Mallon" <waltmallon@.hotmail.nospam.com> wrote in message
news:e3M07$AyFHA.2652@.TK2MSFTNGP14.phx.gbl...
> I'm having an issue with selecting data from an Excel 2002 spreadsheet
> using OpenDataSource (or DTS for that matter). Excel will sometimes
> identify the column type as numeric and ignore all character data in the
> column, returning nulls instead of the data (known issue -
> http://support.microsoft.com/?id=194124). I've editted my registry to
> force the Jet drive to always use text as the data types for columns;
> however, the registry is ignored unless the "IMEX=1" switch appears in the
> connection string. All the examples of using the switch that I've found
> have been for using it with the command object in VBScript. Can this
> switch be used with the OpenDataSource TSQL command? If so, what is the
> syntax and do I need to have Office installed on the sql server?
> Thanks!!!
> Walter
>

Images and PDF Export POST SP2

After installing RS SP2, images included in my reports display fine in HTML
View, Excel View and TIFF View, but when exported to PDF they are not
displaying properly (they appear garbled, much like a scrambled TV signal).
Any ideas or thoughts?On Follow Up.. this appears to be images other than JPEG's, which according
to documentation are all rendered as PNG's.
"Rob Johnson" wrote:
> After installing RS SP2, images included in my reports display fine in HTML
> View, Excel View and TIFF View, but when exported to PDF they are not
> displaying properly (they appear garbled, much like a scrambled TV signal).
> Any ideas or thoughts?

Image Problem-Export To Excel

Hi,
I am using embeded image in my Report.But when it is exporting into Excel,i
could not get any image.
Am i missing anything?or is it not possible?
Thanks in advance.
DibakarTry include the image in your project or give some server URL so that Excel
will also get the image from that particular location.
Amarnath, MCTS
"Dibakar" wrote:
> Hi,
> I am using embeded image in my Report.But when it is exporting into Excel,i
> could not get any image.
> Am i missing anything?or is it not possible?
> Thanks in advance.
> Dibakar|||Thanks for your reply.But i set the background image of a TextBox,which is
not visible in Excel.
So,what to do in this case?i have tested all the possible Image
format(Embeded/Project/Web).Pls help.
"Amarnath" wrote:
> Try include the image in your project or give some server URL so that Excel
> will also get the image from that particular location.
> Amarnath, MCTS
>
> "Dibakar" wrote:
> > Hi,
> > I am using embeded image in my Report.But when it is exporting into Excel,i
> > could not get any image.
> > Am i missing anything?or is it not possible?
> > Thanks in advance.
> > Dibakar|||On Mar 7, 4:44 am, Dibakar <Diba...@.discussions.microsoft.com> wrote:
> Thanks for your reply.But i set the background image of a TextBox,which is
> not visible in Excel.
> So,what to do in this case?i have tested all the possible Image
> format(Embeded/Project/Web).Pls help.
> "Amarnath" wrote:
> > Try include the image in your project or give some server URL so that Excel
> > will also get the image from that particular location.
> > Amarnath, MCTS
> > "Dibakar" wrote:
> > > Hi,
> > > I am using embeded image in my Report.But when it is exporting into Excel,i
> > > could not get any image.
> > > Am i missing anything?or is it not possible?
> > > Thanks in advance.
> > > Dibakar
Unfortunately, this seems to be a limitation in the Excel rendering
from the export. It should work fine when exported to PDF or web
archive. To get around the issue, I would suggest including the image
in the report and then using a textbox directly below it (if this is
suitable). Sorry I could not be of more assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Ok, you said embedded image, I thought you have pasted image control,
No Excel ignores all the background image. You can check the other post for
suggestion, in other words, you can place a image control and on top of it
place the textbox and right click properties and click on "Send to back".
Amarnath, MCTS
"Dibakar" wrote:
> Thanks for your reply.But i set the background image of a TextBox,which is
> not visible in Excel.
> So,what to do in this case?i have tested all the possible Image
> format(Embeded/Project/Web).Pls help.
> "Amarnath" wrote:
> > Try include the image in your project or give some server URL so that Excel
> > will also get the image from that particular location.
> >
> > Amarnath, MCTS
> >
> >
> > "Dibakar" wrote:
> >
> > > Hi,
> > > I am using embeded image in my Report.But when it is exporting into Excel,i
> > > could not get any image.
> > > Am i missing anything?or is it not possible?
> > > Thanks in advance.
> > > Dibakar

Image problem in Report

Hi,
I copied an image on report and call this report from VB. But when i export it in Excel, data properly exported but not the image. How can it be possible that when we export report image exported with it.
ThanksDid you try exporting to Word? Which version of CR are you using?

Image in Report Header

I embeded an image in my report.When i export it to PDF and Excel,it works fine but in HTML in does not show..

Can anyone tel me why..

Thanks,

Plengski

This would be a SQL Server Reporting Services specific question, i would recommend asking it in this forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=82&SiteID=1

Thanks,

Othmane

Image in Report Header

I embeded an image in my report.When i export it to PDF and Excel,it works fine but in HTML in does not show..

Can anyone tel me why..

Thanks,

Plengski

This would be a SQL Server Reporting Services specific question, i would recommend asking it in this forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=82&SiteID=1

Thanks,

Othmane