Keep quotes around empty cells when saving a csv

[Zlotz]

Hi all

I’m given a csv file in which empty cells are represented by “”.

I work on the file, save, and my file is corrupted: there are no more the “” for empty cells.

What could I do to prevent this (and keep the “”)

Kind regards

[anon73440385]

which empty cells are represented by “”

Don’t understand that - generally empty cells are two or more consecutive field separators (e.g. data1;;data3 or data1;data2,,,data5) and only if you select option [x] Merge delimiters in import dialog you may get what is not intended.

> What could I do to prevent this (and keep the "")

From my perspective there is no option in filter dialog on saving to csv, which allows quoting an empty cell by "".

[Lupp]

Most sheets currently have about 2^30 (=1073741824) empty cells, and newer versions allow experimentally for 16 times a many. You can not quote them all.
If you want to quote empty cells, you need to restrict the export to a range.
There are no means currently to do this by standard means. Also the FilterOptions evaluated by API means don’t offer a respective setting/Token for csv.
However, you can explicitly create empty text for all the empty cells yozu want to get quoted by the formula ="". These empty texts will be quoted in the csv-like file you create following the advice by @mariosv.
User code replacing the emptiness of cells by the mentioned formula in a given range (.UsedArea e.g.) is simple. Tell me if you want to get my suggestion.

[anon73440385]

for 16 times a many

~ 16² - number of cols x 16 and number of rows x ~16 (16m rows) - sorry for the nitpicking and wisenheimerism.

[Lupp]

@anon73440385: Thanks for the correction. You always should do it. Facts are facts.

[Lupp]

(I want to get this out of my mind again. Therefore I post the code I had in mind without waiting for an explicit reqest.)

Sub efficientPrepareUsedAreaOfSheetForFullyQuotedCSVexport(Optional pSheet)
If IsMissing(pSheet) Then pSheet = ThisComponent.CurrentController.ActiveSheet
cellCursor = pSheet.createCursor()
cellCursor.gotoStartOfUsedArea(False)
cellCursor.gotoEndOfUsedArea(True)
emptyRgs = cellCursor.queryEmptyCells()
For Each emptyRg In emptyRgs
  ra = emptyRg.RangeAddress
  With ra
    topRow = emptyRg.SpreadSheet.getCellRangeByPosition( _
               .StartColumn, .StartRow, .EndColumn, .StartRow)
    topLeftCell = topRow.getCellByPosition(0, 0)
  End With
  topLeftCell.Formula = "="""""
  topRow.fillAuto(1, 1)
  emptyRg.fillAuto(0, 1)
Next emptyRg
End Sub

[mariosv]

That can’t be understood as corruption, you must select the proper options when saving.

Menu/File/Save as, select csv type an mark edit filter settings.

On Export Text File, select to ‘Quote all text cells’

[anon73440385]

This does not quote empty cells (on my 7.1.1.2 LibreOffice installation)

[nm-8]

opaque is right. and most “answers” here ignore the question. Its not about all the empty cells visible, just the ones within the content (have you guys never exported to csv??? You sound like: A: “do you have a map?” B:“Having a map about everything would be too huge. you couldn’t use it.” -.- ).
excel does it right: it exports empty textcells as …,"","","",… rather than …,…
since csv is a format used for scripts and automated dataevaluation, a unified formatting for all cells (including empty ones) is necessary.
the filteroption to “quote all text cells” does nothing at empty textcells.
i currently workaround it with checking the exported csv for commas that dont have a quotesign before them. there i insert doublequotes via javascript. having an option in libreoffice, would be much more consistent of course.