Tools: SQL Server 2K, Excel 2000
Hi,
I have an Excel report worksheet with formatted headings. What I want
to do is to export data from the SQL server into a specific cell of the
excel file. Is this doable? Can somebody give me some direction on how
to accomplish this?
I appreciate any suggestions.
Edgar J.
Hi
I'm struglling with this, can anyone assist. I'd go as far as to say, there is $$ up for the solution that works.
Here's the routine..
1) Upload file to server - works, no problem.
2) Extract cell ref A1-ws1 & A1-ws2 from the newly uploaded file (myXLSFileName - WHERE ProjectID = Session("strProjectID").
3) Update tMyTable.myfield1 with A1-ws1 & tMyTable.myfield2 with A1-ws2 WHERE ProjectID = Session("ProjectID")
All efforts greatly received!.
Thanks
I want to copy FirstName and LastName cells from table1 to table2. Which FirstName and LastName cells depend on the username, i.e. which person has logged in.
Q1) How can this be done? (I have chosen to SELECT from Table1 and output to variables (C# code); then INSERT these variables into Table2. [Finding it very difficult]).
Any suggestions/comments would be appreciated. Thank you in advance.
I tried to scheduled and save a Report Via SSRS. The Exporting file name need to be in this format -- 17July2013_NewSystem.csv
I have tried using @timestamp in file name, but the file was saved like this 2013_07_17_090529_NewSystem
How to specify the filename to get the desired format ie. the file name deposited in the folder be --
17July2013_NewSystem.csv
18July2013_NewSystem.csv (for tomorrow and so on)
We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
I am very early on in developing a website to track issues with projects which is tied to a SQL database. I have my Projects Table, my Users Table, and am creating a third table to track issues. I'm wondering what is the best way to assign specific users to specific data/projects. The user should only be able to view & update the projects assigned to him. He should not be able to see other projects. What is the best way to assign projects/data to the users to make sure they are only viewing their data?
View 1 Replies View RelatedMy Report consists of a matrix and table . I Kept the matrix inside the table because i need to calculate subgroups total as well as Grand Total using the group i provided inside the table . The issue when i tried to render the report to Excel it shows "Data Regions within table/matrix cells are ignored." I dont know how to solve it . When i google it . It says that its a Microsoft SSRS limitation . Anyway i could solve it any help will be appreciated
Regards
Praveen John
+91-9895074288
"Frankly, my dear, I don’t give a damn"
My Report consists of a matrix and table . I Kept the matrix inside the table because i need to calculate subgroups total as well as Grand Total using the group i provided inside the table . The issue when i tried to render the report to Excel it shows "Data Regions within table/matrix cells are ignored." I dont know how to solve it . When i google it . It says that its a Microsoft SSRS limitation . Anyway i could solve it any help will be appreciated
View 1 Replies View Related
When we try to export to excel a SQL client report containing tables that were grouped based on some data in two tables of a dataset I am receiving the following error
"Data Regions within table/matrix cells are ignored".
I'm having a real hard time coming up with a solution to this problem. I created a custom gridview control from Dino Esposito's "Extending Gridview" article which autogenerates a checkbox column that allows for multiple record selection. Once a user checks a box, the entire row gets selected. I added a dropdown list on the top of the page that has only two options, "Yes" or "No". What I'm trying to do is update a boolean column called "contract" (I'm using the Pubs sample database) for all selected rows (via checkboxes checked) depending on whether the user selects "Yes" or "No" from the drop down menu. For example,1) the user selects "No" in the dropdown2) The user checks all rows in the checkbox column for which he wants the all the values"contract" field set to "No"3) The user then clicks on a button called "Submit" and all selected records get updated to "No" under the "contracts" column. The idea is to allow the user to change the boolean values from a field for multiple records. Hence, making individual cells editable is pointless. Anybody have an idea how to go about this?
View 3 Replies View Relatedi hav four cells that display
startdate | %completed| taskname | indicator
[startdate value]|[%completed]|[taskname value]|[indicatorfield value]
task name and indicator are grouped by years and months
i want all these in a single cell with a separator.
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
I am using the following query to export data from sql server to ms access in export data wizard:
SELECT * FROM myView where myID = 123
Order by varcharColumnName1,varcharColumnName2 ,intColumnName3
This query will fetch about 7, 00,000 records.
SQL server 2005 shows the correct order, but Data in access table shows Incorrect data.
Please give me the solutions.
I have a sql server 2008 backend with an Access 2007 frontend database. Each time I export a query I get the following error:
Code:
Microsoft Access was unable to append all the data to the table.
The contents of fields in 0 record(s) were deleted, and 1 record(s) were lost due to key violations.
*If data was deleted, the data you pasted or imported doesn't match the field data types or the FieldSize property in the destination table.
*If records were lost, either the records you pasted contain primary key values that already exist in the destination table, or they violate referential integrity rules for a relationship defined between tables. Do you want to proceed anyway?
I don't know what if anything is actually missing because of the amount of data is more thant 6000 records. It seems everything exported but I would have to comb through the data to be sure.
Hi,
Does anyone know if it is possible to point data that underwent the "merge join" transformation (in one data flow) to the following data flow? I don't want to recreate all that merging, sorting and calling the same sources again in the following data flow if the data that I am using exists in the previous data flow. The merged data is simply too big to export to an excel file, so does anyone have any ideas? Thanks!
hi all,
I am getting problem while importing data from excel file.
I am bale to do the same with flat files. But when i do with excel files
its throwing error : format error.
pls help me in this regard.
and How to export data into export files from query analyser..
Hello everybody,
I was wondering if there is a way to export and recode data at the same time with SQL.
For example I have gender information coded as 1 or 2 in my table and I need to upload the information to a different application that needs M or F. Is there a way to export to a new table and recode at the same time ?
I'm still pretty new to it.
Thank you.
-Seb
I would like to export data to a excel file using a stored procedure. I'm not sure how to go about this or if it is even possible. Can someone point me to a link to show me how to do this?
View 1 Replies View RelatedHi all,
I recently found out that there is no easy way to distribute a MSSQL 2005 database to MSSQL Server 2000. Most forums that I have read say to use the DTS facility to import the data into the SQL 2000, however this is not an option (for a variety of reason I won't go into).
The next best option in my mind is to script the entire database, including the data into one or a few script files then run them on the SQL 2000 server to recreate the database. Unfortunately, exporting the data, what I thought would be a fundamental feature, isn't part of SQL 2005.
So does anybody know of a good (free) scripting program that will allow me to export the entire database from a server? I've tried:
- Free program from the CodeProject.com (program dies when there's more than 5000 lines) http://www.codeproject.com/dotnet/ScriptDatabase.asp
-SQL Scripter www.sqlscripter.com (Doesn't script table which don't have primary keys, and produces a script for each object instead of just a single file)
I'd probably like a program which only creates a single script, as the database has over 200 tables and I don't want to have to go through this process everytime I need to distribute the DB (which will be often).
The only other option I can think of is a program which converts an SQL 2005 backup to a 2000 version.
Thanks for your help!
Guys,
I'm trying to export data from SQL. Can anyone help. I have commented my script below and have managed to create a table, insert using 'bulk insert' - now I want to export the data out. I'm getting an error message saying 'cannot use the output option when passing to a stored procedure'.
comment
-----------
drop table for re-runs !
-----------
drop table JET
go
comment
------------
create a table to match the .txt file importing
----------
create table JET
( [USER] char( 25),
[DESC] char (20),
SYSDATE datetime,
SYSVALUE MONEY ,
POSTDATE datetime,
POSTVALUE MONEY,
GLCODE CHAR (20)
)
comment
---------------
import using pipe delimited .txt file
--------------
bulk insert JET from 'D:Documents and SettingsmpeetSQL Test.txt'
with
(
fieldterminator = '|',
firstrow = 2
)
comment
-------
check results
-------
select *
from JET
NEW BATCH
bcp JET out 'D:Documents and SettingsmpeetSQL Testexport.txt'
I want extract the data to a .txt file would anyone know the syntax?
Cheers
Michael
I am using sql server 2005
I want export data from Excel to new SQL Server table,
select *
into mytable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:sample1.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
But i am getting following error message
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
I'm very new to SQL - please bear with me.
1. I need to be able to export data from a database to an excel sheet (I have written the query and tested it works, so I don't need to know this stage :^)). What is the best way of doing this? Could you send me a link of a howto doc?
2. Once the data is export to excel, it then needs to be manipulated so that a final sheet is created. During the manipulation I need to add the values of certain columns to give me an end result.
Should I use excel to manipulate the data or can sql add the values of certain columns and then export to an excel sheet?
What's the best way please.
Many thanks
I want to export data to CSV that is nested.
eg.Poll 1,val1,val2
Question1,Q1val1,Q1val2
AnswerQ1A1,Q1A1val1,Q1A1val2, TallyQ1A1
AnswerQ1A2,Q1A2val1,Q1A2val2, TallyQ1A2
Question2,Q2val1,Q2val2
AnswerQ2A1,Q2A1val1,Q2A1val2, TallyQ2A1
AnswerQ2A2,Q2A2val1,Q2A2val2, TallyQ2A2
AnswerQ2A3,Q2A3val1,Q2A3val2, TallyQ2A3
Best practices-wise, what should my data end up like?
Since it's nested surely it can't be simply a list of identically-formatted rows, one for each answer:
Poll1,val1,val2,Question1,Q1val1,Q1val2, AnswerQ1A1,Q1A1val1,Q1A1val2, TallyQ1A1,Poll1,val1,val2,Question1,Q1val1,Q1val2, AnswerQ1A2,Q1A2val1,Q1A2val2, TallyQ1A2,Poll1,val1,val2,Question2,Q2val1,Q2val2, AnswerQ2A1,Q2A1val1,Q2A1val2, TallyQ2A1,Poll1,val1,val2,Question2,Q2val1,Q2val2, AnswerQ2A2,Q2A2val1,Q2A2val2, TallyQ2A2
Do I end up with three separate tables?
Poll1,val1,val2
Question1,Q1val1,Q1val2, Question2,Q2val1,Q2val2
AnswerQ1A1,Q1A1val1,Q1A1val2, TallyQ1A1,AnswerQ1A2,Q1A2val1,Q1A2val2, TallyQ1A2,AnswerQ2A1,Q2A1val1,Q2A1val2, TallyQ2A1,AnswerQ2A2,Q2A2val1,Q2A2val2, TallyQ2A2
If that's so, do I simply concatenate them in the final file? Or do I have three separate files? And do I label them uniquely such as Poll1Main, Poll1Questions and Poll1Answers so that I can export several polls side-by-side without overwriting each other?
Hi
I am trying to export data from SQL which is ok and I can do that part. However from the result of the query I need to add the values of certain columns. Because I'm a newbie to SQL I'll explain and try and show images as best as I can as we go....
So far I have written a query
select Stockitem.code, Warehouseitem.ConfirmedQtyInStock, Warehouseitem.UnConfirmedQtyInStock, Warehouseitem.QuantityAllocatedStock, Warehouseitem.QuantityAllocatedBOM, Warehouseitem.QuantityAllocatedSOP, WarehouseItem.WarehouseIDfrom WarehouseItem INNER JOIN StockItem ONWarehouseItem.ItemID = Stockitem.itemidINNER JOIN BinItem ONBinitem.itemid = stockitem.itemidwhere WarehouseItem.WarehouseID = '3403' AND BinItem.BinName LIKE 'S%' AND BinItem.BinName <> 'S' OR BinItem.BinName LIKE 'T%' AND BinItem.BinName <> 'T'order by stockitem.code
This returns the following results....
Code ConfirmedQtyInStock UnConfirmedQtyInStock QuantityAllocatedStock QuantityAllocatedBOM QuantityAllocatedSOP WarehouseID
12345 96.00000 .00000 .00000 3.00000 13.00000 3403
Now I am trying to find out the amount of freestock available for everything with the WarehouseID 3403. Therefore in the example above to be able to workout how many '12345' (Code) we have in stock, the equation used to find out the free stock available is as follows....
Free Stock = (ConfirmedQtyInStock + UnConfirmedQtyInStock) – (QuantityAllocatedStock+ QuantityAllocatedBOM + QuantityAllocatedSOP)
Therefore if I manually use the equation above the amount of freestock available =
Free Stock = (96+0) - (0+3+13) = 80 (free stock).
Then once I have the equation to be able to output the 'freestock' quantity, I need to export the results into an excel spreadsheet with the following layout...
ColumnA
Code,FreeStock
12345,80
Hope I've explained this in a good manner, any help would be gratefully received.
Simba
Hi All,
I need to export data from one database to another and I want to automate this process. The problem is I only have to select specific tables of data to be exported. Any help will be greatly appreciated
Thank you!!
Is there any way in SQL 7/2000 to export a query result to a csv or xls via t-sql?
Thanks a lot for your help,
Yelena
may i know where to find more detail like how to importing and exporting data in ms sql server.?
Hope can get the more detail about it and also teach me step by step. (hope can include the photo).
I have a specific format that I need to export data to. The first three lines of the document MUST be in the form of:
ascii
,
klg, Eastern Daylight Time,1,1
PineGrove,0,2005/10/01,00:00,1,1.75,192
PineGrove,0,2005/10/01,00:05,1,1.75,192
Pinegrove,0,2005/10/01,00:10,1,1.75,192
If I set this up in DTS and do an export, it puts commas after ascii - which I cannot have.
I've also tried using two data sources and exporting twice (hoping to append), however, one just overwrites the other.
Anyone have any ideas?? :o
Thanks in advance,
Krista
Hello E'body,
I have to export a SQL Server Table (Structure+Data) to an access mdb file at run time. How can i do it? please help.
Lax.
Does anybody know what "free" tool I can use to export data from an MSDE database? I want to import it back to SQL Server
View 5 Replies View RelatedHi,
I am Sree Raj. New to SQL Server.
I am trying to export data to excel and it is giving an error.
Here is the query and error:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:SQL ServerEmp_Personal_Data.xls;', 'SELECT * FROM [Sheet1$]')
SELECT * FROM Emp_Info
error is:
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
Can anybody please help me what is this error about & how to fix this.
Thanks in advance
Sree Raj
Hi all,I've seen this noted in many posts, but nothing I've checked out givesme any clue on how to do this.Basically as my topic says, I have a DTS and I simply need to exportsome data from a table in MS SQL 2000 to an Excel spreadsheet. I alsoneed to automate this process so it can run nightly and each new day anew spreadsheet will be on a network share for us to pick-up.Can someone point me to the right direction? This needs to be donetotally through the DTS script, so no ImportExport wizard or anythingmanual.Thanks --Sam
View 1 Replies View RelatedHi all,
I am rather fresh to SQL2000, so...
When I export data from database A tot database B and I look at the copied tables in database B, all the primary keys are gone and the field is now a "normal" field ?
How can I keep the primary key in the copied tables ?
Help is appreciated, Ger.