How To Select Data From Excel Table By Original Column Order?
Hello, everyone:
I have an Excel table that has columns like,
Vendoe_Name
Level
Address
Email
Phone
I use the statemen
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C: est.xls;Extended Properties=Excel 8.0')...[Sheet1$]
and got output by column order,
Address
Email
Level
Phone
Vendoe_Name
How to make output by original column order like,
Vendoe_Name
Level
Address
Email
Phone
Any help will appreciated. Thanks
ZYT
View Complete Forum Thread with Replies
Related Forum Messages:
How Can I Use SELECT DISTINCT And Maintain The Original Order
Say I have a result set with two fields numbers and letters. 1 A3 A1 B2 B The result set is ordered by the letters column. How can I select the distinct numbers from the result set but maintain the current order? When I tryselect distinct Number from MyResultSet it will reorder the new result set by the Number field and return 123 However, I'd like maintain the Letter order and return 132
View Replies !
How To Keep Original Order When Querying A Table?
Hello, everyone: I have a table like: ColName b b b d d d a a c c c I use DISTINCT to filter duplicated row. I want to get the return by original order like: b d a c However, SQL Server re-order it if using DISTINCT and return like: a b c d Can any one have the idea to handle that? Thanks ZYT
View Replies !
How Do Create Table From Excel (based On Excel Column Name) And Import Data From It?
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 Replies !
Default Sort Order - Open Table - Select Without Order By
Hi! I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order. The table returns the data in the same order in SQL Manager "Open Table" So I started to wonder what deterimins the sort order when there is no order by clause ? I researched this for a bit but found no straight answers. My table has no PK, but an identiy column. Peace. /P
View Replies !
Select Distinct Column Data With Other Values From The Table
I have a table 'wRelated' with the following columns [related_id] [int] [channel_id] [int] [mui] [varchar] [price_group_id] [type_id] [int] [related_mui] [varchar] (100) [date_started] [smalldatetime] [date_ended] [smalldatetime] [date_entered] [datetime] [deleted] [tinyint], [rank] [int] data in column [mui] is repeated as the table has more than one entries for the same [mui], The requirement is to select the distinct[mui] but value in all the other columns for the same mui should be select in the next row with null for the same [mui] The recordset expected should be something like this. [mui],[related_mui],[price_group_id],[date_entered],[date_ended] m123,rm345,'pr','12-10-2003',12-12-2004' null,rm789,'ar','12-1-2003',26-2-2004' null,rm999,'xy','14-12-2002',12-2-2004' m777,rm889,'pr','12-12-2004',12-12-2004' null,rm785,'yy','1-10-2002',12-12-2004' m888,rm345,'pr','2-8-2003',12-12-2004' null,rm345,'tt','30-7-2002',12-12-2004' I have tried Unions and temporary table inserts.
View Replies !
SELECT DISTINCT And ORDER BY With Aliased Column
This query demonstrates a problem I have run across: USE AdventureWorks GO -- This query works fine. SELECT DISTINCT FirstName AS Name1 FROM Person.Contact ORDER BY FirstName GO -- This query also works fine. SELECT ISNULL(FirstName, '') AS Name1 FROM Person.Contact ORDER BY FirstName GO -- This query returns error 145 SELECT DISTINCT ISNULL(FirstName, '') AS Name1 FROM Person.Contact ORDER BY FirstName GO The last query returns the error "ORDER BY items must appear in the select list if SELECT DISTINCT is specified". It will work if I change ORDER BY to use "Name1" instead of "FirstName", but in the situation I have at hand, the query is generated by third-party software and I don't have the ability to change it. Can anyone explain why what's going on here? Oddly, this same query will work if I run it against SQL Server 2000.
View Replies !
Using The ORDER BY Clause When The Ordered Column Is Not Needed In The SELECT Statement
Greetings, I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered. How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column. ALTER PROCEDURE [dbo].[MyProcedure] AS BEGIN SELECT DISTINCT A.Name, A.index FROM ... ... ORDER BY A.[Index], A.Name ASC END ALTER PROCEDURE [dbo].[MyProcedure] AS BEGIN SELECT DISTINCT A.Name FROM ... ... ORDER BY A.[Index] END Thanks
View Replies !
Changing Column Order In A Table
This subject has been posted several times, but I haven't seen a goodanswer.Problem:I want to change the order of the columns in a table using T-SQL only.Explanation:After running your code, I want to see the following table...CREATE TABLE [dbo].[TableName] ([First_Column] [int] NULL ,[Second_Column] [varchar] (20) NULL) ON [PRIMARY]look like this...CREATE TABLE [dbo].[TableName] ([Second_Column] [varchar] (20) NULL ,[First_Column] [int] NULL) ON [PRIMARY]Limitations:Don't post if your post would fall in the following categories:1. If you don't think it can be done2. If you think Enterprise Manager is the only way to do this3. If you think I should just change the order of my Selectstatements4. If you want to state that order column doesn't matter in arelational database5. If you want to ask me why I want to do thisWish:Hopefully the answer WON'T involve creating a brand new table, movingthe data from old to new, dropping the old table, then renaming thenew table to the old name. Yes, I can do that. The table I'm workingwith is extremely huge -- I don't want to do the data juggling.Thanks in advance!
View Replies !
Alter Table And Column Order
Is it possible to add a column to a table using the "alter table"statement and specify where in the sequence of columns the new columnsits. If not is there any way to alter the order of columns using TSQLrather than Enterprise Manager / Design Table.TIALaurence Breeze
View Replies !
Does Column Order Matter When Creating A Table?
Does column order matter when creating a table? For example, Should NOT NULL columns always come before NULL columns? Should most frequently used columns always be near the top? What about text, ntext and image data types? Should they always appear near the end of the column order?
View Replies !
Mass Table Structure Change For Column Order
Say you have an existing populated SQL 2005 database, with 700+ tables, and you want to just change the order of the columns inside every table. Short of manually building conversion scripts, anyone know an automated way to do this? I was thinking thru ways to do them all in one shot, and have tools like Erwin and DbGhost that could be used also. Basically moving some standard audit columns from the end of the tables to just after the PK columns. Thanks, Bruce
View Replies !
Invalid Results For Order By On Select Query Against Table Variable
I am attempting to sort the results of a query executed against a table variable in descending order. The data is being inserted into the table variable as expected, however when I attempt to order the results in descending order, the results are incorrect. I have included the code as well as the result set. DECLARE @tblCustomRange AS TABLE ( RecordID INTEGER IDENTITY(1,1), RangeMonth INTEGER, RangeDay INTEGER ) DECLARE @Month INTEGER DECLARE @Day INTEGER -- Initialize month and day variables. SET @Month = 8 SET @Day = 11 -- Insert records into the table variable. INSERT INTO @tblCustomRange (RangeMonth, RangeDay) VALUES (1,2) INSERT INTO @tblCustomRange (RangeMonth, RangeDay) VALUES (1,27) INSERT INTO @tblCustomRange (RangeMonth, RangeDay) VALUES (6,10) INSERT INTO @tblCustomRange (RangeMonth, RangeDay) VALUES (9,22) INSERT INTO @tblCustomRange (RangeMonth, RangeDay) VALUES (12,16) -- Select everything from the table variable ordering the results by month, day in -- descending order SELECT * FROM @tblCustomRange WHERE (RangeMonth < @Month) OR (RangeMonth = @Month AND RangeDay <= @Day) ORDER BY RangeMonth, RangeDay DESC I am getting the following resultset: RecordID RangeMonth RangeDay ----------- ----------- ----------- 2 1 27 1 1 2 3 6 10 I am expecting the following resultset: RecordID RangeMonth RangeDay ----------- ----------- ----------- 3 6 10 2 1 27 1 1 2
View Replies !
Adding An Auto-increment Column To Existing Table With A Particular Order
Hello all,I'm using SS2K on W2k.I'v got a table say, humm, "Orders" with two fields in the PK:OrderDate and CustomerID. I would like to add an "ID" column whichwould be auto-increment (and would be the new PK). But, I would reallylike to have orders with the oldest OrderDate having the smallest IDnumber and, for a same OrderDate, I'd to have the smallest CustomerIDfirst. So my question is:How could I add an auto-increment column to a table and make it createits values in a particular order (sort by OrderDate then CustomerIDhere)?In the real situation, the table I want to modify has around 500krecords and the PK has 5 fields and I want to sort on three of them.Thanks for you helpYannick
View Replies !
Column Order/presentation In Virtual Table (result Set From Viewor UDF)
I was just messing around with some ad hoc views and table returningUDFs today so I could look at and print out data from a small tableand noticed something strange.If I stick my select statement into a View the columns are returned inthe order I specify in the SELECT, but if the same statement is in a UDF(so I can specify a parameter), the columns are not returned in theorder specified in statement.I know that relations don't have a specified column order, but it was myunderstanding that a SELECT statement could be used to define how youwant your data presented. Views seem to respect the order specified inthe SELECT, but functions don't.What am I missing? Is there some way to force the order of the columnsreturned from a SELECT?View:CREATE VIEW dbo.View1ASSELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt,ThenStmt, ElseStmt, NextStmtFROM dbo.tblStmtWHERE (Ident LIKE '4.2.%')Column order from this view:Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt, ThenStmt,ElseStmt, NextStmtFunction:ALTER FUNCTION dbo.Function1(@SearchPrm varchar(255))RETURNS TABLEASRETURN ( SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt,ChildStmt, ThenStmt, ElseStmt, NextStmtFROM dbo.tblStmtWHERE (Ident LIKE @SearchPrm) )Column order from this function:Type, Text, ElseStmt, NextStmt, IfStmt, ChildStmt, ThenStmt, Ident,ParentStmt, ForStmtTable:(I know that this table isn't entirely normalized, but it serves mypurposes to have a matrix instead of a fully normalized relation):CREATE TABLE dbo.tblStmt (StmtID INT IDENTITY(1,1) CONSTRAINT PK_Stmt PRIMARY KEY,Ident VARCHAR(255),Text TEXT,ErrorText TEXT,Type INT,ParentStmt VARCHAR(255),ChildStmt VARCHAR(255),IfStmt VARCHAR(255),ForStmt VARCHAR(255),ThenStmt VARCHAR(255),ElseStmt VARCHAR(255),NextStmt VARCHAR(255),FullName VARCHAR(255),LocalName VARCHAR(255),Method INT)INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmtVALUES('4.2.1', 'LineNumberOfResp := EMPTY' 64, '4.2', '4.2.2')INSERT INTO tblStmt Ident, Text, Type, ParentStmt, ChildStmt, ForStmt,NextStmtVALUES('4.2.2', 'FOR K:= 1 TO 2', 128, '4.2', '4.2.3','4.2.7')INSERT INTO tblStmt Ident, Text, Type ParentStmt, ChildStmt, ForStmt,NextStmtVALUES('4.2.3', 'Person[K].KEEP', 16, '4.2', '4.2.3.1', '4.2.2', '4.2.4')INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmtVALUES('4.2.3.1' 'AuxInterviewerName := DOSENV', 64, '4.2.3', '4.2.3.2')
View Replies !
Sql Query Summerize The Detail Table In Order To Convert Its Trans_type Into Column
I have detail table like date item_id grade in out ------------- --------------- ------- -------- ---------- 01-01-08 001 A 10 0 02-01-08 001 O 8 0 01-02-08 002 O 1 0 03-01-08 001 T 0 10 02-01-08 003 O 20 0 02-01-08 003 T 0 10 02-01-08 003 B 0 8 Result View ======= Item_id A B O T Total 001 10 0 8 -10 8 002 0 0 1 0 1 003 0 8 20 -10 18 where grade could be any Alphabet, the column of result query could varies each grade contain sum of (in-out) of all item_id of detail table. Smartsys
View Replies !
Select Data From Excel
Hello, Is there a good way in SQL to select records that have been sent in an excel spreadsheet and import them into a temporary table? I have a list of order codes that have different seg codes for example: Order CodeZSeg 7625 ZBL 717009 ZBL 3277 ZCI 8144 ZCI Each order code is unique and is classifed into a zseq. Depending on this zseg I need to update an existing table with the following field names and values for each order code: For example for an order code classified as a ZBL I need to insert into an existing table that uses order code as the PK Value Code: Patrac Value Description: Patient Race Value Type: ST Units: None Template Name: Lab_Master_ Field Name: AOE_BL_Race There are multiple Value codes for each Zseg that will be inserted for each order code. Can someone tell me the best way to do something like this?
View Replies !
Excel Column Data
im trying to access data in an excel spreadsheet using excel source component...the excel column is type of general and most of the data is numeric, some string..now, when ii hit the preview button, the string data would show up as NULL and the numeric data are correct...i've changed the excel column to TEXT data type and same result, how can i get the string data from this column?
View Replies !
Table Order When Inserting Data
I have to import data into a empty database, that has many tables.some tables have to be inserted first than others due to the foreignkeys.How do I find out the order of the tables that I have to insert datainto?Thanks in advance!Sam
View Replies !
Excel Data Mining Add In: &&"Order By&&" Not Working
Hello, I am trying insert the "Order by" clause into DMX but nothing is working. INSERT INTO MINING STRUCTURE [ARS] ( [OrderID], [Product_Table](SKIP, [Product]) ) SHAPE { OPENQUERY ([dwMDA on PSD_TEST_TEST], 'Select Top 30000 "OrderID" From "dwMDA"."dbo"."vDetail" Order By "OrderID"') } APPEND ( { OPENQUERY ([dwMDA on PSD_TEST_TEST], 'Select Top 30000 "OrderID","Product" From "dwMDA"."dbo"."vDetail" Order By "OrderID"')} RELATE [OrderID] TO [OrderID] ) AS T This is the DMX for market basket analysis. I am also unable to use "order by" when i pull data into excel. here's an example of something that doesn't work: Select Top 20000 "OrderID","ProductGroupDescription" From "dwMDA"."dbo"."vDetail" Order By "OrderID" Can someone show me the exact working SQL commands they are using for the "Order By" clause when they import data from sql into an excel spreadsheet? Even better would be if someone 1. Open excel, and go to the data mining tab 2. Click on the Cluster Button 3. Select Analysis Service Data Source 4. Input a command the includes "Order By" 5. Run the model 6. Show me the tracer. Thanks Davy
View Replies !
SqlBulkCopy Into SS2000 Table From Excel Without Column Headers
I am trying to copy data using SqlBulkCopy into a SQL Server 2000 table from and Excel worksheet that doesn't have column headers. I am running into an error message that tells me "The given ColumnMapping does not match up with any column in the source or destination." If there are no column names in the Excel sheet, how do I map them to the table?
View Replies !
Excel Column Data Type Issue
Ok, hopefully a simple issue with a simple fix. I'm importing data from an Excel spreadsheet with values such as: 10, 20, 20 Tall, 40, 40 Tall. The column imports but is treated as numeric with values 10, 20, 20, 40, 40. I set the cells to be treated as text in Excel. Any suggestions?
View Replies !
Default Sort Order When Order By Column Value Are All The Same
Hi, We got a problem. supposing we have a table like this: CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times) select top 5 * from a order by aName Result is: 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde select top 10 * from a order by aName Result is: 11Bank of abcde 10Bank of abcde 9Bank of abcde 8Bank of abcde 7Bank of abcde 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek: Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot. So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
View Replies !
Default Sort Order When The Order By Column Value Are All The Same
Hi, We got a problem. supposing we have a table like this: CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times) select top 5 * from a order by aName Result is: 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde select top 10 * from a order by aName Result is: 11 Bank of abcde 10 Bank of abcde 9 Bank of abcde 8 Bank of abcde 7 Bank of abcde 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot. So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
View Replies !
Exporting A Table With Changing Column Names To An Excel File
I'm trying to write an SSIS package that exports a table that has changing column names to an excel file. The column names change due to the fact that the table is created by a pivot daily. the only thing I'm missing is the ability to dynamically map the tables' columns to the excel destination. Is this possible? I read in another thread that "It is not possible to create packages or new objects within packages using SSIS." I also read in the books online that "The input and the input columns of the Excel destination have no custom properties." To me this means that I cannot programmatically create or remove columns in the excel destination. Please tell me I'm wrong. So, to summarize my research so far. In writing an SSIS package, I cannot programmatically create a new excel destination object and I can't manipulate an existing one. I hope I'm wrong. Can anyone help me? (and please correct any wrong assumptions I may have stated)
View Replies !
Importing Excel Data With More Than 255 Char Text Column
I am trying to import data from an excel file. One of the columns contains textual information with linefeeds. Its length is greater than 255 characters. I am having trouble with truncation of the data. Is there a limitation I am running into? Is there a work around? TIA eventnext
View Replies !
Default Sort Order Of The Data When Inserting From One Table To Another
I have a data load process that reads data from flat file into a Stage table in sql server. The order of the records in the stage table is exactly same as the order in the flat file. The identity column on the Stage table (which is also the clustered index) represents the exact line/row number of the data in the filat file. I perform some transformations on the data in the stage table and then insert it into a cumulative table which has a clustered index on an identity column again. When I do this, does the order of the data in the cumulative table be in the same order as the data in the stage table? Anyone, please let me know if I can rely on SQL server to maintain the same order or I will be forcing a sort order on the Identity column (clustered index) of the stage table when I insert the data into a cumulative table. Thanks in advance!!
View Replies !
How To Select All Columns Except One Column From A Table ?
Hi I can't figure out how to do this and hope you guys can give me a hint.... I want to select all columns from a table except one column: For example, A table has 20 columns : {1,2,3.....20} To select all of columns in a table, i could use select * from table; However, I want to select only column {1....19} I do not want to type the column names in one by one in the SQL query, is there a way to achieve this in a short form of Select? Thanks,
View Replies !
(Urgent)How Can I Specify The Excel File's Column To Import Data As Varchar Instead Of Float
Hi, I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000... but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails... what can i do to rectify the problem... regards Karen
View Replies !
How Do You Deal With Page Refresh After A Filter Has Been Applied To Original Data?
I have added a page refresh in combination with 'Command Notification' to ensure that my data is fresh,( it is updated on the sql server database approx. every 5 mins). However, I also allow filtering on the table contents and every time I refresh the filtering is lost. Is it possible to maintain the filtering through the refresh? <meta http-equiv="refresh" content="30"/>..<asp:SqlDataSource ID="SqlDataSource1" runat="server" ..SelectCommand="SELECT ... EnableCaching="true" CacheDuration="Infinite" CacheExpirationPolicy="Absolute" SqlCacheDependency="CommandNotification" >
View Replies !
Specify Order For Select Results, Order By: Help!
Lets say I have a table named [Leadership] and I want to select the field 'leadershipName' from the [Leadership] Table. My query would look something like this: Select leadershipName From Leadership Now, I would like to order the results of this query... but I don't want to simply order them by ASC or DESC. Instead, I need to order them as follows: Executive Board Members, Delegates, Grievance Chairs, and Negotiators My question: Can this be done through MS SQL or do I need to add a field to my [Leadership] table named 'leadershipImportance' or something as an integer to denote the level of importance of the position so that I can order on that value ASC or DESC? Thanks, Zoop
View Replies !
Datamining Model Automatically Add Space To Field Name. Can't Use Prediction Join With Original Table
Dear All, I have a database table which has all the inputs, key and the result. In visual studio, I created a decision tree model which has exactly the same fields as in the table. However the visual studio automatically add space preceding the capital letters. As the field name in the Datamining model and those in the database table are slightly different. I cannot use NATURAL prediction join. Is there anyway to told the visual studio not to add the spaces in the variable names? Thanks and regards, Tony Chun Tung Siu
View Replies !
How Do I Get The Actual Name Of A Column Or Table In A Sql Select Statement?
Hello fellow .net developers, In a website I'm working on I need to be able to put all of the user tables in a database in a dropdownlist. Another dropdownlist then will autopopulate itself with the names of all the columns from the table selected in the first dropdownlist. So, what I need to know is: is there a sql statement that can return this type of information? Example: Table Names in Database: Customers, Suppliers Columns in Customers Table: Name, Phone, Email, Address I click on the word "Customers" in the first dropdownlist. I then see the words "Name", "Phone", "Email", "Address" in the second dropdownlist. I'm sure you all know this (but I'll say it anyways): I could hardcode this stuff in my code behind file, but that would be really annoying and if the table structure changes I would have to revise my code on the webpage. So any ideas on how to do this the right way would be really cool. Thanks in advance, Robert
View Replies !
|
TRACKER
