This isn’t advanced. A wizard should be able to handle this, should it not?
What are you ultimately trying to do? Calc uses " to indicate the start and end of a string, so if you’re intending to search for that character, you’ll have to search for CHAR(34), instead.
If you’re intending to search for a comma followed by a backslash, then I’m not sure how you actually arrived at the problem in the photo. When I typed “,” into the formula (either directly in the cell or in the wizard, here), it took it without issue.
I don’t know where you are getting comma from?
Since I want to export my strings to a CSV file which also uses quotes to indicate the same, I need to do what is called “to escape” them, and that’s done by inserting a backslash in front of every quote.
And when I try what you tell me, the error changes from 511 to 508:
I’ve also seen error 509 when I try to add an extra escape using another backslash…
EDIT: I see now that both a single quote and encapsulating double quotes can be used to escape characters and strings in Libre Office, but when I try, I still always get error 508 or 509…
Are we 100% sure we don’t just want to be saving using the CSV filter? Just in case, here is a workhorse that I use daily for saving all sheets as CSVs in the ODS directory. It has passed through many hands before getting to me, so I cannot credit it appropriately.
Sub ExportToCsv
Dim Document as Object
Dim FileDirectory as String
Dim Sheets as Object
Dim index as Integer
Dim Filename as String
Dim FileURL as String
Document = ThisComponent
' Use the global string tools library to generate a path to save each CSV
GlobalScope.BasicLibraries.loadLibrary("Tools")
FileDirectory = Tools.Strings.DirectoryNameoutofPath(Document.getURL(), "/") & "/classync/"
'MsgBox "Saving CSV files to: " & FileDirectory
' Work out number of sheets for looping over them later.
Sheets = Document.Sheets
' Set up a propval object to store the filter properties
Dim Propval(1) as New com.sun.star.beans.PropertyValue
Propval(0).Name = "FilterName"
Propval(0).Value = "Text - txt - csv (StarCalc)"
Propval(1).Name = "FilterOptions"
Propval(1).Value ="44,34,0,1,1" 'ASCII 59 = ; 34 = " 44 = ,
For index = 0 to Sheets.Count - 1
' For each sheet, assemble a filename and save using the filter
If True Then 'Conditions to exclude/include various sheets by name, etc.
Document.getCurrentController.setActiveSheet(Sheets(index))
Filename = FileDirectory & Sheets(index).Name & ".csv"
FileURL = convertToURL(Filename)
Document.StoreToURL(FileURL, Propval())
End If
Next index
End Sub
1 Like
This is also interesting! Thank you, I will try this as well.
One can also use an escaped literal double quote in a literal string. In formula context, double quote characters in literal strings are escaped by doubling them. So: """" (4 double quotes) is a literal string containing one double quote.
See Text Functions .
Wrong. In CSV, double quotes are escaped by doubling them and surrounding the field content with double quotes. Exporting from Calc to CSV does that automatically.
See RFC 4180 - Common Format and MIME Type for Comma-Separated Values (CSV) Files .
Regarding your attempt to type \CHAR(34), it failed because that’s not proper syntax. If you want two strings to concatenate into a single one, you need to use either the CONCATENATE function or the concatenation operator (&).
Put another way: If you need the two-character string \" then one way to get that into the replace field would be “\”&CHAR(34). Another would be CONCATENATE("\",CHAR(34))
Hilarious (to me) sidenote: I didn’t notice in my initial post that Ask.LO was somewhat treating my back slashes as escape characters and removing them from the initial post. That’s why there was just a random comma in the quotations. I was trying to replicate what you had in your initial formula, and got the string “,\”. When I typed that the first time, Ask.LO formatted it as “,”
That is one of the reasons why code, including spreadsheet formulas, should be formatted as code. Either inline for small examples by enclosing it in single ` backticks, or for a more detailed formula or multiline code three consecutive backticks alone on a row before and after the code.
See also This is the guide - How to use the Ask site? - #6 by erAck .
1 Like
That did not help. I’ve already turned off the auto correct for quotes, so I know what I’m searching for.
My trouble is that none of the escape instructions that I found works, including the four quotes in a row.
Why do you upload pictures instead of a document? In row #1 of my document I demonstrated how to substitute literal double quotes and it works.
I believe that OP doesn’t intend to replace double quotes, but a sequence of two characters: comma and backslash ,\.
@eobet1: FTR: I can’t repro with Version: 7.3.3.1 (x64) / LibreOffice Community
Build ID: 1688991ca59a3ca1c74bc2176b274fba1b034928
CPU threads: 12; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win
Locale: en-US (ru_RU); UI: en-US
Calc: CL
no matter if wildcards, regular expressions, or none are enabled in formulas.
Seems I’m terrible at explaining, so I suspect there’s a language barrier (English is not my native language).
EscapedQuotesToCSV.ods (14.2 KB)
Ok, I think I get where the confusion arises from now… it’s that CSV are comma separated, but I’m trying to escape the quotations. Yes, ok, that’s contradictory, but the program I’m trying to read the CSV file still needs this. But I’ll take any general solution on how to escape any character in formulas, because everything I’ve tried so far doesn’t work.
Ooh, now I understand what the documentation meant with “four in a row”. Ok, thank you very much, it finally clicked!