none
How to convert a file from tab delimited to comma delimited?

    Question

  • I ran the following code to convert a tab delimited file to a comma delimted file:

    Sub Convert()
    Const ForReading = 1, ForWriting = 2
        Dim fso, MyTabFile, MyCsvFile, FileName
        Dim strFileContent As String
        Set fso = CreateObject("Scripting.FileSystemObject")
        ' Open the file for input.
        Set MyTabFile = fso.OpenTextFile("C:\CMI.csv", ForReading)
        ' Read the entire file and close.
        strFileContent = MyTabFile.ReadAll
        MyTabFile.Close
        ' Replace tabs with commas.
        strFileContent = Replace(expression:=strFileContent, _
                                 Find:=vbTab, Replace:=",")
        ' Can use Chr(9) instead of vbTab.
        ' Open a new file for output, write everything, and close.
        Set MyCsvFile = fso.OpenTextFile("C:\CMI_Done.csv", ForWriting, True)
        MyCsvFile.Write strFileContent
        MyCsvFile.Close
    End Sub

    Of almost 100,000 rows, only about 8,000 were converted, and of those that were converted, I'm not sure it was done right.  For instance, I had several consecutive blanks, if several adjacent cells were empty.  I would expect to see several consecutive commas, if several adjacent cells are blank.  Also, the headers were totally screwed up; can't even explain what happened.

    Any thoughts on this?  I need to find a special way to open this CSV, because when I open it, I get records with Scientific Notation.  I want to do the conversion w/out causing those fields to records with really large number, to convert to Scientific Notation.


    Ryan Shuell

    Tuesday, July 17, 2012 6:57 PM

Answers

  • Perhaps your file is too big to process in one chunk. The following code will be much slower, but hopefully also more dependable:

    Sub Convert2()
        Dim f1 As Long
        Dim f2 As Long
        Dim strLine As String
        f1 = FreeFile
        Open "C:\CMI.csv" For Input As #f1
        f2 = FreeFile
        Open "C:\CMI_Done.csv" For Output As #f2
        Do While Not EOF(f1)
            Line Input #f1, strLine
            Print #f2, Replace(strLine, vbTab, ",")
        Loop
        Close #f1
        Close #f2
    End Sub

    Remarks:

    • If the original file contains text strings with commas, they will mess up the format.
    • Recent versions of Windows don't like files being created in the root of C:\, it's better to use a subfolder.

    Regards, Hans Vogelaar

    • Marked as answer by ryguy72 Wednesday, July 18, 2012 8:32 PM
    Tuesday, July 17, 2012 7:51 PM

