|
Hi All,
I have an Excel file in which there are multiple sheets and its columns are spread in multiple rows like a parent has multiple children and those children have their children.
How can I load this data in to a table sheet by sheet, means for each load if it loads from one sheet that's fine but important thing here is columns are spread into multiple rows.
I am also searching, but any link, any suggestion or code snippet is going to be very very helpful. Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
|
Sorry I didn't mention it may be but I want to do it SQL Server script. So I am trying in the following way
SELECT * INTO #Servxxx
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml; HDR=YES; IMEX=1;
Database=D:\Servxxx.xls',
[Servxxx First Sheet$]);
It is giving me the following error
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine could not find the object 'D:\Servxxx.xls'. Make sure the object exists and that you spell its name and the path name correctly. If 'D:\Servxxx.xls' is not a local object, check your network connection or contact the server administrator.".
Msg 7303, Level 16, State 1, Line 10
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
It is saying file is not there, but file is there and it is erroring out with link server etc.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Is D: a local drive, or a mapped network drive?
Does the SQL service account have permission to access the file?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hey Rick, thanks I got it sort out my friend here is the sample one may be somebody has the same issue.
SELECT * INTO #xxxxxxs
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml; HDR=YES; IMEX=1;
Database=C:\xxxxxxx\Dont Remove ThisFolder\xxxxxxs.xlsx',
'SELECT * FROM [Back xxxxx$]');
I have a question here, do we have any way to copy the existing schema structure into a table variable, along with an identity column?
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
I am using Sql server 2008.
I have 3 columns say, UserID,ManagerID,deptCode.
I need the output as UserID,ManagerID,deptCode,ManagersDeptCode
So, the column "ManagerID" need to be used to generate the output of "ManagersDeptCode".
Please help.
|
|
|
|
|
Join back to your user table using the managerid ot userid to get the managers details
Select U., M.
From UserTable U
inner join UserTable M on M.userID = U.ManagerID
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi All,
I have a table A in it I have columns like Id, ParentId, ParentName, Child1Id, Child1Name, Child2Id, Child2Name. ParentId is the parent of Child1Id, and Child1Id is the parent of Chil2Id. Id is the Primary key of this table.
I want to have a select statement which, I have to get ParentIds, ParentNames order by ParentNames, under each Parent, I want to have Child1Ids, Child1Names, order by Child1 Names, Under each Child1s, I want to have its child2Ids and Child2Names.
ParentId, ParentName
Child1Id, Child1Name
Child2Id Child2Name
Child2Id Child2Name
Child2Id Child2Name
Child1Id Chil1Name
Child2Id Child2Name
Is there any way to write a query like this? Please help me, any type of help, a link, a code snippet or a suggestion would be greatly helpful, thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Hi everyone, I have a temporary table as shown below
tempTable(ID int not null identity(1,1) primary Key, Col1 int, Col2 int, Col3 int)
I am trying to get data of each column, store them in a variable, and manipulate them as I loop through the table using while loop.
The following is what I have tried
create proc [dbo].[spGetData]
@Col1 int
@Col2 int
@Col3 int
as begin
declare @count int
declare @currentColumn varchar(25)
declare @currentColumnVal varchar(25)
set @count = 1
set @currentColumn = NULL
set @currentColumnVal = NULL
create #tempTable(ID int not null identity(1,1) primary Key, Val1 int, Val2 int, Val3 int)
insert #tempTable(Val1, Val2, Val3) Values(@Col1, @Col2, @Col3)
set @count = 1
while @count < 3
begin
set @currentColumn = 'Val' + CAST(@count AS VARCHAR(25))
select @currentColumnVal = @currentColumn from #tempTable
set @count = @count + 1
end
print @currentColumnVal
end
I'm not getting any errors but I'm getting 0 as the return value which is incorrect.
modified 23-Oct-15 20:46pm.
|
|
|
|
|
What were you expecting? There is no final select in this SP. In fact you can't create the SP because there are errors reported.
If I fix the errors ... commas after @Col1 int and @Col2 int and create TABLE #tempTable the Stored Procedure is created and when I run it, it prints "Val2" - which is what I would expect given what you have in the body of the SP.
Are you actually trying to use dynamic sql? Have a look at this CP article - Building Dynamic SQL In a Stored Procedure[^]
You also need to understand how to return values from Stored Procedures - see MSDN article[^]
|
|
|
|
|
--[Spgetdata] 1,2,3
ALTER PROC [dbo].[Spgetdata] @Col1 INT,
@Col2 INT,
@Col3 INT
AS
BEGIN
DECLARE @count INT
DECLARE @currentColumn NVARCHAR(25)
DECLARE @currentColumnVal NVARCHAR(25)
SET @count = 1
SET @currentColumn = NULL
SET @currentColumnVal = NULL
CREATE TABLE #tempTable
(
ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
Val1 INT,
Val2 INT,
Val3 INT
)
INSERT #tempTable
(Val1,
Val2,
Val3)
VALUES(@Col1,
@Col2,
@Col3)
SET @count = 1
WHILE @count <= 3
BEGIN
SET @currentColumn = 'Val' + Cast(@count AS VARCHAR(25))
--SELECT @currentColumnVal = @currentColumn
--FROM #tempTable
DECLARE @Q NVARCHAR(Max)
SET @Q = 'SELECT @currentColumnValX = '
+ @currentColumn + ' FROM #tempTable'
EXEC Sp_executesql
@Q,
N'@currentColumnValX varchar(500) Output',
@currentColumnVal Output
-- Do stuff
-- Do more Stuff
SET @count = @count + 1
END
PRINT @currentColumnVal -- Just to see what the value of the last Column is
END
|
|
|
|
|
There are several files that contains (in csv format) names given to babies, both male and female and their rankings of a particular country for every year from 1944 to 2013.Now we have have create a database so that we can search popular names by birth year.
|
|
|
|
|
First you'd have to decide which database-server; there are various ones out there, some paid, some free, some simple, some complex.
If the database supports SQL (most do), then you could issue a CREATE DATABASE command.
If it will be a local application, using it only for lookups or leaning much on reporting, then you may want to look into MS Access. If there's going to be updates and reporting is not an issue, I'd recommend SQLite. If it is not a local application, I'd recommend Sql Server Express.
Those can all be used for free; interacting is done as documented.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
This Zip file contains (in csv format) (Link1 or Link2 or Link3) names given to babies, both male and female and their rankings of a particular country for every year from 1944 to 2013. Create an interface which can display the following queries:
Popular Names by Birth Year: User can enter a year and choice like Top 10, Top 20..Top 1000 etc and the relevant names will be displayed. Choices should be there for only males, only females and both. Name rankings should also include Number of Births
Popularity of a name that has changed over the years: Given a name and a year it should show (preferably in a graphical format) how popularity has changed starting from that particular year till 2013. For example if a user inputs: Brendon and a year 2000 it should show the popularity of the name starting from 2000 to 2013. Again choice of Male and Female should be there because some names can be both male and female.(actually this is the project that i have been given and i am using oracle 11g i guess if that's fine for this project)
|
|
|
|
|
Oracle is a good choice; now you'd first need a way to get that data in Oracle. You could type in a lot of insert-statements, but there's probably a tool out there that can upload the CSV to an existing database.
Next, someone will have to create a UI (I'd recommend .NET) and create a way for the user to set named options. They can all be translated to a change in the <a href="https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm">SELECT</a>[<a href="https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm" target="_blank" title="New Window">^</a>] statement that has to be generated.
The select-query is a nice piece of homework; the example actually looks as if each option is there to introduce another part of the select-command. I'd recommend focussing on that before you try to 'add' the popularity-indication.
You'd need to filter on both gender and year in the WHERE statement, limit your results, and dynamically build a query from code.
When is the deadline?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
deadline is 1st november,and everything you said after 2nd paragraph was over my head.ps-I am new to database.
|
|
|
|
|
|
i am going to use php for the database connectivity.
|
|
|
|
|
Good, that makes the UI nice and simple, and not much mucking around with forms. Just a few input-fields, a submit button, and a result. Another benefit is that one could demo it on a smartphone.
..but working on that makes more sense once you have the data in the database. Did you create a database on your database-server yet?
If yes, then you'd need to create the table to hold the data. If you post the top two lines from your example CSV, someone might help with the create table or the select-command 
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
will i have to load all the files in a single table or mutiple table?i haven't created a database yet.
|
|
|
|
|
That will depend on the layout of your CSV files. If there's no difference in layout, then it might be done using a single table. I can imagine calculating the popularity as an occurence of the name between a set of dates, compared to 'any' other name on the same set of dates.
..that is, of the same gender, of course. A female-baby name does not compete with any of the male-baby names.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
|
I cannot download the zip, just list its contents. Means I also cannot view the content of these files. Still, by the naming-pattern of the file, I'd guess that all files have the same layout and that a single table would do.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
yeah the layout is same,but i will have to add two columns gender and year to specify the gender and year right?
|
|
|
|
|
If they are not in the original data as columns, then yes, please do; it will make creating a selection-query a lot easier - you'll need them for calculating popularity 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
