I have a spreadsheet that's designed to look like a form. Does anyone know how I would create a SSIS package to extract specific cell values?
TIA
Have you tried using the Excel Source Adapter (http://msdn2.microsoft.com/en-us/library/ms181175.aspx)
Thanks
Anjan.
Hi,
I am a beginner using Sql server 2000 with visualstudionet 2003. When I try to export a report to excel spreadsheet. some of the page headers are displaying in separate row. page headers seems to align perfectly with the table columns and they seem fine in preview. Can someone help me with this issue.
Thanks
Bhavya
How are you doing the export ?Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||
I am exporting from Report Manager. Thanks for the resposne.
Bhavya
|||Its hard to tell without having you report design at hand, my suggestion would be to inspect the controls used one by one and eventually create a new report with importing the controls one by one.Jens K. Suessmeyer.
http://www.sqlserver2005.de
I have a SQL database that is used as a backend to an accounting application. Currently many upper management users have created spreadsheets to pull reports from this database using the backend (DSN connection). Since this is a production database I have created a script to restore the nightly backup to another server and I have denied access to the production server backend. Now I have many spreadsheets with queries set up to use a DSN connection to the production database. I would like to know if there is a known way or script to change the DSN without creating new queries.
Thanks for any help that you can provide.
SboivinGO to Settings - Control Panel - Administrative Tools - Data Sources (ODBC)
Find DSN you used in your queries and hit Configure. Here you can change server name or just follow directions.
Don't change DSN name itself. This way when queries will refer to this DSN they will be pointed to a right server.
Good Luck.|||
Quote:
Originally Posted by iburyak
GO to Settings - Control Panel - Administrative Tools - Data Sources (ODBC)
Find DSN you used in your queries and hit Configure. Here you can change server name or just follow directions.
Don't change DSN name itself. This way when queries will refer to this DSN they will be pointed to a right server.
Good Luck.
Thanks tho...
Are you specifying Import Mode by using IMEX=1 in your connection string? This forces all data to text.
The Excel driver does store what it believes to be text data preceded by the apostrophe, to force the Excel application in turn to recognize it without question as text values. This implies to me that the columns in question contain at least some values that the driver is interpreting as text.
The BOL topic on the Excel Source contains some information that explains some of the behaviors of the Jet Excel driver.
-Doug
|||Doug,
Thanks a lot for advice. I tried to put IMEX=0 (2) into a connection string, but it did not solve the problem. I wonder what the default is if IMEX parameter is not specified?
Sergey
|||Sergey,
I was not encouraging the use of Import Mode...on the contrary, this forces everything to text and would presumably add the opening apostrophe everywhere.
You may want to try allowing the Import and Export Wizard to build your "base" package and add necessary Data Conversion tasks etc. for you. Then you can customize the package afterward.
Be careful to check that the driver is recognizing your numeric columns as numeric, by examining the data types of columns in the various components.
-Doug
The following is a SAMPLE data from an excel spreadsheet. This SAMPLE data has many other fields as date. Here I have only used two date columns i.e. 28 Dec 2006 and 29 Dec 2006
This data needs to be exported into sql server 2005 table which has the fields below where I have placed the data into a table.
How can this be done please?
data:
Ref Sector Name 28 Dec 2006 29 Dec 2006
1 Sovereign RUSSIA 05 null 173.21
2 Sovereign RUSSIA 07 102.99 102.22
3 Sovereign RUSSIA 10 114.33 104.63
4 Sovereign RUSSIA 18 115.50 145.50
...
sql server table
create table tblData
(
DataID int,
Ref int,
Sector varchar(20),
Name varchar(20),
Date datetime,
value decimal(6,2)
)
DataID Ref Sector Name Date value
1 1 Sovereign RUSSIA 05 28 Dec 2006 null
2 1 Sovereign RUSSIA 05 29 Dec 2006 173.21
3 2 Sovereign RUSSIA 07 28 Dec 2006 102.99
4 2 Sovereign RUSSIA 07 29 Dec 2006 102.22
5 3 Sovereign RUSSIA 10 28 Dec 2006 114.33
6 3 Sovereign RUSSIA 10 29 Dec 2006 104.63
7 4 Sovereign RUSSIA 18 28 Dec 2006 115.50
8 4 Sovereign RUSSIA 18 29 Dec 2006 145.50
...
First import the data into temp table..
Then use the UNPIVOT operator to get the required data..
Here the complete query,
Code Snippet
/*
create table tblData
(
DataID int identity(1,1),
Ref int,
Sector varchar(20),
Name varchar(20),
Date datetime,
value decimal(6,2)
)
*/
Code Snippet
Create Table #tempdata (
[Ref] int ,
[Sector] Varchar(100) ,
[Name] Varchar(100) ,
[28-Dec-2006] float ,
[29-Dec-2006] float
);
Insert Into #tempdata Values('1','Sovereign','RUSSIA05',NULL,'173.21');
Insert Into #tempdata Values('2','Sovereign','RUSSIA07','102.99','102.22');
Insert Into #tempdata Values('3','Sovereign','RUSSIA10','114.33','104.63');
Insert Into #tempdata Values('4','Sovereign','RUSSIA18','115.50','145.50');
Go
Code Snippet
Declare @.UnPivotColumns as varchar(max)
Select@.UnPivotColumns = ''
Select @.UnPivotColumns = @.UnPivotColumns + ',[' + name + ']' from tempdb.Sys.columns
Where object_id = object_id('tempdb..#tempdata')
and column_id > 3
Select@.UnPivotColumns = Substring(@.UnPivotColumns, 2, len(@.UnPivotColumns)-1)
Insert Into tblData(ref,sector,name,date,value)
Exec ('Select ref,sector,Name,cast(date as datetime),[value]
from #tempdata unpivot([value] for [date]
in (' + @.UnPivotColumns + ') )as uptv')
Drop table #tempdata;
--To fill the missed values when the value is null
Insert Into tblData
select
fulldata.*,
tbl.value
from
(
select * from
(select distinct ref,sector,namefrom tblData) data
cross join (select distinctDatefrom tblData) dates
) fulldata
left outer join tblData tbl
on tbl.ref = fulldata.ref
and tbl.sector = fulldata.sector
and tbl.name = fulldata.name
and tbl.Date = fulldata.date
where
tbl.Date is null
Select * from tblData
Anywho, try changing this registry key to 0 in order to change the default of scanning 8 rows to unlimited:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel|||Hi!
I have set the value in the registry (TypeGuessRows=0), but i still don't get the right data type.
What I have figured out:
Lets say I have 25 rows in my column in the Excel file. It that column 13 are numeric values and 12 are text. In this case the column gets indentified as a Numeric type.
Now if there are 12 numeric and 13 text values, then the type is set to text.
Is there any way to get JET engine indentify the coulmn type as text if there is only one text value?
It really sux if the type is determined by the majority of the values, this way there is no sence to increase the number of the sampled rows, as if you have more numeric values then the type will be numeric anyway.
Any help? Ideas?
Anywho, try changing this registry key to 0 in order to change the default of scanning 8 rows to unlimited:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel|||Hi!
I have set the value in the registry (TypeGuessRows=0), but i still don't get the right data type.
What I have figured out:
Lets say I have 25 rows in my column in the Excel file. It that column 13 are numeric values and 12 are text. In this case the column gets indentified as a Numeric type.
Now if there are 12 numeric and 13 text values, then the type is set to text.
Is there any way to get JET engine indentify the coulmn type as text if there is only one text value?
It really sux if the type is determined by the majority of the values, this way there is no sence to increase the number of the sampled rows, as if you have more numeric values then the type will be numeric anyway.
Any help? Ideas?