All replies

  • Perhaps your file is too big to process in one chunk. The following code will be much slower, but hopefully also more dependable:

    Sub Convert2()
        Dim f1 As Long
        Dim f2 As Long
        Dim strLine As String
        f1 = FreeFile
        Open "C:\CMI.csv" For Input As #f1
        f2 = FreeFile
        Open "C:\CMI_Done.csv" For Output As #f2
        Do While Not EOF(f1)
            Line Input #f1, strLine
            Print #f2, Replace(strLine, vbTab, ",")
        Loop
        Close #f1
        Close #f2
    End Sub

    Remarks:

    • If the original file contains text strings with commas, they will mess up the format.
    • Recent versions of Windows don't like files being created in the root of C:\, it's better to use a subfolder.

    Regards, Hans Vogelaar

    • Marked as answer by ryguy72 Wednesday, July 18, 2012 8:32 PM
    Tuesday, July 17, 2012 7:51 PM
  • Ryan,

    Try this function

    Public Function ConvertTabSeparatedToCSV( _
      ByVal FileSpec As String, _
      Optional ByVal BackupExtension As String = "", _
      Optional Separator As String = ",", _
      Optional UnixQuoting As Boolean = False) As Long
      
      'Converts a tab-separated (tab-delimited) text file to
      'CSV (comma-separated). Returns 0 if successful,
      'VBA error code otherwise.
      'By John Nurick, Feb 2007.
      
      'Arguments
      '  FileSpec:        Path and name of file to convert
      
      '  BackupExtension: If supplied, original file will be left
      '                   as a backup with this extension (e.g. "bak")
      
      '  Separator:       By default this is a comma. In countries where
      '                   a comma is used for the decimal point, the
      '                   field separator is usually a semicolon
      
      '  UnixQuoting:     If a field value contains a quote mark ",
      '                   it needs special handling or it will be confused
      '                   with the quote marks used to enclose values
      '                   that contain separators. In Windows, quotes need
      '                   to be ""doubled""; in some other systems they need
      '                   to be \"escaped\" with a backslash.
      
      Const QUOTE = """"
      
      Dim fso As Object 'Scripting.FileSystemObject
      Dim fIn As Object 'Scripting.TextStream
      Dim fOut As Object 'Scripting.TextStream
      Dim fFile As Object 'Scripting.File
      Dim strFolder As String
      Dim strNewFile As String
      Dim strBakFile As String
      Dim strQuote As String
      Dim strLine As String
      Dim arFields As Variant
      Dim j As Long
      
      On Error GoTo Err_ConvertTabSeparatedToCSV
      
      Set fso = CreateObject("Scripting.FileSystemObject")
      
      With fso
        'Handle relative path in Filespec
        FileSpec = .GetAbsolutePathName(FileSpec)
        strFolder = .GetParentFolderName(FileSpec)
        strNewFile = .BuildPath(strFolder, fso.GetTempName)
        'Open files
        Set fIn = .OpenTextFile(FileSpec, ForReading)
        Set fOut = .CreateTextFile(strNewFile, True)
        
        'Process lines in file
        Do While Not fIn.AtEndOfStream
          strLine = fIn.ReadLine
          'Escape any quotes
          If UnixQuoting Then
            strQuote = "\" & QUOTE
          Else
            strQuote = QUOTE & QUOTE
          End If
          strLine = Replace(strLine, QUOTE, strQuote)
          
          'if a value contains Separator or ", qualify it with quotes
          arFields = Split(strLine, vbTab)
          For j = 0 To UBound(arFields)
            If InStr(arFields(j), Separator) _
                Or InStr(arFields(j), QUOTE) Then
              arFields(j) = QUOTE & arFields(j) & QUOTE
            End If
          Next
          fOut.WriteLine Join(arFields, Separator)
        Loop
      
        fOut.Close
        fIn.Close
      
        'Rename or delete old file
        If Len(BackupExtension) > 0 Then
          strBakFile = .GetBaseName(FileSpec) _
            & IIf(Left(BackupExtension, 1) <> ".", ".", "") _
            & BackupExtension
          If .FileExists(.BuildPath(strFolder, strBakFile)) Then
            .DeleteFile .BuildPath(strFolder, strBakFile), True
          End If
          Set fFile = .GetFile(FileSpec)
          fFile.Name = strBakFile
          Set fFile = Nothing
        Else
          .DeleteFile FileSpec, True
        End If
      
        'Rename new file
        Set fFile = .GetFile(strNewFile)
        fFile.Name = .GetFileName(FileSpec)
        Set fFile = Nothing
        Set fso = Nothing
      
      End With
      'normal exit
      ConvertTabSeparatedToCSV = 0
      Exit Function
    Err_ConvertTabSeparatedToCSV:
      ConvertTabSeparatedToCSV = Err.Number
    
    End Function
    

    Tuesday, July 17, 2012 8:08 PM
  • Have you considered opening the file in Excel or Word and then saving as tab limited file.

    Wednesday, July 18, 2012 6:25 AM
    Answerer
  • Hi Ryguy72,

    Thank you for posting in the MSDN Forum.

    The picture below describes some situation mentionied in Mike7952's code. Just make it to be seen more directly. To convert tab delimited file to a comma delimted file, we should take special punctuation into consideration.

    Hope it helps.

    Best regards,
    Quist


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, July 18, 2012 9:11 AM
    Moderator
  • Thanks everyone. 

    Hans, your code converted everything to Chinese characters.  I am actually learning Chinese now; I think these characters are a little more advanced than what I have encountered so far.

    Mike, I couldn't get your code to work.  I kept getting '5'.  How do you call that Function?

    L&L, as I stated in my original post, I can't open the file, per se.  Maybe with VBA code, I can open the file, do the conversion, save the changes, and close the file.  When I open the file the usual way, some large numbers are converted to Scientific Notation.  Since the file is a CSV, I can't format this field as Text.  These are just account numbers; I'm not doing any calculations on these.

    Thanks, Quist.  That's a great example.  It totally makes sense.


    Ryan Shuell

    Wednesday, July 18, 2012 1:57 PM
  • Sub test()
      x = ConvertTabSeparatedToCSV("C:\File.csv")
      'If x returns 0 convert was successfull else returns err.number
     MsgBox x
    End Sub
    Wednesday, July 18, 2012 2:01 PM
  • Hans, your code converted everything to Chinese characters.  I am actually learning Chinese now; I think these characters are a little more advanced than what I have encountered so far.


    Ryan Shuell

    Interesting... ;-)

    That implies that the file is not a plain text file, it must have a different structure...


    Regards, Hans Vogelaar

    Wednesday, July 18, 2012 2:03 PM
  • Hummm, I got another '5', Mike.

    Hans, I'm not sure about the structure. 

    As an aside, does anyone here know if the format of the Office Discussion Groups changed recently?  It seems to be different.  I've tried Firefox and Explorer; both seem to produce the same results.  This is REALLY hard to read.  My eyesight is not good now.  I can almost FEEL it getting worse as I try to read these posts here.


    Ryan Shuell

    Wednesday, July 18, 2012 6:49 PM
  • Ryan,

     

    Can you upload your file somewhere? And Yea the forums have changed yesterday or the day before.

    Wednesday, July 18, 2012 6:56 PM
  • The minimalistic design has to do with the introduction of the public preview of Office 2013/Office 365, and indirectly with Windows 8.

    I agree that readability hasn't improved.


    Regards, Hans Vogelaar


    Wednesday, July 18, 2012 7:02 PM
  • Hans,

    Yea I downloaded a preview of Office 2013 and it boged down my computer(Windows 7), so I unistalled it. After uninstalling I went to work in VS2010 and I kept getting an Error the my Com objects are not registered or installed something like that. I have Excel 2007 installed. The code would compile but when I went to open up an Excel file my Program would BOMB! Luckly my system restore point was created the day before. Excel 2013 looks ok but it was very slow responding, maybe cause its a preview or just my computer.

    Wednesday, July 18, 2012 7:12 PM
  • I think there was a problem with the file.  When I got new data, and ran my original code, and the code Hans posted, everything worked fine.  I still didn't get your code to work, Mike.  But it's ok.  We have a couple valid solutions here.  There's just something a little funky with that file.

    Ryan Shuell

    Wednesday, July 18, 2012 8:32 PM