Results 1 to 7 of 7

Thread: Converting Excel file(s) to CSV file

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2012
    Location
    Charlotte,nc
    Posts
    4

    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.

  2. #2

    Thread Starter
    New Member
    Join Date
    May 2012
    Location
    Charlotte,nc
    Posts
    4

    Re: Converting Excel file(s) to CSV file

    Just a quick change to the above post, File names are not always same.

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,621

    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

  4. #4

    Thread Starter
    New Member
    Join Date
    May 2012
    Location
    Charlotte,nc
    Posts
    4

    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.

  5. #5
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,483

    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.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,621

    Re: Converting Excel file(s) to CSV file

    you can try like

    vb Code:
    1. Set sh = CreateObject("shell.application")
    2. Set src = sh.namespace("c:\temp\lexar")
    3. Set dest = sh.namespace("ftp://username:password@www.someserver.com/somefolder")
    4.  
    5. If src Is Nothing Then
    6.   MsgBox "No Connection"
    7.   Exit Sub
    8. End If
    9. savepath = src.parentfolder.self.Path   ' or just put the folder path
    10. Set oxl = CreateObject("excel.application")
    11. For Each xls In src.items  'assumes all files in folder are xls
    12.     Set owb = oxl.Workbooks.Open(xls)
    13.     For Each sht In owb.Sheets
    14.         sht.SaveAs savepath & "." & sht.Name & ".csv", 6  'if your workbooks
    15.         'can have same sheet names they will be overwritten by the next
    16.         dest.copyhere savepath & "." & sht.Name & ".csv"   ' upload to ftp
    17.         ' to overwrite previous files
    18.         'without asking , add 16 as last parameter
    19.     Next
    20.     owb.Close
    21. Next
    22. oxl.Quit
    23. Set owb = Nothing
    24. Set oxl = Nothing
    25. Set src = Nothing
    26. Set dest = Nothing
    27. ' 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:
    1. 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

  7. #7

    Thread Starter
    New Member
    Join Date
    May 2012
    Location
    Charlotte,nc
    Posts
    4

    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
  •  



Click Here to Expand Forum to Full Width