Pipe "|" Delimiter CSV?

stildawn · Sep 15th 2016

Hi All

Got a really tricky one... Can a excel sheet be saved as CSV but with Pipe "|" delimiter instead of ","?

I know there is a way to change windows wide delimiters from "," to "|", but that is not ideal, is there a way for excel VBA code to do it?

Or alternatively, VBA code that changes the windows value, saves a CSV, and then changes back the windows value to a ","?

Thanks in advance.

Infomage · Sep 15th 2016

Re: Pipe "|" Delimiter CSV?

Would it be easier to save the CSV file, then load it into notepad and perform a global search and replace?

**cytop** · Sep 15th 2016

Re: Pipe "|" Delimiter CSV?

You can't 'natively' save a file using delimiters other than a comma, tab or space.

You can change your computer settings to use '|' as the text delimiter.

Access can be used to export a table (as a linked table) using the '|' as the delimiter, although you may not have a copy of Access to hand. You can use ADO/DAO to open an Excel file as a database and export that but that means more code and more complication and the worksheet must be a simple list like a database.

As infomage said (posts overlapped), you can save as a 'standard' CSV and edit the file. This can also be automated - again more code and more complication.

The only other alternative is to use VBA to write to a file using '|' as the field delimiter

Code

Sub ExportPipe()


   Dim UsedRows      As Long
   Dim UsedColumns   As Long
   
   Dim i As Long, j As Long
   
   '// Define a suitable file name
   Open "C:\temp\pipedexport.txt" For Output As #1


   With ActiveSheet
   
      UsedRows = .UsedRange.Rows.Count
      UsedColumns = .UsedRange.Columns.Count


      For i = 1 To UsedRows
         For j = 1 To UsedColumns - 1
            Print #1, .Cells(i, j); "|";
         Next j
         Print #1, .Cells(i, UsedColumns)
      Next i
   End With
   
   Close #1
   MsgBox "Finished...", vbInformation


End Sub

Display More

(Quick & Dirty)

Infomage · Sep 15th 2016

Re: Pipe "|" Delimiter CSV?

Beautiful, cytop!

**cytop** · Sep 15th 2016

Re: Pipe "|" Delimiter CSV?

Hardly... I labeled it 'Quick & Dirty' but Thank you.

I will make 1 change. The lines

Code

For j = 1 To UsedColumns - 1 
                Print #1, .Cells(i, j); "|"; 
            Next j 
            Print #1, .Cells(i, UsedColumns)

Writes the values of the cells, but you can use

Code

For j = 1 To UsedColumns - 1 
                Print #1, .Cells(i, j).Text; "|"; 
            Next j 
            Print #1, .Cells(i, UsedColumns).Text

to write the formatted (as displayed) values rather than the underlying values.

Infomage · Sep 15th 2016

Re: Pipe "|" Delimiter CSV?

Quick and dirty it may be, but it is a technique that I wouldn't have thought of, hence my compliment.

**cytop** · Sep 15th 2016

Re: Pipe "|" Delimiter CSV?

Thank you again, then.

Just to finish off your post, a generic procedure to replace all occurrences of 1 string with another in a file.

Instead of just replacing delimiters it can also be used to replace all occurrences of 'Ford' (for example) with 'Chrysler' - although 'Chrysler Mustang' does not roll off the tongue too easily!

Code

