How can I convert tab delimited files to pipe delimited?
I currently have some .txt files which are tab delimited, and I need to
convert them so that they are pipe(|) delimited. Any ideas how to do this? Excel seems to support comma, tab and space delimiting, not sure how to use pipe or tilda. |
How can I convert tab delimited files to pipe delimited?
I'd copy the text files into their own folder--just in case!
Then run a macro like: Option Explicit Sub testme01() Dim myFileNames As Variant Dim wkbk As Workbook Dim fCtr As Long Dim myCDP As DocumentProperties myFileNames = Application.GetOpenFilename("Text Files, *.Txt", _ MultiSelect:=True) If IsArray(myFileNames) = False Then Exit Sub End If For fCtr = LBound(myFileNames) To UBound(myFileNames) Call DoTheWork(myFileNames(fCtr)) Next fCtr End Sub Sub DoTheWork(myFileName As Variant) Dim FSO As Object Dim RegEx As Object Dim myFile As Object Dim myContents As String Set FSO = CreateObject("Scripting.FileSystemObject") If FSO.fileexists(myFileName) = False Then 'Do nothing Else Set myFile = FSO.OpenTextFile(myFileName, 1, False) myContents = myFile.ReadAll myFile.Close Set RegEx = CreateObject("VBScript.RegExp") With RegEx .Global = True .IgnoreCase = False .Pattern = vbTab myContents = .Replace(myContents, "|") End With Set myFile = FSO.CreateTextFile(myFileName, True) myFile.Write myContents myFile.Close End If End Sub Jeremy Town wrote: I currently have some .txt files which are tab delimited, and I need to convert them so that they are pipe(|) delimited. Any ideas how to do this? Excel seems to support comma, tab and space delimiting, not sure how to use pipe or tilda. -- Dave Peterson |
How can I convert tab delimited files to pipe delimited?
Did this solution work? Is there an easier answer? I am currently using
Office 2000 and am willing to update for this feature as a simple "save as" option. "Dave Peterson" wrote: I'd copy the text files into their own folder--just in case! Then run a macro like: Option Explicit Sub testme01() Dim myFileNames As Variant Dim wkbk As Workbook Dim fCtr As Long Dim myCDP As DocumentProperties myFileNames = Application.GetOpenFilename("Text Files, *.Txt", _ MultiSelect:=True) If IsArray(myFileNames) = False Then Exit Sub End If For fCtr = LBound(myFileNames) To UBound(myFileNames) Call DoTheWork(myFileNames(fCtr)) Next fCtr End Sub Sub DoTheWork(myFileName As Variant) Dim FSO As Object Dim RegEx As Object Dim myFile As Object Dim myContents As String Set FSO = CreateObject("Scripting.FileSystemObject") If FSO.fileexists(myFileName) = False Then 'Do nothing Else Set myFile = FSO.OpenTextFile(myFileName, 1, False) myContents = myFile.ReadAll myFile.Close Set RegEx = CreateObject("VBScript.RegExp") With RegEx .Global = True .IgnoreCase = False .Pattern = vbTab myContents = .Replace(myContents, "|") End With Set myFile = FSO.CreateTextFile(myFileName, True) myFile.Write myContents myFile.Close End If End Sub Jeremy Town wrote: I currently have some .txt files which are tab delimited, and I need to convert them so that they are pipe(|) delimited. Any ideas how to do this? Excel seems to support comma, tab and space delimiting, not sure how to use pipe or tilda. -- Dave Peterson |
All times are GMT +1. The time now is 11:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com