?
Solved

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

Posted on 2011-03-01
6
Medium Priority
?
1,080 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:sapgonzalez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35010761
If this is a one time shot then export as a tab delimited file, open in Word, and change all tabs to pipes.

Kevin
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 1000 total points
ID: 35010767
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
   Else
      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
   Do
      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
   Loop
   
   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
   SourceWorksheet.Copy
   
   ' 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]

Kevin
0
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 1000 total points
ID: 35010861
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
    strm.Close
End Sub

Open in new window


Sid
0
 
LVL 1

Expert Comment

by:martyk
ID: 35070441
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:

General|;@|

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.
0
 
LVL 24

Expert Comment

by:broomee9
ID: 35356956
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question