Sub ReplaceCharsInFile(strFileName As String, _
                       strChar1 As String, _
                       strChar2 As String, _
                       Optional BackUp As Boolean = True)




   Dim iFile            As Integer
   Dim FileContents     As String
   Dim iCounter         As Long


   If Dir(strFileName) = vbnullstirng Then
      MsgBox "The file " & UCase(strFileName) & " does not exist...", vbExclamation
      Exit Sub
   End If


   iFile = FreeFile
   Open strFileName For Input As #iFile


   FileContents = Replace(Input(LOF(iFile), #iFile), strChar1, strChar2)
   Close iFile


   If BackUp Then


      Do While Dir(strFileName & "." & Format(iCounter, "000") & ".OLD") <> vbNullString
         iCounter = iCounter + 1
      Loop
      If iCounter < 100 Then
         Name strFileName As strFileName & "." & Format(iCounter, "000") & ".OLD"
      End If


   End If


   Open strFileName For Output As #iFile
   Write #iFile, FileContents
   Close #iFile


End Sub

Display More

Optionally, it creates a sequentially numbered backup of the original file.

**Kenneth Hobson** · Sep 15th 2016

Re: Pipe "|" Delimiter CSV?

Chip Pearson's code is somewhat similar to cytop's but adds an append option. http://www.cpearson.com/excel/ImpText.aspx

Seems like I used the method in post #1 in the past.

Of course cytop, is correct, a pipe-delimited file<>CSV file.

stildawn · Sep 15th 2016

Re: Pipe "|" Delimiter CSV?

Quote from cytop;777439
You can't 'natively' save a file using delimiters other than a comma, tab or space.

You can change your computer settings to use '|' as the text delimiter.

Access can be used to export a table (as a linked table) using the '|' as the delimiter, although you may not have a copy of Access to hand. You can use ADO/DAO to open an Excel file as a database and export that but that means more code and more complication and the worksheet must be a simple list like a database.

As infomage said (posts overlapped), you can save as a 'standard' CSV and edit the file. This can also be automated - again more code and more complication.

The only other alternative is to use VBA to write to a file using '|' as the field delimiter
Code
Sub ExportPipe()


   Dim UsedRows      As Long
   Dim UsedColumns   As Long
   
   Dim i As Long, j As Long
   
   '// Define a suitable file name
   Open "C:\temp\pipedexport.txt" For Output As #1


   With ActiveSheet
   
      UsedRows = .UsedRange.Rows.Count
      UsedColumns = .UsedRange.Columns.Count


      For i = 1 To UsedRows
         For j = 1 To UsedColumns - 1
            Print #1, .Cells(i, j); "|";
         Next j
         Print #1, .Cells(i, UsedColumns)
      Next i
   End With
   
   Close #1
   MsgBox "Finished...", vbInformation


End Sub
Display More
(Quick & Dirty)
Display More

Thanks Cytop, this looks the best solution.

Can you run me through the code so I can best implement it into my code:

Code

'// Define a suitable file name
   Open "C:\temp\pipedexport.txt" For Output As #1

They need it in a ".csv" file, can I edit this file name code to generate dynamically something like:

Code

Open ScoutSavePathStr & "\Scout Upload " & RefNoStr & " - " & CurrentDateStr & ".csv" For Output As #1

Or do I need to actually generate this file before the above? Basically currently I have all the data in a sheet called "Scout" (which is the system its uploading to), need to get all the data in that sheet into a piped CSV file.

Code

For i = 1 To UsedRows
         For j = 1 To UsedColumns - 1
            Print #1, .Cells(i, j); "|";
         Next j
         Print #1, .Cells(i, UsedColumns)
      Next i
   End With

From what I understand, this loop is simply going through all used cells in the sheet and printing the contents + "|" into the text file (the output file). Will this work as is with a csv file?

EDIT: Just re-reading the instructions from the Scout people (who are making this a pain in the ass lol), here's there vague instructions:

Quote

1. Fill in the data in one cell, use | as delimiter and remember to enter ~ as Line End Indicator of each line.
2. Save the file as Comma delimited CSV
3. Send email to &amp;quot;[email protected]&amp;quot;domain.com with the CSV as attachment, Subject: Reference

Couldn't the above code work straight in excel without the print to output? Just do the above in A1?

Thanks

**cytop** · Sep 16th 2016

Re: Pipe "|" Delimiter CSV?

Quote

can I edit this file name code to generate dynamically something like...

Yes, I just used a simple fixed file name as an example. Just be aware that any existing file with the same name will be truncated (effectively overwritten, without warning) as soon as you open the file.

Quote

Will this work as is with a csv file

No problem, you just name the file with any extension you want (Even .XLSM). when writing to a file, the extension you use doesn't really matter as long as the program reading the file can understand the structure.

Quote

this loop is simply going through all used cells in the sheet and printing the contents + "|" into the text file

Yep - nothing complicated there.

Quote

remember to enter ~ as Line End Indicator of each line

Code

For i = 1 To UsedRows 
    For j = 1 To UsedColumns - 1 
        Print #1, .Cells(i, j); "|"; 
    Next j 
    Print #1, .Cells(i, UsedColumns) & "~"
Next i

One thing not clear is if a New Line character is added after that. If it is then no problem but if the other program interprets '~' as the end of the line then it will need a ";" as well

Code

Print #1, .Cells(i, UsedColumns) & "~";

Note the semicolon is outside the double quotes.

You can"Fill in the data in one cell" if you wish and saving that as a CSV file will work as there's only one cell to export so no field delimiters (',') are needed. Getting all the information into 1 cell, adding the pipe delimiters and the ~ line terminator would be a pain, though.

stildawn · Sep 16th 2016

Re: Pipe "|" Delimiter CSV?

Quote

No problem, you just name the file with any extension you want (Even .XLSM). when writing to a file, the extension you use doesn't really matter as long as the program reading the file can understand the structure.

So would I not need to create this "csv" file first? Would simply having it as the "open "blah.csv" For output as #1" work?

The "~" is already in the last column of the work sheet, so I believe your code will automatically include it correctly.

Could you explain why doing it within excel (into A1) would be a pain? Wouldn't it just be something like:

Code

For j = 1 to UsedColumns - 1
.Range("A1").Value = .Range("A1").Value & .Cells(i,j).Text & "|"
Next j

Wouldn't that work? Or is there something I'm missing.

**cytop** · Sep 16th 2016

Re: Pipe "|" Delimiter CSV?

Opening a file 'For Output' will create the file if it doesn't exist, or truncate any existing file.

Quote

Wouldn't that work...

Yes. You can do that if you wish, but there is no particular advantage doing it that way.

Quote

The "~" is already in the last column

Just covering all bases. You suddenly mention something that is non-standard for a CSV file so tried to cover that. If the character is already in the cells then it will be included.

Infomage · Sep 16th 2016

Re: Pipe "|" Delimiter CSV?

Looks like the requirements are broken...

I wonder if they are seeking a format similar to:

Val1|Val2|Val3~,Val4|Val5|Val6~,Val7|Val8|Val9~

to represent:

Val1 Val2 Val3
Val4 Val5 Val6
Val7 Val8 Val9

I recommend seeking clarity though - perhaps worth asking whether that have a sample file?

stildawn · Sep 17th 2016

Re: Pipe "|" Delimiter CSV?

Quote from cytop;777504

Opening a file 'For Output' will create the file if it doesn't exist, or truncate any existing file.

Yes. You can do that if you wish, but there is no particular advantage doing it that way.

Just covering all bases. You suddenly mention something that is non-standard for a CSV file so tried to cover that. If the character is already in the cells then it will be included.

Display More

I just coded it to do it within excel, but the loop is fairly slow, takes a minute or two on big files, will try your print to file way as well and see which is quicker.

Pipe "|" Delimiter CSV?

Participate now!

Share