2

When I open my CSV in excel, LibreOffice Calc, or Google Sheets, the character limit per cell is exceeded and the data in one cell spills over into the neighboring cells. How can I prevent this, so that the file maintains its original structure? Alternatively, is there another way I can edit this file that avoids this issue? Or if not, is there a way to cut off anything that exceeds the cell character limit so that I can use the spreadsheet properly?

user17038038
  • 126
  • 7
  • What other tools do you have access to? If you only have spreadsheets you could write something in Excel's VBA to read the file manually line by line. But if you have other tools like command line utilities or a text editor there may be a better answer. – Jerry Jeremiah Oct 08 '21 at 02:19
  • @JerryJeremiah Hi Jerry, I have Sublime Text. I don't know if that is relevant. Do you know if there is any easy way for me to just trim any cell which went over the limit so that I can still use the spreadsheet? Thank you – user17038038 Oct 08 '21 at 03:11
  • What do you mean by "spill over"? Do you mean that content from cell 1 is placed in cell 2, or is it just a matter of displaying content (cell content remains in cell, but is displayed over adjacent cells)? LO Calc allows for 32767 characters per cell - do you reach this limit? – tohuwawohu Oct 08 '21 at 06:32
  • @user17038038 If SublimeText allows search and replace with regex you could try searching for `(?<=^|,)([^,]{1,10})[^,]*(?=,|$)` (changing 10 to whatever the maximum a cell can hold) and replacing it with `$1` According to https://stackoverflow.com/questions/11819886/regular-expression-search-replace-in-sublime-text-2 SublimeText uses `$1` for the backreference - if that's not true you should try `\1` instead. Here is an example with keeping 10 characters https://regex101.com/r/ukFdlZ/1 Let me know if that works for you or not - it may not work if SublimeText doesn't understand lookarounds. – Jerry Jeremiah Oct 08 '21 at 09:09
  • Is SublimeText doesn't understand lookarounds you could try searching for `([^,]{1,10})[^,]*` and replacing with `$1` Here is an example without lookarounds that seems to still work https://regex101.com/r/v5kFKo/1 so that may be the one to use. – Jerry Jeremiah Oct 08 '21 at 09:11
  • 1
    @tohuwawohu, Small clarification. In fact - twice as much (SAL_MAX_UINT16 = 0xFFFF = 65535) – JohnSUN Oct 08 '21 at 12:25
  • I have over 200,000 characters in some of the cells and excel is placing the extra data from those cells into neighboring cells instead of trimming the excess. – user17038038 Oct 09 '21 at 03:38
  • @JerryJeremiah Hi Jerry, I tried this in Sublime Text. The first method didn't produce any result. The second method did do something but it seems to have split the data up into even more cells. It's alright though, I'm looking into other solutions, starting from scratch. Thank you. – user17038038 Oct 09 '21 at 04:05
  • I don't see how it could split it into more columns since the replacement doesn't contain commas. Sorry it didn't help. – Jerry Jeremiah Oct 10 '21 at 20:32

1 Answers1

1

Its a limitation/issue with the program(s). Open the CSV in notepad and make the edit

Bubbles2000
  • 11
  • 2
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 08 '21 at 03:24
  • Notepad?!! If the OP is using Windows, s/he should use at least Notepad++. – Rodrigo Sep 27 '22 at 01:46