March 3, 2006 at 4:27 am
Hi,
I have an excel spreadsheet which has location, name and DOB inthe rows.
how can I split this spreadsheet into multiple spreadsheets so each location has its own spreadsheet. ?
Is this something that can be done via T-SQL If I was to place the spreadsheet into a folder say C:\data spilt\
thank you for any info.
March 3, 2006 at 6:43 am
You're going to have to read the original Excel file into SQL, then export each location to Excel.
March 3, 2006 at 6:47 am
Hi,
but how can I do that using T-SQL ?
I have over 300 different locations. I don't want to do it manually...
March 3, 2006 at 7:10 am
Unless there's more to the problem than you've described, I'd solve it using VBA, rather than T-SQL (in which case this is the wrong forum, so apologies).
Something along the lines of (as a very simple example)...
Public Sub Main()
Call CopyDataToNewWorkbook("Location 1")
Call CopyDataToNewWorkbook("Location 2")
End Sub
Public Sub CopyDataToNewWorkbook(Criteria As String)
Dim ws As Worksheet
Dim wb As Workbook
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Cells.AutoFilter Field:=1, Criteria1:=Criteria
ws.Cells.Copy
Set wb = Workbooks.Add
wb.Sheets(1).Paste
End Sub
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
March 3, 2006 at 7:13 am
in TSQL
INSERT INTO
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=YES;Database=C:\temp\book2.xls', [sheet1$])
(location, [name], DOB)
SELECT location, [name], DOB
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=YES;Database=C:\temp\book1.xls', [sheet1$])
WHERE location = 'locationwanted'
the only caveat with this method is that Book2.xls (in this case) has to exist
Far away is close at hand in the images of elsewhere.
Anon.
March 3, 2006 at 7:19 am
Hi, both are nice suggestions.
I like Davids idea.
Is there a way of actually looping through the locations automatically and populating the WHERE location = 'locationwanted' part .
locations can be any number from 1 to 99999 but not all.
btw; thank you for all the help so far.
March 3, 2006 at 7:34 am
CREATE TABLE #temp (location int, [name] varchar(40), DOB datetime)
INSERT INTO #temp (location, [name], DOB)
SELECT location, [name], DOB
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=YES;Database=C:\temp\book1.xls', [sheet1$])
DECLARE @location int
SELECT @location = MIN(location) FROM #temp
WHILE @location IS NOT NULL
BEGIN
-- need to put code here to make sure file exists e.g. Book2.xls
INSERT INTO
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=YES;Database=C:\temp\book2.xls', [sheet1$])
(location, [name], DOB)
SELECT location, [name], DOB
FROM #temp
WHERE location = @location
-- Rename Book2.xls to actual filename required
SELECT @location = MIN(location) FROM #temp WHERE location > @location
END
Far away is close at hand in the images of elsewhere.
Anon.
March 3, 2006 at 7:38 am
David, you're a frigging genius !
I'll have a play with this and let you know.
thank you so much !
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply