100 Excel Sheets 2

[grayt26]

Hi all,
I have been given the task of taking 100 excel sheets and importing them into one table in sql. Due to the size of each sheet excel really doesn't like it when you try to put it into one sheet then import.

The only solution i can think of at the moment is to import them one by one then union each table together. Each sheet i have been told will have the same field names. I am dreading having a very long union statement for 100 sql tables.

Has anyone got any better ideas?

Thanks

 

[Jamfool]

Assuming that the sheets are in a standard format you could possible do the following:

SELECT *
FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\xls\myexcelsheet.xls',
'SELECT * FROM [Sheet1$]'

Notes:
C:\ path is on server
$ on end of sheet name

 

[grayt26]

hi thanks for this. Just trying it now but the securty settings won't let me. So the IT support are on it. Will let you know how i get on.

 

[AlexCuse]

Do you have MS Access?

Since they all have the same format, you could perhaps set up an import spec in access, bring the data in using the import spec, and then append it to your linked SQL table?

You could set up a loop to do this for each file in your directory.

 

[grayt26]

thanks all, we have managed to get it done the slow way but we are gonna keep looking at your suggestions as they seem very interesting.

Many Thanks

 

[chaoma]

Jamfool,
I like your query. Does anyone know how to delete or drop a sheet? I had try this, but it doesn't work.


SELECT *
FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\xls\myexcelsheet.xls',
'DELETE FROM [Sheet1$]'

Error: object had no column.

Thank alot.

 

[AlexCuse]

grayt, if you need it I believe I can dig up some code where I have set up a loop to import excel sheets. I'll try to find it later today.

Jamfool, do you know if it's possible to use the openrowset or bcp queries to access a spreadsheet on a local or network drive? I have had a lot of difficulty trying to find anything on that particular subject.

 

[AlexCuse]

GrayT,

Try something like this:

Public Function ImportSpreadsheet()

    Dim fso, f, f1, fc
    
    Dim folderspec          As String
    Dim PathAndFile         As String

    
    folderspec = "G:\Development\Test\" 'Location ofSource files

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.GetFolder(folderspec)                    'Specify Folder
    Set fc = f.Files
    
    For Each f1 In fc               'for each file in folderspec

    If Right(f1.Name, 4) = ".xls" Then
        PathAndFile = folderspec & f1.Name

        DoCmd.TransferSpreadsheet acImport,  acSpreadsheetTypeExcel97, "dbo2", PathAndFile, True  
        'True is for having Field Names in first row of S/S
    
   
        Debug.Print PathAndFile
   
    End If

    Next

End Function

and it should get the job done. You can set the destination table name (dbo2) to a linked SQL Server table and throw everything right into your intended destination. If you want to look at the data first just bring it into access and append if it looks good.

Hope this helps,

Alex

 

[Jamfool]

chaoma: I believe you can only select the data.

AlexCuse: Try mapping the network location to a drive letter.

http://support.microsoft.com/?kbid=321686