Source
r/excel•
johnfbw

Save as CSV without commas

unsolved

I'm looking to save a simple Excel file as a | delimited file - because commas are in the data and the system I am exporting to doesn't take xml. I can't use the system settings in Region Settings as users don't have admin access

Posted Feb 5, 2019, 6:06 PM UTC

212
baineschile136•

save it as text

1
johnfbwOP5•

That comes out as a tab limited file with any cell with commas quoted. The system I'm importing into struggles with that format

onetwo
three,fourfive

Should be in the file (if ; delimited)

one;two

three,four;five

1
caribou16175•

Save it as a CSV, then open the file in Notepad and Find/Replace all the commas with pipes?

1
johnfbwOP5•

wouldn't work for data which has commas

1
caribou16175•

How often do you have to do this? In Excel, Find/Replace the commas to something you're confident isn't going to show up in the data, say ~. Then save as a CSV, open in Notepad, Find/Replace the comas with pipes and then Find/Replace the tildes with commas.

1
johnfbwOP5•

This is a solution for users so needs to be a little more elegant. Plus that way is fraught with potential errors

1
DeliciousSoma•

As others have said, you need to save as CSV first then do a regex/grep find and replace. To do this you need to first replace the commas in the data with some temp value such as [COMMA] then replace all of the remaining commas. You can see an example of the regex that you would need to use to find the commas in the data without touching the commas that separate the data: https://regex101.com/r/nYulbL/1

Then you can replace all of the remaining commas with a simple find and replace (find comma replace with pipe) and then do a find and replace for [COMMA] with an actual comma (find [COMMA] replace with comma)

1
johnfbwOP5•

This means i would need to 1)save as csv 2)close file 3)open stream to file 4)process as regex 5)close stream. (Or do with another programming language) It seems massive over kill for something Microsoft forgot to add

1
DeliciousSoma•

I’m really not sure what else you want, though. The answer you want to hear doesn’t exist for Excel out of the box but you have been given a number of solutions to your problem.

It seems massive over kill for something Microsoft forgot to add

I’m pretty sure Microsoft didn’t forget to add it.

2
johnfbwOP5•

A handy way to override the system default via VBA would have been nice (though that would require admin access).

I agree MS didn't forget to add it but I'm not sure their logic in not adding it. (If they are still around this subreddit - what are their user names?) It doesn't even make sense if you work with a country who uses semicommas as default

1
semicolonsemicolon924•

You can try a VBA solution. Stolen and slightly modified from here. This puts semicolons as the delimeter but you can change it to anything in the line that starts Line = Join (....

Option Explicit
Sub WriteFile()

  Dim aa As Integer, bb As Integer, ColNum As Integer
  Dim Line As String
  Dim LineValues() As Variant
  Dim OutputFileNum As Integer
  Dim PathName As String
  Dim RowNum As Integer
  Dim SheetValues() As Variant

  PathName = Application.ActiveWorkbook.Path
  OutputFileNum = FreeFile

  Open PathName & "\Test111.csv" For Output Lock Write As #OutputFileNum

  SheetValues = ActiveSheet.UsedRange.Value
  aa = UBound(SheetValues)
  bb = UBound(SheetValues, 2)
  ReDim LineValues(1 To bb)

  For RowNum = 1 To aa
    For ColNum = 1 To bb
      LineValues(ColNum) = SheetValues(RowNum, ColNum)
    Next
    Line = Join(LineValues, ";")
    Print #OutputFileNum, Line
  Next

  Close OutputFileNum

End Sub
1
johnfbwOP5•

I was worried i would have to do that. It seems overkill basically rewriting tyre save function to do a really simple task

1