Spilt excel file into multiple excel files

  • 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.

     

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

  • Hi,

    but how can I do that using T-SQL ?

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

  • 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.

  • 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.

  • 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.

  • 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.

  • 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