export to a pipe delimited file

millerj19565

New Member
Joined
May 8, 2003
Messages
3
Greetings,

I'm looking to export an excel sheet into a pipe delimited ASCII file (From there it needs to be FTPed to a UNIX system)

The file is a physician directory. It contains spaces and commas in the address column so using the cvs or prn file format is out. (When it is sql'ed from the UNIX file in the UNIX table the fields will be off. The sql is written for a pipe delimiter.)

How can I save or export the data so that a pipe will appear between the columns on the sheet?

For example:

Dr.|John|Smith|101 Long Park, Suite 100|Anytown|PA.|

Appreciatively -- Jeff.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
I have gotten around this before by using the following methodology:

1. Insert another sheet
2. Concatenate the first row of data from the data sheet in cell A1, with pipe symbols separating each field. (i.e. =Sheet1!A1 & "|" & Sheet1!B1 & "|" & Sheet1!C1)
3. Copy the formula down column A for all rows
4. Save this sheet as a text file (*.prn or *.txt). Since it only has one column, it should give you the data exactly as you want it.
 
Upvote 0
Or here is an automated option...

<font face=Courier New><SPAN style="color:#00007F">Type</SPAN> Info
    Prefix <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    FirstName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    LastName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    Address <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    City <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    State <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    Output <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Type</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> ExportToPipe()

<SPAN style="color:#00007F">Dim</SPAN> l <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> f <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>

<SPAN style="color:#00007F">Const</SPAN> ExportName = "C:\Pipe.txt"

l = [A65536].End(xlUp).Row
f = FreeFile

<SPAN style="color:#00007F">Open</SPAN> ExportName <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Output</SPAN> <SPAN style="color:#00007F">As</SPAN> #f

<SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> l

    <SPAN style="color:#00007F">Dim</SPAN> t <SPAN style="color:#00007F">As</SPAN> Info

    t.Prefix = Cells(i, 1)
    t.FirstName = Cells(i, 2)
    t.LastName = Cells(i, 3)
    t.Address = Cells(i, 4)
    t.City = Cells(i, 5)
    t.State = Cells(i, 6)

    <SPAN style="color:#00007F">With</SPAN> t
        .<SPAN style="color:#00007F">Output</SPAN> = .Prefix & "|" & .FirstName & "|" & .LastName & _
            "|" & .Address & "|" & .City & "|" & .State
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>

    <SPAN style="color:#00007F">Print</SPAN> #f, t.<SPAN style="color:#00007F">Output</SPAN>

<SPAN style="color:#00007F">Next</SPAN>

<SPAN style="color:#00007F">Close</SPAN> #f

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
 
Upvote 0
I tried the automated solution suggested by TommyGun, less the asterisks, and get internal error error message. Any thoughts?
 
Upvote 0
Shwalk Try this
I found this code solution at http://www.cpearson.com/excel/ImpText.aspx
Good luck

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ExportToTextFile
' This exports a sheet or range to a text file, using a
' user-defined separator character.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub ExportToTextFile(FName As String, _
Sep As String, SelectionOnly As Boolean, _
AppendData As Boolean)

Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String


Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

If SelectionOnly = True Then
With Selection
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
Else
With ActiveSheet.UsedRange
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
End If

If AppendData = True Then
Open FName For Append Access Write As #FNum
Else
Open FName For Output Access Write As #FNum
End If

For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = Chr(34) & Chr(34)
Else
CellValue = Cells(RowNdx, ColNdx).Value
End If
WholeLine = WholeLine & CellValue & Sep
Next ColNdx
WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END ExportTextFile
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Because the ExportToTextFile procedure accepts input parameters, you must call it from other VBA code, such as the following:

Sub DoTheExport()
ExportToTextFile FName:="C:\Test.txt", Sep:=";", _
SelectionOnly:=False, AppendData:=True
End Sub

In the example DoTheExport procedure above, the file name and the separator character are hard coded in to the code. If you want to prompt the user for the file name and the separator character, use code like the following:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DoTheExport
' This prompts the user for the FileName and the separtor
' character and then calls the ExportToTextFile procedure.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub DoTheExport()
Dim FileName As Variant
Dim Sep As String
FileName = Application.GetSaveAsFilename(InitialFileName:=vbNullString, FileFilter:="Text Files (*.txt),*.txt")
If FileName = False Then
''''''''''''''''''''''''''
' user cancelled, get out
''''''''''''''''''''''''''
Exit Sub
End If
Sep = Application.InputBox("Enter a separator character.", Type:=2)
If Sep = vbNullString Then
''''''''''''''''''''''''''
' user cancelled, get out
''''''''''''''''''''''''''
Exit Sub
End If
Debug.Print "FileName: " & FileName, "Separator: " & Sep
ExportToTextFile FName:=CStr(FileName), Sep:=CStr(Sep), _
SelectionOnly:=False, AppendData:=True
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END DoTheExport
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
This code will prompt the user for a text file name in which to save the file and for the separator character. If the user cancels either of these dialogs, the procedure is terminated and no export operation is carried out.
 
Upvote 0
I found the easiest solution is to download OpenOffice (which is free!!). Open the worksheet in OpenCalc and then just save as a txtcsv file...it prompts you for the delimiter to use. Open Office supports all the microsoft document formats.
 
Upvote 0
Hi Gurus,

I was following the procedure posted juracyamerico, but got stuck with building such file. I hope someone form the community could advise. I am trying to build a file that could export to comma delimited with the Pipe separator. As far as extracting the data and creating the file with the separators it works. I am confused however how I can remove "" sign from the blank cells. The results after all the steps described above are as follows:

ITEM|26|123456|1234|""|0001|""|""|""|""|""|955|3|EA
HDR|20160613|20160613|ABC|ABCINVENTORY20160613|""|""|""|""|""|""|""|""|""

I was wondering regarding 2 things:
1) from the ITEM row how to remove "" signs and leave the blank space in such form ||
2) from the HDR row how I would like to remove |""|""|""|""|""|""|""|""|"" all this and leave the row in the form of HDR|20160613|20160613|ABC|ABCINVENTORY20160613
3) the above rows are in the same position in excel so ITEM is under HDR and 26 under 20160613 etc

Please let me know, if you have any questions.

Thanks!
[TABLE="width: 936"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top