Convert csv, html, xml, etc to Excel native format (reward offered)
Scott Ballinger
larger audience I am also posting here... (is that an oxymoron?)
I have a variety of pick basic programs that produce html, xml, and
csv output. Often I name the output file aaaaa.xls so that when you
double-click it Excel is invoked. I need a script (like the
atwordmerge one below [from the asent forum!]) that will open a file
in Excel and then "Save as..." "microsft excel workbook (xls)."
Ideally it would work like this
call convert.excel.sub(filename) ;* overwrite orig file with the .xls
version
or even
execute "convert.excel.file ":filename
Where filename is either in the current working directory, or /the/
full/path/to/the/filename.
As I mentioned, perhaps one way to accomplish this is to do it in
Excel like this atwordmerge script does its thing in Word.
atwordmerge
001 * this prints a word mail-merge document using accuterm
002 * 09-08-04 asb
003
004 tclread buf
005 buf = trim(buf)
006 if field(buf," ",1) eq "run" then
007 buf = oconv(buf,"g2 99")
008 end
009 doc = field(buf," ",2)
010 if doc eq "" then stop
011
012 stx = char(2)
013 cr = char(13)
014 em = char(25)
015 esc = char(27)
016
017 * create object variables
018
019 scr = 'Dim WordApp As Object'
020 scr = scr:em:'Dim WordDoc As Object'
021 scr = scr:em:'Const SendToPrinter = 1'
022
023 * make sure errors dont mess us up
024
025 scr = scr:em:'On Error Resume Next'
026
027 * start up word
028
029 scr = scr:em:'Set WordApp = CreateObject("Word.Application")'
030
031 * open source document
032 scr = scr:em:'Set WordDoc = WordApp.Documents.Open(FileName:="n:
\':doc:'",
ReadOnly:=True)'
033
034 * merge the document with data file
035
036 scr = scr:em:'With WordDoc.MailMerge'
037 scr = scr:em:' .Destination = SendToPrinter'
038 scr = scr:em:' .SuppressBlankLines = True'
039 ***scr = scr:em:' .Execute Pause:=False'
040 scr = scr:em:' .Execute Pause:=True'
041
042 *** turn off sql prompt
043 ***scr = scr:em:' application.DisplayAlerts=wdAlertsNone'
044
045 scr = scr:em:'End With'
046 scr = scr:em:' WordDoc.Close savechanges:=False'
047 scr = scr:em:' Set WordDoc = Nothing'
048 scr = scr:em:' WordApp.Quit'
049 scr = scr:em:' Set WordApp = Nothing'
050
051 print esc:stx:'P':scr:cr:
052
$50 to the first, best solution.
/Scott Ballinger
Pareto Corporation
Edmonds WA USA
206 713 6006
Albert D. Kallal
idea as proc in pick).
They are very simlar with parmaters etc.
Just save the following text as a docuemnt on windows with file extension
vbs
(lets assume name ExcelCV.vbs
dim ExcelApp
dim excelWorkBook
dim strFromDoc
dim strToDoc
strFromdoc = WScript.Arguments(0)
strToDoc = WScript.Arguments(1)
set excelApp = createObject("Excel.Application")
excelApp.Workbooks.Open strFromDoc
excelApp.ActiveWorkbook.SaveAs strToDoc
excelApp.ActiveWorkBook.Close 0
excelApp.Quit
Ok, now, from the windows prompt you can go:
c:\myscripts\ExcelCV.vbs "c:\rawdata\test.csv" "c:\ExcelData\test.csv"
The above will run the script. You should use full path name for the script,
but the above is really all you need on the windows side. As for the
particular terminal emulator etc, the syntax to execute a command line
varies.
So, all you have to do is execute the above command line..
Windows scripting can be fun, and it very much reminds me of writing procs
from the old pick days...
--
Albert D. Kallal
Edmonton, Alberta Canada
pleaseNOO...@msn.com
Scott Ballinger
Note that both D3 and your windows workstation must have a path to the
file you are converting (in my case [linux], a samba share).
Corrections or improvements appreciated.
/Scott
convert.excel
001 * convert file in csv, tab, etc format to Excel worksheet
002 * If path not specified, file to convert must be in \\uncpath
\convertexcel\
003 * output file will be put in same if path not specified, if output
file not specified then input file will be converted
004 * 12-29-09 asb
005
006 stx = char(2)
007 cr = char(13)
008 em = char(25)
009 esc = char(27)
010
011 tclread buf
012 buf = trim(buf)
013 if field(buf," ",1) eq "run" then
014 buf = oconv(buf,"g2 99")
015 end
016
017 infile = field(buf," ",2)
018 outfile = field(buf," ",3)
019
020 if infile eq "" then stop "no file specified? syntax:
convert.excel INFILE {OUTFILE}"
021
022 if infile[1,2] ne "\\" and infile[2,1] ne ":" then ;* if no path
specified
then use default
023 infile = '\\uncpath\convertexcel\':infile
024 end
025
026 if outfile eq "" then outfile = infile
027 if outfile[1,2] ne "\\" and outfile[2,1] ne ":" then
028 outfile = '\\uncpath\convertexcel\':outfile
029 end
030
031 scr = "dim ExcelApp"
032 scr<-1> = "dim ExcelWorkBook"
033 scr<-1> = "dim strFromDoc"
034 scr<-1> = "dim strToDoc"
035 **scr<-1> = "on error resume next"
036 scr<-1> = "const wksFormat=-4143"
037 scr<-1> = 'strFromDoc = "':infile:'"'
038 scr<-1> = 'strToDoc = "':outfile:'"'
039 scr<-1> = 'set ExcelApp = CreateObject("Excel.Application")'
040 scr<-1> = "ExcelApp.DisplayAlerts = FALSE" ;* no dialog boxes!
041 scr<-1> = "ExcelApp.Workbooks.Open strFromDoc"
042 scr<-1> = "ExcelApp.ActiveWorkbook.SaveAs strToDoc,wksFormat"
043 scr<-1> = "ExcelApp.ActiveWorkBook.Close 0"
044 scr<-1> = "ExcelApp.Quit"
045
046 convert @am to em in scr
047
048 print esc:stx:'P':scr:cr:
049
050
051 ** convertexcel.vbs script to do same...
052 **dim ExcelApp
053 **dim excelWorkBook
054 **dim strFromDoc
055 **dim strToDoc
056 **
057 **const wks=-4143
058 **strFromdoc = WScript.Arguments(0)
059 **strToDoc = WScript.Arguments(1)
060 **
061 **set excelApp = createObject("Excel.Application")
062 **
063 **excelApp.DisplayAlerts = FALSE
064 **excelApp.Workbooks.Open strFromDoc
065 **excelApp.ActiveWorkbook.SaveAs strToDoc,wks
066 **excelApp.ActiveWorkBook.Close 0
067 **
068 **excelApp.Quit
Scott Ballinger
\'s on the command line; but you can use /'s and then convert them to
\'s.
/Scott
* convert file in csv, tab, etc format to Excel worksheet
* file to convert must be in \\uncpath\convertexcel\
* output file will be put in same.
* 12-29-09 asb
* 12-29-09 asb: [1] D3 Can't execute with \'s on command line,
* so use /'s and convert to \'s here.
stx = char(2)
cr = char(13)
em = char(25)
esc = char(27)
tclread buf
buf = trim(buf)
if field(buf," ",1) eq "run" then
buf = oconv(buf,"g2 99")
end
infile = field(buf," ",2)
outfile = field(buf," ",3)
if infile eq "" then stop "no file specified? syntax: covert.excel
INFILE {OUTFILE}"
convert "/" to "\" in infile ;* [1]
convert "/" to "\" in outfile
if infile[1,2] ne "\\" and infile[2,1] ne ":" then ;* if no path
specified then use default
infile = '\\uncpath\convertexcel\':infile
end
if outfile eq "" then outfile = infile
if outfile[1,2] ne "\\" and outfile[2,1] ne ":" then
outfile = '\\uncpath\convertexcel\':outfile
end
scr = "dim ExcelApp"
scr<-1> = "dim ExcelWorkBook"
scr<-1> = "dim strFromDoc"
scr<-1> = "dim strToDoc"
**scr<-1> = "on error resume next"
scr<-1> = "const wksFormat=-4143"
scr<-1> = 'strFromDoc = "':infile:'"'
scr<-1> = 'strToDoc = "':outfile:'"'
scr<-1> = 'set ExcelApp = CreateObject("Excel.Application")'
scr<-1> = "ExcelApp.DisplayAlerts = FALSE" ;* no dialog boxes!
scr<-1> = "ExcelApp.Workbooks.Open strFromDoc"
scr<-1> = "ExcelApp.ActiveWorkbook.SaveAs strToDoc,wksFormat"
scr<-1> = "ExcelApp.ActiveWorkBook.Close 0"
scr<-1> = "ExcelApp.Quit"
convert @am to em in scr
print esc:stx:'P':scr:cr:
** convertexcel.vbs script to do same...
**dim ExcelApp
**dim excelWorkBook
**dim strFromDoc
**dim strToDoc
**
**const wks=-4143
**strFromdoc = WScript.Arguments(0)
**strToDoc = WScript.Arguments(1)
**
**set excelApp = createObject("Excel.Application")
**
**excelApp.DisplayAlerts = FALSE
**excelApp.Workbooks.Open strFromDoc
**excelApp.ActiveWorkbook.SaveAs strToDoc,wks
**excelApp.ActiveWorkBook.Close 0
**
**excelApp.Quit