Spilt excel file into multiple excel files

Question

Post reply

Spilt excel file into multiple excel files

Jamie-136634

SSC-Addicted

Points: 418
More actions
March 3, 2006 at 4:27 am

#112205

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.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Joe Nakanishi-3 SSC Eights! Points: 828 More actions · Answer 1

You're going to have to read the original Excel file into SQL, then export each location to Excel.

Jamie-136634 SSC-Addicted Points: 418 More actions · Answer 2

Hi,

but how can I do that using T-SQL ?

I have over 300 different locations. I don't want to do it manually...

RyanRandall SSChampion Points: 13623 More actions · Answer 3

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.

David Burrows SSC Guru Points: 65144 More actions · Answer 4

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.

Jamie-136634 SSC-Addicted Points: 418 More actions · Answer 5

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.

David Burrows SSC Guru Points: 65144 More actions · Answer 6

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.

Jamie-136634 SSC-Addicted Points: 418 More actions · Answer 7

David, you're a frigging genius !

I'll have a play with this and let you know.

thank you so much !