Find/Next + Excel Merge Column Issues
May 9, 2008
Two issues,
1. Even if there is no text entered in the search box of the report viewer header, the next button is clickable and returns the message "the entire report is searched'. Is this a defect in the product?
2. When report is exported to excel, the excel object tries to keep the report formatting to the closest and hence merging the columns in the excel. What is the work around to fix this issue. The end users after exporting to excel would normally do a sort by ascending/descending on required columns and this behaviour is causing an issue.
View 1 Replies
ADVERTISEMENT
Jun 14, 2006
l've the following situation,
l've some excel files controlled by Vendor which changing frequently. The only thing does not change is the header name of each column.
So my question is, is there any way to create a new table based on the excel file selected including the column name in SSIS? So that l can use the data reader as source to select those columns l am interested on and start the integration.
Thanks.
Regards,
Yong Boon, Lim
p/s : The excel header is at the row 7.
View 3 Replies
View Related
Jul 6, 2015
While importing data from Excel source , some column is getting null value even though excel column has value.To Resolve the issue we tried with
HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoftOffice14.0Access Connectivity EngineEnginesExcel
1.Change the Value of the Row TypeGuessRows from 8 (Default value) to 0 and ImportMixedType = text
• xls
HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcel
1.Change the Value of the Row TypeGuessRows from 8 (Default value) to 0 and ImportMixedType = text
the connection string of the excel
UPPER(REVERSE(SUBSTRING( REVERSE(@[User::VarInputExcelFile]), 1, 5) ) ) == ".XLSX" ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::VarInputExcelFile] + ";Extended Properties="Excel 12.0;HDR=Yes;IMEX=1";":"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + @[User::VarInputExcelFile] + ";Extended Properties="EXCEL 8.0;HDR=Yes;IMEX=1";"
by doing the above setting also , the column is coming as null from excel source even though there is data in excel.
View 2 Replies
View Related
Jul 20, 2005
Hi,Suppose I have a table containing monthly sales figures from my shopbranches:Branch Month Sales-----------------------London Jan 5000London Feb 4500London Mar 5200Cardiff Jan 2900Cardiff Feb 4100Cardiff Mar 3500The question I am trying to ask is this: in which month did each branchachieve its highest sales? So I want a result set something like this:Branch Month----------------London MarCardiff FebI can do a "SELECT Branch, MAX(Sales) FROM MonthlySales GROUP BY Branch" totell me what the highest monthly sales figure was, but I just can't figureout how to write a query to tell me which month corresponded to MAX(Sales).Ideas anyone?Cheers,....Andy
View 5 Replies
View Related
May 1, 2015
I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1. To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique.
Table1
GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2
The result of the query would only have PID1 because it has two unique GUID's. PID2 would not be listed has it has the same GUID3 in each row.
Result:
PID1
View 2 Replies
View Related
May 19, 2015
I am working on a project that will require me to get a flat data file (excel spreadsheet) with hundreds of thousands of records. Each record is an Owner, and specifically what they own. There will be a field for OwnerName that I want to figure out a way to pull the data into a database like;
Table(Owners) - make sure owner is listed only once
Table(Properties) - joined to owners showing all properties that person owns
Now the tricky part, the owner names might not be exactly the same. Some records might have;
Smith, John
John Smith
Smith, John T
etc.
To make matters worse, this will be a continuous process. I will receive updated excel spreadsheets from time to time and will need to import the new records, many times overwriting the old data. For the good news, there should be an OwnerID that will be unique within the excel data. So as I am merging similar records into the Owners table, I should have a list of OwnerID's that can forever be used to link to the owner.
View 3 Replies
View Related
Oct 12, 2007
Hi All,
I have two SQL queries that we would like to automate. Ideally we want them to both be scheduled to run and dump their results to a single Excel spreadsheet with two workbooks, one for each query
Is it possible to do this? If not, sending each query to a seperate XLS or CSV file would be OK
Here are the queries:
SELECT p21_view_unvouched_po_currency_report.unvouched_document_type, p21_view_unvouched_po_currency_report.date_created, p21_view_unvouched_po_currency_report.unvouched_document_no, p21_view_unvouched_po_currency_report.line_number, p21_view_unvouched_po_currency_report.po_no, p21_view_unvouched_po_currency_report.po_line_number, po_line.created_by, p21_view_unvouched_po_currency_report.item_id, p21_view_unvouched_po_currency_report.item_desc, p21_view_unvouched_po_currency_report.qty_received, p21_view_unvouched_po_currency_report.qty_vouched, p21_view_unvouched_po_currency_report.order_date, p21_view_unvouched_po_currency_report.location_id, p21_view_unvouched_po_currency_report.supplier_id, p21_view_unvouched_po_currency_report.supplier_name, p21_view_unvouched_po_currency_report.extended_cost_home
FROM P21.dbo.p21_view_unvouched_po_currency_report p21_view_unvouched_po_currency_report, P21.dbo.po_line po_line
WHERE po_line.po_no = p21_view_unvouched_po_currency_report.po_no AND po_line.line_no = p21_view_unvouched_po_currency_report.po_line_number
and
SELECT p21_view_unvouched_po_currency_report.unvouched_document_type, p21_view_unvouched_po_currency_report.date_created, p21_view_unvouched_po_currency_report.unvouched_document_no, p21_view_unvouched_po_currency_report.line_number, p21_view_unvouched_po_currency_report.po_no, contacts.last_name, p21_view_inventory_return_hdr.buyer_id, p21_view_unvouched_po_currency_report.po_line_number, p21_view_unvouched_po_currency_report.item_id, p21_view_unvouched_po_currency_report.item_desc, p21_view_unvouched_po_currency_report.qty_received, p21_view_unvouched_po_currency_report.qty_vouched, p21_view_unvouched_po_currency_report.order_date, p21_view_unvouched_po_currency_report.location_id, p21_view_unvouched_po_currency_report.supplier_id, p21_view_unvouched_po_currency_report.supplier_name, p21_view_unvouched_po_currency_report.extended_cost_home
FROM P21.dbo.contacts contacts, P21.dbo.p21_view_inventory_return_hdr p21_view_inventory_return_hdr, P21.dbo.p21_view_unvouched_po_currency_report p21_view_unvouched_po_currency_report
WHERE p21_view_inventory_return_hdr.return_number = p21_view_unvouched_po_currency_report.unvouched_document_no AND contacts.id = p21_view_inventory_return_hdr.buyer_id
View 4 Replies
View Related
Apr 29, 2015
I have one table with two columns, GUID and PID and another table with three columns, GUID, LastName, and FirstName. For each unique PID, I need to find the PID's that have more than one GUID and then match that with their respective FirstName and LastName from the other table.
Table1
GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2
Table2
GUID LastName FirstName
GUID1 Mulder Fox
GUID2 Scully Dana
GUID3 Skinner Walter
So I'm looking for a result like:
PID1 GUID 1 Mulder Fox
----- GUID 2 Scully Dana
View 5 Replies
View Related
Jul 25, 2007
I'm trying to quickly create a way of importing data from an excel sheet on my C drive to a table on a sql 2005 db hosted by a provider.
I set this up according to the following:
http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx
The article is in C# but I write in VB. I think I got it except I get this error:
Could not find installable ISAM.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OleDb.OleDbException: Could not find installable ISAM.Source Error:
Line 23: Dim command As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand("Select ID,Data FROM [Data$]", connection)
Line 24:
Line 25: connection.Open()
Line 26:
Line 27: ' Create DbDataReader to Data Worksheet
Microsoft is not much help. A couple of folks say to contact my host. Before I do I just want to be sure there code I have is correct.
Could someone take a quick look at this.
Thanks,
Here is the code behind the button.
Protected Sub BTNImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BTNImport.Click
' Connection String to Excel Workbook
Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Book1.xls;ExtendedProperties=""Excel 8.0;HDR=YES;"""
' Connection to Excel WorkbookUsing connection As Data.OleDb.OleDbConnection = New Data.OleDb.OleDbConnection(excelConnectionString)Dim command As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand("Select ID,Data FROM [Data$]", connection)
connection.Open()
' Create DbDataReader to Data WorksheetUsing dr As Data.Common.DbDataReader = command.ExecuteReader()
' SQL Server Connection StringDim connectionString As String = ConfigurationManager.ConnectionStrings("HbAdminMaintenance").ConnectionString
'Origina from David Hyden's site - Dim sqlConnectionString As String = "Data Source=.;Initial Catalog=Test;Integrated Security=True"
' Bulk Copy to SQL ServerUsing bulkCopy As Data.SqlClient.SqlBulkCopy = New Data.SqlClient.SqlBulkCopy(connectionString)
bulkCopy.DestinationTableName = "ExcelData"
bulkCopy.WriteToServer(dr)
End Using
End Using
End UsingEnd Sub
View 6 Replies
View Related
May 29, 2006
Hi I have tried to add a new column to a publication table using sp_repladdcolumn and also directly add the column under Publication's properties screen.
I am able to add the column using both method. But changes to the newly added column either at the publisher or subscription databse will not get replicated.
Have I miss out something? or do I need to reinitialise the subscription to make changes in the newly added column to be replicated?
View 3 Replies
View Related
Apr 18, 2006
i have 2 fields name_last , name_first
when i use the query ,
Select name_last , name_first from members order by 1,2
i get the values..
Now I want to merge the fileds and show in a single column like Name_last , Name_first
ie ::
Select name_last , name_first from members order by 1,2 =
Col1 Col2
Aagersen Kevin
Select name_last " , " name_first from members order by 1,2 =
ColumnMerge1
Aagersen , Kevin
View 7 Replies
View Related
Dec 20, 2007
Hi
I have an old db I'm converting with SSIS.
In the old db I have an Log-table wich is in this format:
ID,CaseID,Note
each CaseId can have several rows.
Now I want to merge the Note Row into one row based on the CaseID.
So that I get One Row per CaseID with one bignote.
I have been looking into Pivot, but just dont seem to get it working.
Any Ideas?
View 2 Replies
View Related
Sep 5, 2015
I was querying to find the first non null address value using the COALESCE function.And I got the correct result.But then I jumped into another question and i.e what if I need to find the column name without knowing the column nameand just by using the column value.What I mean is this...My query was.....
SELECT COALESCE(AddressLine1,AddressLine2) AS [Addresss] FROM Person.Address
This is what I got.
Address#500-75 O'Connor Street#9900 2700 Production Way00, rue Saint-Lazare02, place de Fontenoy035, boulevard du Montparnasse081, boulevard du Montparnasse081, boulevard du Montparnasse084, boulevard du Montparnasse1 Corporate Center Drive1 Mt. Dell Drive
But then what if I just know the address of that person i.e #500-75 O'Connor Street..How am I suppose to retrieve that without knowing the column name.
View 2 Replies
View Related
Sep 27, 2007
Hi,
I have a table used in Merge Replication. If i add a new column in that table it gives me an error for tables in which either this table is a key or this table has key from other tables "Error validating the default for column 'rowguid'." Also for views i'm getting an error
"View 'dbo.vw_TestCaseBasic': indexes and schema binding will be removed."
If i proceed further i get an error and m not able to add the column.
"It is invalid to drop the default constraint on the rowguid column that is used by merge replication.
The schema change failed during execution of an internal replication procedure. For corrective action, see the other error messages that accompany this error message.
The transaction ended in the trigger. The batch has been aborted."
Is there any solution for the same?
Regards,
Ruchir Jain
View 4 Replies
View Related
May 16, 2007
Is the maximum number columns per table still 246 for a Merge replication in SQL Server 2005?
View 4 Replies
View Related
May 16, 2007
I m extracting data from Excel-sheet and inserting into SQL Server 2005 Database using SSIS(SQL Server Integration services).
Having 2 columns in excel-sheet and want to insert those into 1 column of SQL Table.
Can anyone help me about, how can I insert data from 2 columns into 1 column in SSIS. Is Export/ Import column or Copy Column or Merge will work..
Plz reply soon..
Thanks...
View 1 Replies
View Related
Feb 25, 2008
Hello,
I have data coming in from two sources, one being SQL and the other being Oracle. The end result needs to be a CSV file with the columns in a specific order. I have a Data Flow task setup that takes both sources and does a Merge Join on them. I can add a Sort Transformation and manually set the sorting of all 156 columns that end up going to a CSV file destination. However, I have a table setup that holds the names of the 156 columns and the order that the CSV file expects them to be in. I would much rather do this step dynamically as the column names and order may change in the future. Anyone who has used the Sort Transformation for a large number of columns knows how tedious it can be and how adding a column in the middle will cause you to change the sort # for each of the columns that come after it.
So I added a Script Component between the Merge Join and the Flat File Destination hoping that I could alter the order of the columns there. However I added the following code and found that the SortKeyPosition is ReadOnly.
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim column As IDTSInputColumn90
For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection
column.SortKeyPosition = 1
Next
End Sub
I was hoping to add some code to get the sort index from my table for each of the columns and set it to the SortKeyPosition. Has anyone out there done this before or seen an example that might point me in the right direction? I've searched for 2 days without coming up with much.
Thanks!
View 5 Replies
View Related
Oct 9, 2015
How can I merge two cells in the same column?
example
ColmAAAA
cell001
cell002
I want to merge cell001 and cell002
View 4 Replies
View Related
Aug 22, 2005
Please provide me a column-level merge replication in SQL Server 2005. In the SQL Sever books online, I could able to get very few informaiton about this topic. Please provide me a best practices for using this kind of conflict resolution in Merge-replication.
View 5 Replies
View Related
Jul 28, 2001
Hi all,
After I setup the merge replication in SQL SERVER 7.0, there is a rowguid column (datatype uniqueidentifier) inserted into each table in the both the source and destination database.
I need to get rid of the rowguid column in all tables. I deleted the replication, but the column still existed.
Is there an easy way to get rid of all the rowguid column in the database? Thanks for your reponse in advanced.
View 2 Replies
View Related
Aug 12, 2015
I need to merge column values (#Status.Status) based on OrderID onto #Orders.NewStausCombined field separated by commas .
CREATE TABLE #Status
(
ID INT IDENTITY (1,1) PRIMARY KEY,
OrderID INT,
Status VARCHAR(20)
)
INSERT INTO #Status ( OrderID, Status )
[code].....
View 3 Replies
View Related
Nov 6, 2006
Hi
I'm working with merge replication between Sql Server 2005 and Sql Server 2005 Mobile. I'm using dynamic filtering by function HOST_NAME (... where Table.FilteredColumn = HOST_NAME())
When I'm trying to chagne values from filtered column on the client side (Table.FileteredColumn) I get error message that the column is read-only.
How can I change data in filtered column? Is this possible?
View 3 Replies
View Related
Aug 30, 2000
Hello!
I have 80 tables in the database and I need to find in which table particular column exist. How can I do it shortly, without naming every table in my query?
Thank you.
View 1 Replies
View Related
Jun 5, 2008
I have more than 70 tables
How can i find the column name ( this column is the one of the column name from above tables)
pls help me
View 1 Replies
View Related
Oct 11, 2007
Greetings!I have a project that has been in production for a couple years now. It's a v1.1 ASP.NET web farm hitting a SQL database. The last couple weeks, the website has been erroring out on various pages for about 1-2 hours at a time twice a week. While the error messages are occurring, I cannot reproduce the errors myself. Here are some of the error messages I recieve:Cannot find column [ColumnName][ColumnName] is neither a DataColumn nor a DataRelation for table RESULT.[ColumnName] (This is a wierd one. The error message returned is just a column name)No record foundColumn '[ColumnName]' does not belong to table RESULT.I come to a dead end when I follow the Stack Trace to find a programmatic error. I'm able to hit the same page with all the same form fields at the error occured on and I cannot reproduce. The only consistentcy in these errors is that they come all at once and they are database related. Anyone have an issue like this before?
View 8 Replies
View Related
Jul 23, 2003
Hi,
I need to find the text data type column in all the table.
Please let me know the script.
Thanks,
Ravi
View 2 Replies
View Related
Mar 29, 2004
I have column A,B and C. I need a query to find the duplicates among these column.
Thanks,
Ravi
View 2 Replies
View Related
Oct 16, 2006
How can I find all the tables with a specific column name?
View 3 Replies
View Related
Apr 16, 2011
How could i find all tables name which have column value 'Ferrari'
.
.
.
How could we find table name from it's column value?????
View 9 Replies
View Related
Jan 28, 2006
Hi,How to find first not null value in column whitout chacking whole table(if there is a not null value then show me it and stop searching, thetable is quite big)?thx,Martin*** Sent via Developersdex http://www.developersdex.com ***
View 26 Replies
View Related
May 29, 2008
Hi all,
Is there a way to see how many tables have a column like "name" in database?
Thanks
View 5 Replies
View Related
Jan 30, 2007
hi
is it possible to find a column's type inside stored procedure?? I am going to get the name of the table and then work with the columns and I just need the type of the column if I have had the name of it
regards
View 1 Replies
View Related
Jan 21, 2007
hi ,How can I find sql Datatype (like NVARCHAR , DESIMAL & .. ) and size of columns and also is it Identity or not in a Table of SQl server?
View 4 Replies
View Related
