Designing a report in SQL from an Excel worksheet

Tags:
Microsoft Excel
SQL
SQL Server reporting
I am trying to design a report from an Excel worksheet, and I’m not sure how to get this information into SQL. I have 180 files with multiple tabs in each that house all the data I need, but I only need one tab from each of the files. Is there an easy way to import this specific information?

Answer Wiki

Thanks. We'll let you know when a new response is added.

What I would do is create a VBS script that converts the one tab into a CSV file and load the CSV into SQL through your DB utilities.

Here is a VB script I have that will convert every tab into a separate CSV script. If you know how to identify the tab (say, it’s always the 2nd tab), then it is easy to change the script to suit your needs.

You can then run a batch to process all your 180 files.

‘ VB Script to remove rows from a trade spreadsheet supplied by client so we can move it to staging table
‘ Usage: cscript “PATH1\Convert to CSV.vbs” “PATH2\spreadsheet” [ “NEW_PATH”>
‘ where
‘ PATH1 is the full path of the vbs script
‘ PATH2 is the full path of the spreadsheet to be transferred
‘ spreadsheet is the actual name of the .xls spreadsheet file
‘ NEW_PATH is the optional new location of where the csv file will be located (default is the same directory as the spreadsheet
‘ Can also just drag the file to use onto the VB script

option explicit

‘ Parse arguments
dim oArgs, ArgNum

Set oArgs = WSCript.arguments
ArgNum = oArgs.Count

if ArgNum < 1 or ArgNum > 2 then
WSCript.echo “Syntax: cscript Convert to CSV.vbs filename [newpath]”
WScript.quit(1)
end if

‘ Create source (old) and target (new) filenames
dim oldfilename, newpathname
dim fObj, sPath, sBaseName, sNewPath

oldfilename = oArgs(0)
set fObj = CreateObject(“Scripting.FileSystemObject”)
sPath = fObj.GetParentFolderName(oldfilename)
sBaseName = fObj.GetBaseName(oldfilename)

if ArgNum = 2 then
dim fObj2
newpathname = oArgs(1)
set fObj2 = CreateObject(“Scripting.FileSystemObject”)
sNewPath = fObj2.GetAbsolutePathName(newpathname)
else
sNewPath = sPath
end if

if right(sNewPath, 1) <> “” and right(sNewPath, 1) <> “/” then
sNewPath = sNewPath & “”
end if

dim app
set app = createobject(“Excel.Application”)
app.DisplayAlerts = False

dim wb
set wb = app.workbooks.open( oldfilename )

const xlXMLSpreadsheet = 46
const xlCSV = 6

app.DisplayAlerts = false
dim sht
dim cnt
cnt = 0
for each sht in wb.worksheets
sht.activate
cnt = cnt + 1

‘Save worksheet as CSV file
dim output_filename
output_filename = sNewPath & sBaseName & “_” & cnt &”.csv”
wb.saveAs output_filename, xlCSV
next
app.DisplayAlerts = true

wb.close false

WScript.quit

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following