|
-
May 28th, 2012, 02:26 PM
#1
Thread Starter
New Member
Converting Excel file(s) to CSV file
Hi, I am new to VBScripting, i am looking for a batch script that looks for the files in a windows folder (Folder might have multiple files and file names will be same always) and convert them into a CSV file format.
I am going to FTP these CSV files into my linux server and load them into DB2 tables using my ETL tool.Please help me with this.
-
May 28th, 2012, 02:37 PM
#2
Thread Starter
New Member
Re: Converting Excel file(s) to CSV file
Just a quick change to the above post, File names are not always same.
-
May 28th, 2012, 04:43 PM
#3
Re: Converting Excel file(s) to CSV file
to do this you need to automate excel to saveAs or saveCopyAs file format and name .csv
are you doing this in vb6, vbs or from within excel?
from vb6 or vba you can use the DIR function to iterate through the files, in vbs you would have to use fso or shell automation, or you could use a shell object to both iterate through the files and upload them to your ftp server, no matter which vb you are using
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 28th, 2012, 05:49 PM
#4
Thread Starter
New Member
Re: Converting Excel file(s) to CSV file
Thanks for the quick reply, i would like to do it through VBS . I have gone through some of the posts and figured out this simple script that converts a excel file with particular filename. but in my case i don't know what the file name is going to be as vendor can place with any file name in the network drive.
Const workBookPath = "c:\VBScript\some.xls"
With CreateObject("Scripting.FileSystemObject")
savePath = .BuildPath(.GetParentFolderName(workBookPath), _
.GetBaseName(workBookPath))
End With
Set workBook = GetObject(workBookPath)
For Each sheet In workBook.Worksheets
sheet.SaveAs savePath & "." & sheet.Name & ".csv", 6
Next 'sheet
workbook.Close False
WScript.Echo "Done"
As i am new to VBScript please excuse any typos.
Last edited by haari; May 28th, 2012 at 06:16 PM.
-
May 28th, 2012, 11:11 PM
#5
Re: Converting Excel file(s) to CSV file
This is a VB forum, not a VBScript forum.
Maybe try http://www.vbforums.com/forumdisplay.php?f=4
Last edited by dilettante; May 29th, 2012 at 12:41 AM.
-
May 29th, 2012, 05:08 AM
#6
Re: Converting Excel file(s) to CSV file
you can try like
vb Code:
Set sh = CreateObject("shell.application") Set src = sh.namespace("c:\temp\lexar") Set dest = sh.namespace("ftp://username:password@www.someserver.com/somefolder") If src Is Nothing Then MsgBox "No Connection" Exit Sub End If savepath = src.parentfolder.self.Path ' or just put the folder path Set oxl = CreateObject("excel.application") For Each xls In src.items 'assumes all files in folder are xls Set owb = oxl.Workbooks.Open(xls) For Each sht In owb.Sheets sht.SaveAs savepath & "." & sht.Name & ".csv", 6 'if your workbooks 'can have same sheet names they will be overwritten by the next dest.copyhere savepath & "." & sht.Name & ".csv" ' upload to ftp ' to overwrite previous files 'without asking , add 16 as last parameter Next owb.Close Next oxl.Quit Set owb = Nothing Set oxl = Nothing Set src = Nothing Set dest = Nothing ' etc
change all paths to suit
if the source folder can have folders or files other than xls then you will need a conditional like
vb Code:
If xls.Type = "Microsoft Excel Worksheet" Then
this should work fine from scripting or vb6
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jun 1st, 2012, 06:52 AM
#7
Thread Starter
New Member
Re: Converting Excel file(s) to CSV file
Thanks for all your help WestConn1/dilettante, it worked for me.
Dilettante - Next time onwards i will post in the correct forums.
Thanks.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|