• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1089
  • Last Modified:

How can I convert Excel .xls (2003 version) into pipe delimited txt file?

I imported a pipe delimited txt file into Excel for editing and now I want to save back into a pipe delimited txt file, but this is not one of the options.  How can I accomplish?
2 Solutions
zorvek (Kevin Jones)ConsultantCommented:
If this is a one time shot then export as a tab delimited file, open in Word, and change all tabs to pipes.

zorvek (Kevin Jones)ConsultantCommented:
Here is a VBA solution.

The VBA routines below export a worksheet as a text file with a custom field delimiter. There are three routines:

ExportWorksheetWithCustomDelimiterQuery - This is a "wrapper" routine that can be called from VBA or as a macro. It queries the user for the export file path parameter and field delimiter parameter and then calls ExportWorksheetWithCustomDelimiter with those parameters using the currently active worksheet for the worksheet to be exported. This is the easiest way to export a worksheet without writing any additional VBA code.

ExportWorksheetWithCustomDelimiterDefault - This is a "wrapper" routine that can be called from VBA or as a macro. It calls ExportWorksheetWithCustomDelimiter with a default set of parameters. These default parameters can be changed as desired. Use this routine when the export file path and field delimiter are the same each time.

ExportWorksheetWithCustomDelimiter - This is the main routine that does the work. This routine can only be called from VBA code. The parameters to ExportWorksheetWithCustomDelimiter are explained in the routine.

Note that the resulting output contains the same values as formatted on the worksheet.

To implement this solution add the code below to any general code module.

[Begin Code Segment]

Public Sub ExportWorksheetWithCustomDelimiterQuery()

' Call the routine ExportWorksheetWithCustomDelimiter using a queried set of
' parameter values.

   Dim FileName As String
   Dim FilePath As String
   Dim Delimiter As String
   If InStrRev(ActiveWorkbook.Name, ".") = 0 Then
      FileName = ActiveWorkbook.Name
      FileName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1)
   End If
   FilePath = Application.GetSaveAsFilename(FileName & ".txt", "Text File (*.txt), *.txt")
   If FilePath = "False" Then Exit Sub
      Delimiter = Application.InputBox("Enter the field delimiter:", "Export Worksheet With Custom Delimiter", Type:=2)
      If Delimiter = "False" Then Exit Sub
      If Len(Delimiter) > 0 Then Exit Do
      If MsgBox("Enter a field delimiter or click Cancel.", vbOKCancel, "Export Worksheet With Custom Delimiter") = vbCancel Then Exit Sub
   ExportWorksheetWithCustomDelimiter ActiveSheet, FilePath, Delimiter

End Sub

Public Sub ExportWorksheetWithCustomDelimiterDefault()

' Call the routine ExportWorksheetWithCustomDelimiter using a predefined set of
' parameter values.

   ExportWorksheetWithCustomDelimiter "Sheet1", "C:\Full\Path\To\OutputFile.txt", "|"

End Sub

Public Sub ExportWorksheetWithCustomDelimiter( _
      ByVal SourceWorksheet As Variant, _
      ByVal FilePath As String, _
      ByVal Delimiter As String, _
      Optional ByVal PrefaceText As String, _
      Optional ByVal TrailingText As String _

' Exports the source worksheet as a text file with a custom field delimiter.
' Syntax
' ExportWorksheetWithCustomDelimiter(SourceWorksheet, FilePath, Delimiter)
' SourceWorksheet - The name of or a reference to a worksheet.
' FilePath - The full path to the export file.
' Delimiter - One or more characters to use as the field delimiter.
' PrefaceText - One or more characters to place in front of every record.
' TrailingText - One or more characters to place after every record.

   Dim DisplayAlerts As Boolean
   Dim FileNumber As Long
   Dim FileData As String
   If VarType(SourceWorksheet) = vbString Then SourceWorksheet = ActiveWorkbook.Sheets(SourceWorksheet).Name
   ' Create copy of source worksheet in new workbook
   ' Save copy as tab delimited text file and close
   DisplayAlerts = Application.DisplayAlerts
   Application.DisplayAlerts = False
   ActiveWorkbook.SaveAs Filename:=FilePath, FileFormat:=xlText
   ActiveWorkbook.Close SaveChanges:=False
   Application.DisplayAlerts = DisplayAlerts
   ' Read file into string variable and delete file
   FileNumber = FreeFile
   Open FilePath For Binary Access Read Write As FileNumber
   FileData = StrConv(InputB(LOF(FileNumber), FileNumber), vbUnicode)
   Close FileNumber
   Kill FilePath
   ' Replace all tabs with special character
   FileData = Replace(FileData, Chr(9), Delimiter)
   ' Add preface text is specified
   If Len(PrefaceText) > 0 Then
      FileData = PrefaceText & Replace(FileData, vbCrLf, vbCrLf & PrefaceText)
      FileData = Left(FileData, Len(FileData) - Len(PrefaceText))
   End If
   ' Add trailing text if specified
   If Len(TrailingText) > 0 Then
      FileData = Replace(FileData, vbCrLf, TrailingText & vbCrLf)
   End If
   ' Right modified text back out to same file
   Open FilePath For Binary Access Read Write As FileNumber
   Put FileNumber, , FileData
   Close FileNumber

End Sub

[End Code Segment]

Try this

Sub WriteFile()
    Dim oFSObj As Object, strm As Object
    Dim lngRow As Long, lngCol As Long
    Dim rng As Range
    Dim strTextFile As String, strDelimiter As String
    strDelimiter = "|"
    Set rng = ActiveSheet.UsedRange

    strTextFile = "C:\Sidz.txt"
    Set oFSObj = CreateObject("Scripting.FilesystemObject")
    Set strm = oFSObj.CreateTextFile(strTextFile, True)

    For lngRow = 1 To rng.Rows.Count
        For lngCol = 1 To rng.Columns.Count
            strm.Write Chr(34) & rng.Cells(lngRow, lngCol) & _
            Chr(34) & strDelimiter
        Next lngCol
        strm.Write vbCr
    Next lngRow
End Sub

Open in new window

A way to get closer without using VBA is to use a custom number format.

In particular, if you select all the cells to export and Format Cells...->Number->Custom and enter this:


all the cells will have the pipe symbol appended on the display regardless if they are numbers or text.

If you then save the file as .txt or .prn, the fields will be separated by pipe symbols.

The unfortunate problem is that the  fields will also be separated by tab (.txt) or multiple spaces (.prn).
If you can live with that limitation then you don't need any macro code.
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now