Designing a report in SQL from an Excel worksheet
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?
Software/Hardware used:
Software/Hardware used:
ASKED:
September 22, 2008 3:20 PM
UPDATED:
September 23, 2008 6:03 PM
Answer Wiki:
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 "PATH1Convert to CSV.vbs" "PATH2spreadsheet" [ "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
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
