 |
AutoHotkey Community
Let's help each other out
|
| View previous topic :: View next topic |
| Author |
Message |
BoBo
Guest
|
 Posted: Thu Jul 13, 2006 8:55 pm Post subject: xls2csv - Convert xls to csv [CMD+GUI] |
 |
|
Kindly provided by an AutoIt geek  (yep, the optional GUI is made with AutoIt) | Quote: | xls2csv - Recode a spreadsheet's charset and save as CSV.
usage: xls2csv -x spreadsheet.xls [-w worksheet] [-b charset] [-c csvfile.csv] [
-a charset] [-qshvW]
-x : filename of the source spreadsheet
-b : the character set the source spreadsheet is in (before)
-c : the filename to save the generated csv file as
-a : the character set the csv file should be converted to (after)
-q : quiet mode
-s : print a list of supported character sets
-h : this help message
-v : get version information
-W : list worksheets in the spreadsheet specified by -x
-w : specify the worksheet name to convert (defaults to the first worksheet)
example: xls2csv -x "spreadsheet.xls" -b WINDOWS-1252 -c "csvfile.csv" -a UTF-8
[Download] |
|
|
| Back to top |
|
 |
PhiLho
Joined: 27 Dec 2005
Posts: 6721
Location: France (near Paris)
|
| Posted: Sun Jul 16, 2006 2:58 pm Post subject: |
|
|
catdoc & xls2csv
http://www.45.free.net/~vitus/software/catdoc/
I prefer the original pages, when available...
_________________
 vPhiLho := RegExReplace("Philippe Lhoste", "^(\w{3})\w*\s+\b(\w{3})\w*$", "$1$2") |
