|
caiguosen wrote: I want to import all these files into sql server 2005,
and build these tables automatically.
I don`t know how to do it?
There's more than one way to do so. You might be interested in this[^] KB-article
I are Troll
|
|
|
|
|
I know this solution, but I have several thousands excel Files,
and their version are EXCEL2.1, I still do't know how to import so many excel2.1 files to sql server?
I have such a solution,but I first need to save excel files to the latest version, and it only can import one file to sql server.
for example:
SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\tt.SZ.xls', 'SELECT * FROM [tt$]')
|
|
|
|
|
caiguosen wrote: I know this solution, but I have several thousands excel Files,
and their version are EXCEL2.1, I still do't know how to import so many excel2.1 files to sql server?
You're opening the Excel-file as a database over JET - that means that you're importing from a lot of databases. You can't concatenate the Excel-files, as the Excel-format is limited to 65536 rows.
There's an article on importing multiple files here[^] that might help.
I are Troll
|
|
|
|
|
Thank Eddy Vluggen very much!
I'll try it.
|
|
|
|
|
My pleasure, and good luck
I are Troll
|
|
|
|
|
the version of the excel files which i want to import is Excel2.1,
if I use the following solution to import, it can not work.
for example:
select *
into dbo.Temple FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\a.xls;HDR=YES',
'SELECT * FROM [a$]')
The fact is that I have to open it and save it as the later version.Unfortunately,I have thousands of excel files so that I couldn`t open them one by one.
Are there some solutions that I change their version quickly?
or some solutions that can help me to import excel2.1 to sql server
directly?
|
|
|
|
|
caiguosen wrote: the version of the excel files which i want to import is Excel2.1,
if I use the following solution to import, it can not work.
Can you try it with the Excel 5.0 format, like this?
select *
into dbo.Temple FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=D:\a.xls;HDR=YES',
'SELECT * FROM [a$]')
caiguosen wrote: Are there some solutions that I change their version quickly?
I don't know of any, you'd have to Google for those. I'd try to read the file using C#, and write it directly to a database
I are Troll
|
|
|
|
|
I tried following solution,but it still can not work.
it can not import excel2.1 to sql server.
select *
into dbo.Temple FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=D:\a.xls;HDR=YES',
'SELECT * FROM [a$]')
do you have some c# code that can import excel2.1 to sql server2005 quickly?
I google some code which was written by c++,but most of them can not read excel2.1, and some of them can read, but the speed is really slowly.
the problemis that I have thousands of excel files,not only one.
or do you have some solution that can save many excel2.1 files as later version?
if i open it and save as it, it can be imported.
|
|
|
|
|
caiguosen wrote: do you have some c# code that can import excel2.1 to sql server2005 quickly?
I'm sorry, but I don't have that code. If I did, I would've posted it.
caiguosen wrote: I google some code which was written by c++,but most of them can not read excel2.1, and some of them can read, but the speed is really slowly.
the problemis that I have thousands of excel files,not only one.
There's no "quick" way to get those Excel-files in the database. Version 2.1 is an old file-format, perhaps you could find a tool that converts those files in batch, but I doubt it. If you can read the files, even if it's slow, than you can convert them.
If you can write a console-application that reads a single file, then you can re-use that to import the other files. If "slowly" means that it takes a minute, than 1000 files take 1000 minutes. That's roughly 16 hours, so the conversion would be done within a day.
I are Troll
|
|
|
|
|
ok,thank you for your help.
I think I need to search other solutions, otherwise it will cost me too much time.
|
|
|
|
|
Reference to the following article:
http://www.codersbarn.com/post/2008/10/12/Bind-CheckBoxList-to-DataSet.aspx
I created the following table
1) Should I set all the MusicFeature values by default to zero.
2) How should I create foreign constraits. ProfileID.
3) ProfileID is used in a tbl_Prrofile and is a primary key.
4) How should this table be fixed. Also I need to keep in mind what should be null.
CREATE
TABLE [dbo].[tbl_Music](
[MusicID] [int]
IDENTITY(1,1) NOT NULL,
[ProfileID] [int]
NULL,
[MusicText] [nvarchar]
(64) NOT NULL,
[MusicFeature] [bit]
NULL,
CONSTRAINT [PK_tbl_lookup_music] PRIMARY KEY CLUSTERED
(
[MusicID]
ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY]
--------------------------------
CREATE
TABLE [dbo].[tbl_Profile](
[ProfileID] [int]
IDENTITY(1,1) NOT NULL,
[UserID] [nvarchar]
(50) NULL,
[UserName] [nvarchar]
(50) NULL,
CONSTRAINT [PK_tbl_Profile] PRIMARY KEY CLUSTERED
(
[ProfileID]
ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET
ANSI_PADDING OFF
-----------------------------------
tbl_Music.
MusicID ProfileID MusicText MusicFeature
1 NULL Prefer not to say NULL
2 NULL African NULL
3 NULL Blues NULL
4 NULL Caribbean NULL
|
|
|
|
|
Imagine beating all your competition and flooding your site
with tons of free targeted traffic! Hardly anyone knows about this
incredible secret free tool, so watch this free video before your
competition does, and in just 6 minutes, you'll be in the top spot on
Google - and never spend a penny to do it - no catch - no gimmick.
http://www.clicknearn.net/3601-78.html
|
|
|
|
|
If you want to advertise with CP then go through the usual channel.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Good Day All
i have the Following sp
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Timetable_View] @selectionType varchar(30), -- either Venues, Staff, Subjects, Curricula etc...
@selectedItems ntext, @selectedTerms ntext
AS
*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tempdb].[dbo].[#temp]'))
drop table [#temp]
--Creation of Temp1
SELECT MTN.ID,S.DESCR,ISNULL(MTN.CYCLETEMPLATE,C.CYCLES) AS CYCLETEMPLATE,MTN.ACTV AS [ACTV]
into #temp
FROM TBL_STAFF S
INNER JOIN MTM_ACTV_STAFF MTN ON
S.ID = MTN.STAFF
LEFT OUTER JOIN MTM_ACTV_STAFF_CYCLE C
ON C.IDL = MTN.ID
--STEP 3 HERE WE ARE CREATING A TEMP TABLE
--CHECK IF THE TABLE EXISTS FIRST IF IT DOES DROP IT
--HERE WE ARE CREATING A FIELD CYCLEIDlIST THAT IS EMPTY THAT WILL BE POPULATED LATER
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tempdb].[dbo].[#TempSummary]'))
drop table [#TempSummary]
SELECT DESCR, dbo.GetSortedString(Cast(NULL AS varchar(8000))) AS CycleIdList,ACTV
INTO #TempSummary
FROM #temp (NOLOCK)
GROUP BY DESCR,ACTV
ORDER BY DESCR,ACTV
--CHECK THE TABLE CONTENTETS
--SELECT * FROM TempSummary
--WE ONLY SAW NAMES THAT ARE NOT DUPLICATED AND NULLS IN THE OTHER FIELD
--HERE WE UPDATE THE TEMPSUMMARY TABLE AND DO SOME CONCATINATING AND THIS IS VERY FAST,
--STEP 4
UPDATE #TempSummary
SET CycleIdList = dbo.Concat(#TempSummary.Descr,actv)
--SELECT * FROM TempSummary
--LETS CHECK THE TEMP SUMMARY TABLE
--select * from TempSummary
--IT CONTAINS ALL THE DATA THAT WE WANT :)
--Select DISTINCT Descr AS [Staff],dbo.DistinctList(Cycles,Descr) As [Cycles]
--into Temp2 From temp
Declare @xmldoc int
EXEC sp_xml_preparedocument @xmldoc OUTPUT, @selectedItems --Create an internal representation of the XML document
-- turn the xml into a table with characteristics of the given table
SELECT *
INTO #selectedItems
FROM OPENXML ( @xmldoc , '/Root/Tags' , 2)
WITH (
[TagID] int
)
EXEC sp_xml_removedocument @xmldoc
create table #selectedActvs (
id int
)
-- From the list of selected items of whichever type, find the list of activities these relate to; i.e. which activities are envolved in the selection
if (@SelectionType = 'Venues') begin
-- show the timetable for the selected activities
Insert into #selectedActvs
(id)
select distinct Actv [Id]
from sol_actv_venu
where venu in ( select TagID from #selectedItems )
end
if (@SelectionType = 'Staff') begin
Insert into #selectedActvs
(id)
select distinct Actv [Id]
from sol_actv_staff
where staff in ( select TagID from #selectedItems )
end
if (@SelectionType = 'Subjects') begin
Insert into #selectedActvs
(id)
select distinct Actv [Id]
from sol_actv_time
inner join tbl_actv a on a.id = sol_actv_time.Actv
inner join tbl_cntc ct on ct.id = a.cntcID
where modlID in ( select TagID from #selectedItems )
end
if (@SelectionType = 'SubjectContainer') begin
Insert into #selectedActvs
(id)
select distinct Actv [Id]
from sol_actv_time
inner join tbl_actv a on a.id = sol_actv_time.Actv
inner join tbl_cntc ct on ct.id = a.cntcID
inner join tbl_modl m on m.id=ct.modlid
inner join mtm_modl_container mc on mc.modl=m.id
where mc.container in ( select TagID from #selectedItems )
end
if (@SelectionType = 'Curricula') begin
Insert into #selectedActvs
(id)
select distinct sa.Actv [Id]
from sol_actv_time sa
inner join mtm_subj_strm_actv ss on ss.actv = sa.actv
inner join mtm_curr_strm cs on cs.subjstrmid = ss.strm
inner join tbl_curr_strm c on c.id = cs.currstrmid
where c.curr in ( select TagID from #selectedItems )
end
if (@SelectionType = 'Curriculum Streams') begin
Insert into #selectedActvs
(id)
select distinct sa.Actv [Id]
from sol_actv_time sa
inner join mtm_subj_strm_actv ss on ss.actv = sa.actv
inner join mtm_curr_strm cs on cs.subjstrmid = ss.strm
inner join tbl_curr_strm c on c.id = cs.currstrmid
where c.id in ( select TagID from #selectedItems )
end
EXEC sp_xml_preparedocument @xmldoc OUTPUT, @selectedTerms --Create an internal representation of the XML document
-- turn the xml into a table with characteristics of the given table
SELECT *
INTO #selectedTerms
FROM OPENXML ( @xmldoc , '/Root/Tags' , 2)
WITH (
[TagID] int
)
EXEC sp_xml_removedocument @xmldoc
select distinct tt.Dy, tt.Sess, m.descr as [Code], m.LongName as[Description], ctyp.Abrev, ctyp.Descr as [Type], ct.Number,
a.GrpName, a.GrpNumber, a.Duration,
CASE WHEN MAV.stud IS NULL THEN a.Students
ELSE MAV.STUD END as [Students],
v.descr as [Venue], v.Capacity,
s.descr as [Staff], m.id as [ModlID], t.Descr as [Term]
, ISNULL(temp.CYCLEIDLIST,t.Descr) as [StaffTerm]
, dates.descr [Date],a.Length as [Length]
from sol_actv_time tt
inner join tbl_clmn dates on dates.id=tt.dy
inner join tbl_actv a on a.id = tt.actv
inner join #selectedActvs SelectedActvs on SelectedActvs.ID = a.id -- filter the list of activies to be shown by the activities which fall in the selection
inner join tbl_cntc ct on ct.id = a.cntcid
inner join tbl_cntc_typ ctyp on ctyp.id = ct.cntctyp
inner join tbl_modl m on m.id = ct.modlid
inner join tbl_term t on t.id = a.term --This will become t.id = actv.term when terms move to activities
inner join #selectedTerms selTerms on selTerms.TagID = a.term
left outer join sol_actv_venu ttVenu on ttVenu.actv = a.id
left outer join SOL_ACTV_VENU mav on mav.venu=ttVenu.Venu and mav.actv=a.id
left outer join tbl_venue v on v.id = ttVenu.Venu
left outer join sol_actv_staff ttStaff on ttStaff.Actv = a.id
left outer join mtm_actv_staff mas on mas.actv=ttStaff.actv and mas.staff=ttStaff.Staff
left outer join tbl_term staffTerm on staffTerm.id=mas.cycletemplate
left outer join tbl_staff s on s.id = ttStaff.Staff
left outer join [#TempSummary] temp on temp.descr = s.descr
order by tt.dy, tt.sess
This sp is fine and working Perfectly and i have a UDF defined like this
ALTER FUNCTION [dbo].[Concat] (@Name varchar(50),@Actv int)
RETURNS varchar(max)
WITH EXECUTE AS CALLER
AS
BEGIN
Declare @s varchar(max)
SET @s = ''
SELECT @s = @s + IsNull(',' + Cast(Cycletemplate AS varchar), '')
FROM #Temp (NOLOCK)
WHERE #Temp.Descr = @Name And Temp.Actv = @Actv
GROUP BY Cycletemplate
ORDER BY Cycletemplate
IF (@S IS NOT NULL) AND (@S <> '') SET @S = SubString(@s, 2, Len(@S)-1)
RETURN @S
END
now the UDF is used in the Bolded part of the Sp
i know i cant use a #temp table in a UDF , i want to access a temp table created in the Sp i want to use it in the UDF. i can use table Variables, but is there a Global table variable ? that i can use, that is created in the sp but used in the UDF. I need your Advice.
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
You can use global temp tables ##TableName, this may work. Caveat, I have never attempted this so I'm not sure.
You need to be careful with these, if you have multiple users processing then you will get a race condition.
You can also use XML variables to pass table data between a proc and a function, I'm not sure how you would return the XML to the proc but it could always be your output! You should be able to return an XML variable as well.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I tried the Global temps but the UDF does not want to associate itself with anything thathas to do with # and i tried table Variables and the Query perfomance degraded. and again i tried Common Table Express and the same thing, the Perfomance
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Vuyiswa Maseko wrote: SELECT @s = @s + IsNull(',' + Cast(Cycletemplate AS varchar), '')
FROM #Temp (NOLOCK)
The #Temp table would be empty there? How about using a real table, instead of a temporary one?
I are Troll
|
|
|
|
|
Hi guys, is there a way to retrieve, for a given record, the names of all the columns where the values of those columns for that record match a given condition?
ID A B C D E F G
A1 1 0 1 1 0 1 0
Like if I want to retrieve, for record "A1", all the column names where the value = 1, it would return "A, C, D, F, G".
Can this be done in SQL directly?
Thanks!!
modified on Friday, January 15, 2010 8:56 PM
|
|
|
|
|
There is no easy way, you will need to write some code of your own. Gotchas that spring to mind are datatype conversion and accuracy when comparing numbers - floats for example are only approximate. Its not difficult, but does need a bit of planning and a lot of testing.
Bob
Ashfield Consultants Ltd
Proud to be a Code Project MVP
|
|
|
|
|
Not the nicest looking solution, but it does the trick;
SELECT ID,
CASE WHEN A=1 THEN 'A'
ELSE ''
END +
CASE WHEN B=1 THEN 'B'
ELSE ''
END +
CASE WHEN C=1 THEN 'C'
ELSE ''
END +
CASE WHEN D=1 THEN 'D'
ELSE ''
END +
CASE WHEN E=1 THEN 'E'
ELSE ''
END
FROM [TestTable]
Good luck
I are Troll
|
|
|
|
|
Thank you Eddy, unfortunately I've got 90 columns for conditional check, so I'll have to improvise. However, I could see myself using this code potentially in the future with tables that have fewer columns.
Many thanks again.
|
|
|
|
|
IMO if you need this, you really should rethink your table design. It probably should have been fewer (two or three) columns and more rows, something like ID, name, value
|
|
|
|
|
Unfortunately so far I couldn't think of a better table design without making it even more complicated.
However, I am going to take an alternate route by putting them into an array first, and check for the conditions from there in a loop. This is currently the best I can come up with.
|
|
|
|
|
Go with Luc's suggestion rethink your data structure, this one will drive you nuts. Try this
CREATE TABLE [dbo].[Attr](
[AttrID] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] int,
[AttrType] [varchar](255) NULL,
[AttrValue] [varchar](255) NULL,
[Modified] [datetime] NULL,
[ModifiedBy] [varchar](50) NULL)
Where AttrType = your column Name and AttrValue = 1/0.
CustomerID is the foreign key to the entity you are applying the attributes to.
You would only have 4 rows representing the ACDF colums (0 deemed to not exist)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I tried to store the picture present in the MY Picture location of my system
but unable to do so
Qurey
CREATE TABLE StockGifs (StockGifID int NOT NULL, Gif varbinary(max) NOT NULL)
INSERT StockGifs (StockGifID, Gif)
SELECT 1, BulkColumn
FROM OPENROWSET (BULK 'C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Blue hills',SINGLE_BLOB) AS x
Error Message :Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Blue hills" does not exist.
Let me know what is the change need to be done
If the location alone Can be stored then how to convert a jpeg file to binary file.
Thanks in Advance,
Praveen Arokiam
|
|
|
|
|