VBScript to convert XML to CSV

Question

I have a VBScript that converts any XML file in a folder to XLS and then deletes the XML files - all works fine.

However, I know need to convert the XMLs to CSV rather than XLS.

What do I need to change in the script to allow this? Simply changing the extension of the resulting file(s) obviously doesn't work.

Dim xlApp, xlWkb, SourceFolder,TargetFolder,file
Set xlApp = CreateObject("excel.application")
Set fs = CreateObject("Scripting.FileSystemObject")

Const xlNormal=1

SourceFolder="c:\xml-to-xls\xml"
TargetFolder="c:\xml-to-xls\xls"

xlApp.Visible = false

for each file in fs.GetFolder(SourceFolder).files
  Set xlWkb = xlApp.Workbooks.Open(file)
  BaseName= fs.getbasename(file)
  FullTargetPath=TargetFolder & "\" & BaseName & ".xls"
  xlWkb.SaveAs FullTargetPath, xlNormal
  xlWkb.close
next

fs.DeleteFile("C:\xml-to-xls\xml\*.xml")

Set xlWkb = Nothing
Set xlApp = Nothing
Set fs = Nothing

Thanks

I've removed all comments from the code. It's absolutely unnecessary to comment like this: `xlWkb.close ' close workbook` - the code *already says that*. You are essentially writing your program twice - that's nonsense. Don't do that. — Tomalak, Oct 29 '13 at 09:03

ServerGuy · Accepted Answer · 2013-11-01T11:59:42.307

2

Updating as per the comments: Thanks guys

Const xlCSV = 6
xlWkb.SaveAs FullTargetPath, xlCSV, , , , , , 2
xlWbk.Saved = True
xlWkb.close

edited Nov 01 '13 at 11:59

answered Oct 29 '13 at 08:50

ServerGuy

555
3
11

2

`xlCSV` is not available as a name in VBScript, you'd need to do a `Const xlCSV = 6` beforehand. – Tomalak Oct 29 '13 at 08:55
I'd put it after the other `Const` definition. – Tomalak Oct 29 '13 at 09:04
will give it a try and see what the results are – chenks Oct 29 '13 at 09:04
no that doesn't seem to work as it should. it is prompting for every file if i want to save the changes to a CSV file, which requires me to click YES before it completes. Also, if i do click YES, the resulting file is not the same as if i had manually saved the XLS file (using origin vbscript) as a CSV. – chenks Oct 29 '13 at 09:10
You may need to specify the [`ConflictResolution`](http://msdn.microsoft.com/en-us/library/office/ff841185.aspx) argument (`xlWkb.SaveAs FullTargetPath, xlCSV, , , , , , 2`. And how exactly do the results differ? CSV is a different format that doesn't support all features XLS(X) provides. – Ansgar Wiechers Oct 29 '13 at 16:25
i meant that simply giving the file a CSV extension doesn't automatically make it a CSV file, which is what the script was essentially doing. – chenks Oct 30 '13 at 08:05
OK, have used the ConflictResolution argument as mentioned and this now saves the XMLs to CSVs. 1 issues though, i get a save prompt for each file asking if i want to save the changes. i need this to be a silent process that requires no manual user interaction. – chenks Oct 31 '13 at 09:15
@chenks Add a line `xlWbk.Saved = True` before you close the workbook. – Ansgar Wiechers Oct 31 '13 at 23:39
one final question, what do I need to add so that if there are no files currently present in the XML folder then the script ends with error. at the moment if there are no files to convert then it produces a "file not found" error prompt – chenks Nov 01 '13 at 08:16
@chenks That's probably caused by the line `fs.DeleteFile("C:\xml-to-xls\xml\*.xml")`. To avoid this, move the deletion inside the loop and delete each file after it's been processed (`file.Delete`). – Ansgar Wiechers Nov 01 '13 at 23:14

score 2 · Answer 2 · answered Nov 04 '13 at 12:00

thanks guys... here is the finished script

Dim xlApp, xlWkb, SourceFolder,TargetFolder,file
Set xlApp = CreateObject("excel.application")
Set fs = CreateObject("Scripting.FileSystemObject")

Const xlNormal=1
Const xlCSV=6

SourceFolder="c:\xml-to-xls\xml"
TargetFolder="c:\xml-to-xls\xls"

xlApp.Visible = false

for each file in fs.GetFolder(SourceFolder).files
  Set xlWkb = xlApp.Workbooks.Open(file)
  BaseName= fs.getbasename(file)
  FullTargetPath=TargetFolder & "\" & BaseName & ".csv"
  xlWkb.SaveAs FullTargetPath, xlCSV, , , , , , 2
  xlWkb.Saved = True
  xlWkb.close
  file.Delete
next

Set xlWkb = Nothing
Set xlApp = Nothing
Set fs = Nothing

VBScript to convert XML to CSV

2 Answers2