Excel 2003 - saving/exporting comma delimited text to pipe delimited

[wurzel]

I've googled this & the only solution I can find is to change my regional settings to the pipe symbol, export & then change back to comma again. I need some users to be able to produce these files on a regular basis so this rather flakey solution isn't acceptable.

Anyone know of any other ways of doing this? I'm not that au fait with Excel but I should have thought that this functionality was pretty basic.

[TheFaQQer]

That's the only way that Excel will do it, it seems.

You could do an export with commas and then run it through a script or macro - have a look here for an example.

[Scrag Meister]

I've googled this & the only solution I can find is to change my regional settings to the pipe symbol, export & then change back to comma again. I need some users to be able to produce these files on a regular basis so this rather flakey solution isn't acceptable.

Anyone know of any other ways of doing this? I'm not that au fait with Excel but I should have thought that this functionality was pretty basic.

You could always stick a little bit of VBA behind the scenes.

You can do whatever you want then.

[Sysman]

On locked down work PC I can't get into the settings to change this. Not great when someone has set it to semicolon and a load of the Server 2008 goodies want comma separators...

I asked this question before but didn't get an answer:

In Office 1997, there was an extras pack on the CD which added goodies for Excel import and export. You had to discover this and install it manually after the rest of Office.

Does a similar thing exist on more modern Office distributions?

[wurzel]

That's the only way that Excel will do it, it seems.

You could do an export with commas and then run it through a script or macro - have a look here for an example.

Cheers. I went with this solution.

Just had to modify ActiveSheet.Paste to ActiveWorkbook.ActiveSheet.Paste as the active sheet was still the source worksheet despite the active workbook being the new created workbook. Also added some code to delete the added column from the source worksheet & wired it u-p to the workbook's before save event:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim originalWorkbook As Excel.Workbook
Set originalWorkbook = ActiveWorkbook
FName = ActiveWorkbook.Name
If Right(FName, 4) = ".xls" Then
FName = Mid(FName, 1, Len(FName) - 4)
End If

Columns(1).Insert Shift:=xlToRight

For i = 1 To Range("B5000").End(xlUp).Row
TempString = ""
For j = 2 To Range("HA1").End(xlToLeft).Column
If j <> Range("HA1").End(xlToLeft).Column Then
TempString = TempString & _
Cells(i, j).Value & ";"
Else
TempString = TempString & _
Cells(i, j).Value
End If
Next
Cells(i, 1).Value = TempString
Next

Columns(1).Select
Selection.Copy
Workbooks.Add
Range("A1").Select
ActiveWorkbook.ActiveSheet.Paste
Application.CutCopyMode = False
originalWorkbook.Worksheets("score").Columns(1).De lete
ActiveWorkbook.SaveAs Filename:=FName & ".txt", _
FileFormat:=xlPrinter

End Sub

[stek]

If you have access to a Unix box or Cygwin installed try;

sed 's/|/,/g' oldfile.xls >newfile.xls

[wurzel]

If you have access to a Unix box or Cygwin installed try;

sed 's/|/,/g' oldfile.xls >newfile.xls

Would that replace the commas in the data as well as the delimiters? That's the reason I'm having to use something other than csv unfortunately.

[TheFaQQer]

If you have access to a Unix box or Cygwin installed try;

sed 's/|/,/g' oldfile.xls >newfile.xls

Is that smart enough to spot that you shouldn't replace the comma in "Smith, Jones & Co."?

I considered something like that as a suggestion, but the macro seems to do it fine.

[stek]

Would that replace the commas in the data as well as the delimiters? That's the reason I'm having to use something other than csv unfortunately.

Would replace the pipe with a comma (I think I misunderstood which way we were going) but yes - it would replace all of them, assuming the spreadsheet is saved off in a text format.