Solved

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

Posted on 2011-03-01
6
1,033 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
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 250 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 250 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
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…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now