none
Macro to convert multiple txt to csv format RRS feed

  • Question

  • Hello Team, 

    I am trying to convert multiple .txt file having tab delimited to .csv file. However the code that I am using is also considering ',' as delimited in addition to 'tab'. I have posted the code below and it would be great help if somebody could help me rectify the below code to accept only tab as a delimiter.

    Thanks
    Sub txt2csv()
     Dim Fname As String, ipath As String, retstring, fs, a, i As Long
     With Application.FileDialog(msoFileDialogFolderPicker)
     .AllowMultiSelect = False
     If .Show = -1 Then ipath = .SelectedItems(1) Else Exit Sub
     End With
     Application.ScreenUpdating = False: On Error Resume Next
     Fname = Dir(ipath & "\*.txt")
     Do While Fname <> ""
     Set fs = CreateObject("Scripting.FileSystemObject")
     Set a = fs.OpenTextFile(ipath & "\" & Fname, 1, 0)
    
    
     'retstring = Replace(a.readall, vbTab, " " & Replace(Fname, ".txt", "") & "," & vbTab) & "," & Replace(Fname, ".txt", "") & ",": a.Close
     
    retstring = Replace(a.readall, vbTab, " " & Replace("", ".txt", "") & "," & "") & "," & Replace(Fname, ".txt", "") & ",": a.Close
     
    
    
    
    
    If Err.Number = 0 Then
     Open ipath & "\" & Fname For Output As #1
     Print #1, retstring
     Close #1
     Name ipath & "\" & Fname As ipath & "\" & Replace(Fname, "txt", "csv")
     Else
     Err.Clear
     End If
     Fname = Dir
     Loop
     Set fs = Nothing: Application.ScreenUpdating = False
     End Sub

    Thursday, July 17, 2014 3:08 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support

    Friday, July 18, 2014 2:40 AM
  • CSV format stands for Commas Separated Values, so tab delimited is a bit odd. I would have a macro to import each txt file into Excel then use Excel to save as csv format. Try doing it for one file manually to see if the result is what you want. If it is, record a macro of you doing it manually again, then build a loop around it for doing multiple files.

    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Friday, July 18, 2014 8:15 AM
  • Yes, I saw tab sign in CSV files

    I use that category of division:

    XLS_2_CSV_separator_PL

    Then I checked the file coding (Unicode and UTF-8). In clean English alphabets it probably does not matter, but in other languages ​​encoding affects the reading and writing of non-standard characters.

    Another thing. You can use words placed in the apostrophe and the numbers do not.

    In CSV you can see one standard of writing, byt in Excel you have many. So I think twice or even engage excel or not, to do this in a clean VBA without Excels objects.

    Regards.

     

    Oskar Shon, Office System MVP - www.VBATools.pl
    if Helpful; Answer when a problem solved

    Saturday, July 19, 2014 6:06 AM
    Answerer