Combine multiple spreadsheets into single SQL DB

[CarolCook]

Hi:
I have been charged with converting data from a hundred or more spreadsheets with identical formats (but obviously different values) into a SQL DB.
Any ideas on how to start?
Thanks in advance,
Carol

 

[vongrunt]

spreadsheets = like Excel?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[CarolCook]

Yes. Actually, these are Excel workbooks that have been replicated over and over. I would have to have something that could recurse through a folder, examine each spreadsheet, append to the DB and go on to the next. (I'm sure it will be a little uglier than that because I bet the data on each worksheet will be dependent on the first but a starting point will be greatly appreciated!)
Thanks!

 

[vongrunt]

The key SQL statement here is OPENROWSET(). Check Books Online for more details.

Personally I would write small client-side app (VB, C# or whatever), scan through folder (a la with Scripting.FileSystemobject) and then use Excel object model and/or OPENROWSET(), depending on complexity of sheets.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[vongrunt]

Erm, correction: see OPENDATASOURCE() in BOL.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[CautionMP]

... write small client-side app (VB, C# or whatever), scan through folder (a la with Scripting.FileSystemobject) ...

Or you could use a macro in Excel to combine the workbooks and output the data in a format that works best for importing into SQL.

Asked and answered: Microsoft: Office Forum: Combine multiple spreadsheets into single SQL DB

CMP

 

[SQLSister]

And oh by the way, you had better check to make sure they have the same format. We often get multiple spreadsheets that are supposed to be the same that move the columns around or have a column missing or add an extra one in the middle. I got one today that had the last names inthe first name column and vice versa. Freaked me out when it didn't match any of our data!

Oh and those hidden columns are a problem sometimes too.

And if you have leading zeros, it usually pays to convert to a text file so you don't have to put the leading zeros back in. For some reason Sql Server will treat any field that starts with a number as a number field on import even if Excel has it marked as text.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[AlexIT]

I have used the following code to merge a single Excel file into a DB...you can probably get it to recurse a directory and grab each excel file pretty easily:

Note: In my case I had unique ID fields so I only compared this to see if it was present in the DB, you may need to compare more throughly.

Sub Merger()

'Declare variables for Excel
Dim oExcel As Object
Dim xlsh1 As Object
Dim xlwb As String

'Declare array for excel file records
Dim axl() As String

'Declare variables for database connectivity
Dim conn As adodb.Connection
Dim rs As adodb.Recordset
Dim statement As String

'Declare array for database records
Dim adb() As String

'Declare counters
Dim row As Long
Dim i As Long
Dim j As Long
Dim x As Long
Dim y As Long
Dim Match as Integer

'Assign order of desired fields
ID = 0
Name = 1
Measure = 2
TypeCode = 3

'Start Excel for inputed file
xlwb = UCase(InputBox("Enter Excel file with extension:", "xlwb"))
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.open xlwb, false, true
Set xlsh1 = oExcel.ActiveWorkbook.Worksheets(1)
row = xlsh1.UsedRange.Rows.Count

'Resize Excel array per number of fields/rows
Redim axl(4, row)

'Open a connection to the database
Set conn = New adodb.Connection
conn.Open "DSN=NAME;" & "TRUSTED_CONNECTION=YES"

'Define which fields to be read and provide sort order
statement = "SELECT ID, Name, UoM, TypeCode, FROM Table ORDER BY ID"
Set rs = conn.Execute(statement, , adCmdText)

'Resize record array per number of fields/ max rows
ReDim adb(4, 64999)

'Start by getting the database
Do While Not rs.EOF
i=0
'Pull each field from database into the array
adb(ID, i) = rs.Fields(ID).Value
adbr(Name, i) = rs.Fields(Name).Value
adb(Measure, i) = rs.Fields(Measure).Value
adb(TypeCode, i) = rs.Fields(TypeCode).Value
i = i + 1
rs.MoveNext
Loop

'Resize record array now that we know how big
ReDim adb(4, i - 1)

'Get the data from Excel file
For j = 1 To row
axl(Id, j) = xlsh1.Cells(j, 1).Value
axl(Name, j) = xlsh1.Cells(j, 2).Value
axl(Measure, j) = xlsh1.Cells(j, 3).Value
axl(TypeCode, j) = xlsh1.Cells(j, 4).Value
Next j

'Perform compare to see if xl data exists in db
For x = 0 To i - 1
Match = 0
For y = 0 to row
If adb(ID, x) = axl(Id, y) Then Match = 1
Next y
If Match <> 1 Then

'Add record to database
rs.AddNew
rs!ID = axl(ID, y)
rs!Name = axl(Name, y)
rs!Measure = axl(Measure, y)
rs!TypeCode = axl(TypeCode, y)
rs.Update
End If
Next x

'Clear counters
row = 0
i = 0
j = 0
x = 0
y = 0
Match = 0

'Clear field variables
ID = 0
Name = 0
Measure = 0
TypeCode = 0

'Clear arrays
ReDim adb(0,0)
ReDim axl(0,0)

Close connection to the database
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

'Close Excel without saving
xlsh1.Close False
oExcel.Quit

'Clear Excel variables
xlwb = ""
Set oExcel = Nothing
Set xlsh1 = Nothing

End Sub

 

[SQLDenis]

openrowset should work with .csv or excel

--csv
select * from OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\;Extensions=CSV;',
'SELECT * FROM Q4_2005_DJIA_updates.csv')


--excel
select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\Q4_2005_DJIA_updates.xls', 'Select * from [Q2 2003 DJIA updates$]')




Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/