|
| Back to top |
|
|
BoBo
Guest
|
| Posted: Tue Jul 18, 2006 11:32 am Post subject: |
|
|
VBS code, stolen from [here] ... | Code: | option explicit
dim a, arg, oArgs, ArgNum
a = 0
Set oArgs = WSCript.arguments
ArgNum = oArgs.Count
if ArgNum <> 1 then
WSCript.echo "Syntax: cscript <Script> filename_root"
WScript.quit(1)
end if
dim filename_root, oldfilename, pos,newname
oldfilename = oArgs(0)
rem Remove .xls file extension if it was provided
dim ext
ext = right( oldfilename, 4 )
if lcase( ext ) = ".xls" then
oldfilename = left( oldfilename, len( oldfilename ) - 4 )
end if
pos = instr(oldfilename, " ")
if pos > 0 then
newname = right(oldfilename, len(oldfilename) - instrrev(oldfilename, "\"))
newname = replace(newname," ","_")
filename_root = left(oldfilename, instrrev(oldfilename, "\")) & newname
dim oShell
set oShell = Wscript.CreateObject("WScript.Shell")
Wscript.echo "cmd.exe /c ren """ & oldfilename & ".xls"" " & newname & ".xls"
oShell.run "cmd.exe /c ren """ & oldfilename & ".xls"" " & newname & ".xls"
else
filename_root = oldfilename
end if
dim app
set app = createobject("Excel.Application")
dim wb
set wb = app.workbooks.open( filename_root & ".xls" )
const xlXMLSpreadsheet = 46
const xlCSV = 6
app.DisplayAlerts = false
dim sht
for each sht in wb.worksheets
sht.activate
dim output_filename
output_filename = filename_root & "_" & replace( sht.name, " ", "_" ) & ".csv"
wb.saveAs output_filename, xlCSV
next
'wb.saveAs filename_root & ".xml", xlXMLSpreadsheet
app.DisplayAlerts = true
wb.close false
'app.close
WScript.quit
|
Save that as a VBS (xlstocvs.vbs), then use the following batch script:
Code:
for %%i in (*.xls) do cscript xlstocsv.vbs %%~dpni > nul
Put the batch script in the same directory as the XLS files, and xlstocsv.vbs, then run it from the command line.
... still wishing AHK would provide VBS/JS-Include commands  .
| Code: | (AHKCode)
VBSStart
(VBSCode)
VBSEnd
(AHKCode) |
 |
|
| Back to top |
|
|
BoBo
Guest
|
|
| Back to top |
|
|
BoBo
Guest
|
| Posted: Tue Jul 18, 2006 11:52 am Post subject: |
|
|
Any idea how to use this [xlsconv.dll] with AHK (the correct answer is DllCall(), I know that one already  ) |
|
| Back to top |
|
|
PhiLho
Joined: 27 Dec 2005
Posts: 6721
Location: France (near Paris)
|
| Posted: Tue Jul 18, 2006 12:58 pm Post subject: |
|
|
Exported functions:
| Dependency Walker wrote: | CanLaunchApplication
EndSession
ExportFromFile
GetDataDescription
GetFileFilter
GetLastErrorCode
GetLastErrorDescription
GetSuggestedDestinationFile
InitSession
IsKnownFile
IsNokiaDBConvertExporter
IsNokiaDBConvertImporter
LaunchApplication
MakeExportableFile
MakePreviewFile
SetDestinationFile
SetGaugeAndDescription
SetSourceFile
ShouldSelectMemoryAB
ShouldSelectMemoryPC
|
I searched some names (MakeExportableFile, SetGaugeAndDescription, IsNokiaDBConvertImporter), none of them was found in Google, so I guess we are out of luck.
_________________
vPhiLho := RegExReplace("Philippe Lhoste", "^(\w{3})\w*\s+\b(\w{3})\w*$", "$1$2") |
|
| Back to top |
|
|
AutoWeet
Guest
|
| Posted: Tue Jul 18, 2006 5:55 pm Post subject: |
|
|
AutoIt beta. if that kinda thing interests you.
| Code: | $oldfilename = FileOpenDialog("Get XLS",@ScriptDir,"Excel (*.xls)",1)
If @error Then Exit
$ext = StringRight($oldfilename,4)
If StringLower($ext) = ".xls" Then
$oldfilename = StringLeft($oldfilename,StringLen($oldfilename ) - 4 )
EndIf
$pos = StringInStr($oldfilename, " ")
If $pos > 0 then
$newname = StringRight($oldfilename,StringLen($oldfilename) - StringInStr($oldfilename, "\",0,-1))
$newname = StringReplace($newname," ","_")
$filename_root = StringLeft($oldfilename, StringInStr($oldfilename, "\",0,-1)) & $newname
FileMove($oldfilename & ".xls",$newname & ".xls")
else
$filename_root = $oldfilename
EndIf
$app = ObjCreate("Excel.Application")
$wb = $app.workbooks.open($filename_root & ".xls" )
Const $xlXMLSpreadsheet = 46
Const $xlCSV = 6
$app.DisplayAlerts = 0
For $sht in $wb.worksheets
$sht.activate
$output_filename = $filename_root & "_" & StringReplace($sht.name, " ", "_" ) & ".csv"
$wb.saveAs($output_filename,$xlCSV)
Next
$app.DisplayAlerts = 1
$wb.close(0) |
|
|
| Back to top |
|
|
not-logged-in-daonlyfreez
Guest
|
| Posted: Tue Jul 18, 2006 6:54 pm Post subject: |
|
|
Both the .vbs method and the AutoIt3 (beta) method have it's flaws:
- Not all users have enabled VBS (security reasons)
- Not everybody owns/uses Excel |
|
| Back to top |
|
|
lingoist
Joined: 05 Oct 2004
Posts: 118
Location: Brasília, Brazil
|
| Posted: Sun Apr 01, 2007 7:13 pm Post subject: |
|
|
Here it goes my contribution with only 1 AHK file with 2 finctions.
Thanks BoBo!
| Code: | #SingleInstance force
chosen_folder = %A_ScriptDir%
ConvertXlsCsv(chosen_folder)
Exitapp
ConvertXlsCsv(folder="") {
{ ;FOLDER
If folder =
{
FileSelectFolder, folder, , , Folder with xls files
If ErrorLevel = 1
Return
IfNotExist %folder%
Return
}
}
{ ;LIST FILES TO BE CONVERTED
Loop, %folder%\*.xls
files=%files%`n%A_LoopFileName%
MsgBox, 0, Files to be converted, %files%, 3
}
{ ;VBS CODE
;ORIGINAL VBS CODE FROM Jimmy The Fish
;http://forums.xaprief.com/showthread.php?s=&threadid=3159
vbs_code =
(
option explicit
dim a, arg, oArgs, ArgNum
a = 0
Set oArgs = WSCript.arguments
ArgNum = oArgs.Count
if ArgNum <> 1 then
WSCript.echo "Syntax: cscript <Script> filename_root"
WScript.quit(1)
end if
dim filename_root, oldfilename, pos,newname
oldfilename = oArgs(0)
rem Remove .xls file extension if it was provided
dim ext
ext = right( oldfilename, 4 )
if lcase( ext ) = ".xls" then
oldfilename = left( oldfilename, len( oldfilename ) - 4 )
end if
pos = instr(oldfilename, " ")
if pos > 0 then
newname = right(oldfilename, len(oldfilename) - instrrev(oldfilename, "\"))
newname = replace(newname," ","_")
filename_root = left(oldfilename, instrrev(oldfilename, "\")) & newname
dim oShell
set oShell = Wscript.CreateObject("WScript.Shell")
Wscript.echo "cmd.exe /c ren """ & oldfilename & ".xls"" " & newname & ".xls"
oShell.run "cmd.exe /c ren """ & oldfilename & ".xls"" " & newname & ".xls"
else
filename_root = oldfilename
end if
dim app
set app = createobject("Excel.Application")
dim wb
set wb = app.workbooks.open( filename_root & ".xls" )
const xlXMLSpreadsheet = 46
const xlCSV = 6
app.DisplayAlerts = false
dim sht
for each sht in wb.worksheets
sht.activate
dim output_filename
output_filename = filename_root & "_" & replace( sht.name, " ", "_" ) & ".csv"
wb.saveAs output_filename, xlCSV
next
'wb.saveAs filename_root & ".xml", xlXMLSpreadsheet
app.DisplayAlerts = true
wb.close false
'app.close
WScript.quit
)
FileDelete, %folder%\xls2csv.vbs
FileAppend, %vbs_code%, %folder%\xls2csv.vbs
}
{ ;BATCH FILE
FileDelete, %folder%\xls2csv.bat
FileAppend, for `%`%i in (*.xls) do cscript xls2csv.vbs `%`%~dpni > nul , %folder%\xls2csv.bat
}
{ ;RUN VBS FILE THROUGH BAT
RunWait, %folder%\xls2csv.bat, %folder%
}
{ ;DELETE TEMP FILES
FileDelete, %folder%\xls2csv.vbs
FileDelete, %folder%\xls2csv.bat
}
{ ;CONVERT INTO PORTUGUESE FORMAT (OPTIONAL)
If A_Language = 0416
{
ConvertCsvSemiColon(folder)
MsgBox, 0, Arquivos convertidos em CSV português, Verifique a pasta original, 15
Return
}
}
MsgBox, 0, Files converted, Please check original folder, 15
}
ConvertCsvSemiColon(folder) {
Loop, %folder%\*.csv
{
;REWRITE ONLY FILES MODIFIED IN THE LAST 5 MINUTES
FileGetTime, file_time, %A_LoopFileName%, M
minutes_ago = %A_Now%
minutes_ago += -5, Minutes
If file_time < %minutes_ago%
Continue
;READ CONTENT
FileRead, content, %A_LoopFileName%
;REPLACE COMMAS ETC
StringReplace, content, content, `,, `;, all
StringReplace, content, content, `., `,, all
;REWRITE CSV
FileDelete, %A_LoopFileName%
FileAppend, %content%, %A_LoopFileName%
}
}
|
|
|
| Back to top |
|
|
icefreez
Joined: 15 May 2007
Posts: 34
|
| Posted: Fri Oct 05, 2007 10:33 pm Post subject: Questions |
|
|
Is this last script a functional XML > CSV converter? I have been attempting to get it running but no matter what I did I could not get the script to do much more than list out the XML files in the scripts directory, attempt do something to them, and then end up leaving the files as is.
I am about an average scripter in AHK but not very good with VBS at all |
|
| Back to top |
|
|
engunneer
Joined: 30 Aug 2005
Posts: 6308
Location: Pacific Northwest, US
|
| Posted: Sat Oct 06, 2007 12:28 am Post subject: |
|
|
try the XPath function for reading XML data - it works great!
_________________
 Unless otherwise noted, all code is untested.
Common Answers: 1.(Loops, Viruses, etc.) 2. Search 3.RTFM |
|
| Back to top |
|
|
lingoist
Joined: 05 Oct 2004
Posts: 118
Location: Brasília, Brazil
|
| Posted: Sat Oct 06, 2007 3:43 pm Post subject: Re: Questions |
|
|
| icefreez wrote: | Is this last script a functional XML > CSV converter? I have been attempting to get it running but no matter what I did I could not get the script to do much more than list out the XML files in the scripts directory, attempt do something to them, and then end up leaving the files as is.
I am about an average scripter in AHK but not very good with VBS at all |
Icefreez,
It has been writen for XLS not XML...
lingoist |
|
| Back to top |
|
|
icefreez
Joined: 15 May 2007
Posts: 34
|
| Posted: Mon Oct 08, 2007 4:14 pm Post subject: |
|
|
Sorry I meant to type XLS but I use XML files more in my day to day activities so I always type XML lol.
Anyone able to get these scripts to properly convert an XLS file? |
|
| Back to top |
|
|
lingoist
Joined: 05 Oct 2004
Posts: 118
Location: Brasília, Brazil
|
| Posted: Tue Oct 09, 2007 1:36 am Post subject: |
|
|
| icefreez wrote: | Sorry I meant to type XLS but I use XML files more in my day to day activities so I always type XML lol.
Anyone able to get these scripts to properly convert an XLS file? |
Dear IceFreez,
Try this code:
| Code: | #SingleInstance force
chosen_folder = %A_ScriptDir%
ConvertXlsCsv(chosen_folder)
Exitapp
ConvertXlsCsv(folder="") {
{ ;FOLDER
If folder =
{
FileSelectFolder, folder, , , Folder with xls files
If ErrorLevel = 1
Return
IfNotExist %folder%
Return
}
}
{ ;LIST FILES TO BE CONVERTED
Loop, %folder%\*.xls
files=%files%`n%A_LoopFileName%
MsgBox, 0, Files to be converted, %files%, 3
}
{ ;VBS CODE
;ORIGINAL VBS CODE FROM Jimmy The Fish
;http://forums.xaprief.com/showthread.php?s=&threadid=3159
vbs_code =
(
option explicit
dim a, arg, oArgs, ArgNum
a = 0
Set oArgs = WSCript.arguments
ArgNum = oArgs.Count
if ArgNum <> 1 then
WSCript.echo "Syntax: cscript <Script> filename_root"
WScript.quit(1)
end if
dim filename_root, oldfilename, pos,newname
oldfilename = oArgs(0)
rem Remove .xls file extension if it was provided
dim ext
ext = right( oldfilename, 4 )
if lcase( ext ) = ".xls" then
oldfilename = left( oldfilename, len( oldfilename ) - 4 )
end if
pos = instr(oldfilename, " ")
if pos > 0 then
newname = right(oldfilename, len(oldfilename) - instrrev(oldfilename, "\"))
newname = replace(newname," ","_")
filename_root = left(oldfilename, instrrev(oldfilename, "\")) & newname
dim oShell
set oShell = Wscript.CreateObject("WScript.Shell")
Wscript.echo "cmd.exe /c ren """ & oldfilename & ".xls"" " & newname & ".xls"
oShell.run "cmd.exe /c ren """ & oldfilename & ".xls"" " & newname & ".xls"
else
filename_root = oldfilename
end if
dim app
set app = createobject("Excel.Application")
dim wb
set wb = app.workbooks.open( filename_root & ".xls" )
const xlXMLSpreadsheet = 46
const xlCSV = 6
app.DisplayAlerts = false
dim sht
for each sht in wb.worksheets
sht.activate
dim output_filename
output_filename = filename_root & "_" & replace( sht.name, " ", "_" ) & ".csv"
wb.saveAs output_filename, xlCSV
next
'wb.saveAs filename_root & ".xml", xlXMLSpreadsheet
app.DisplayAlerts = true
wb.close false
'app.close
WScript.quit
)
FileDelete, %folder%\xls2csv.vbs
FileAppend, %vbs_code%, %folder%\xls2csv.vbs
}
{ ;BATCH FILE
FileDelete, %folder%\xls2csv.bat
FileAppend, for `%`%i in (*.xls) do cscript xls2csv.vbs `%`%~dpni > nul , %folder%\xls2csv.bat
}
{ ;RUN VBS FILE THROUGH BAT
RunWait, %folder%\xls2csv.bat, %folder%
}
{ ;DELETE TEMP FILES
FileDelete, %folder%\xls2csv.vbs
FileDelete, %folder%\xls2csv.bat
}
{ ;CONVERT INTO PORTUGUESE FORMAT (OPTIONAL)
If A_Language = 0416
{
ConvertCsvSemiColon(folder)
MsgBox, 0, Arquivos convertidos em CSV português, Verifique a pasta original, 15
Return
}
}
MsgBox, 0, Files converted, Please check original folder, 15
}
ConvertCsvSemiColon(folder) {
Loop, %folder%\*.csv
{
;REWRITE ONLY FILES MODIFIED IN THE LAST 5 MINUTES
FileGetTime, file_time, %A_LoopFileName%, M
minutes_ago = %A_Now%
minutes_ago += -5, Minutes
If file_time < %minutes_ago%
Continue
;READ CONTENT
FileRead, content, %A_LoopFileName%
;REPLACE COMMAS ETC
StringReplace, content, content, `,, `;, all
StringReplace, content, content, `., `,, all
;REWRITE CSV
FileDelete, %A_LoopFileName%
FileAppend, %content%, %A_LoopFileName%
}
} |
|
|
| Back to top |
|
|
icefreez
Joined: 15 May 2007
Posts: 34
|
| Posted: Tue Oct 09, 2007 2:44 pm Post subject: |
|
|
I am unable to get this script to convert an XLS to a CSV file. It shows "Files to be converted, runs the bat file, then says "Files Converted" "Please checked the original folder" The file remains unchanged in the scripts directory, and no new files have been created.
Any suggestions what is going wrong? |
|
| Back to top |
|
|
|
|
You can post new topics in this forum
You can reply to topics in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
FAQ
Search
Memberlist
Register
Profile
Log in to check your private messages
Log in
