Semicolons lost in Notepad++ after editing in Excel

Mihai Ilie

Hello,

I’m new to Notepad++ and if I sound like a noob it’s because I am.
I have this csv file (Unix format and ANSI encoding) that has semicolons at the end of the lines when I open it with Notepad++.

I open the file in Excel to do some editing, save the changes and then I open it again in Notepad++ to change the format (Windows to Unix) but the semicolons at the end of the lines are gone. Is there any way to avoid that?

I know I can add the semicolons with Replace - Find what $ - Replace with ; but let’s say I forget to open the file in Notepad++ first and I edit it directly in Excel, I might not even know that there were semicolons or how many at the end of the lines.

Thank you !

Ekopalypse

@Mihai-Ilie

I don’t see how Npp can make sure that Excel does not remove
these trailing semicolons and is more a question for an Excel forum anyway.

artie-finkelstein

I always thought the c in csv stood for comma. You might try enclosing the contents of each text cell in double quotes (‘"’, 0x22) characters while it’s open in Notepad++. Based on your picture that could be:

"F;TEST;TEST;;;;2;;;;01/01/2000;TEST;;;;"

if each line is one cell or

"F",";","TEST",";","TEST",";",";",";",";"2";",";",";",";"01/01/2000";","TEST",";",";",";",";"

if each non-numeric item is a separate cell, or any other variation of merged text cells.
But mostly, as @Ekopalypse responded, this is an Excel question.

PeterJones

@artie-finkelstein said in Semicolons lost in Notepad++ after editing in Excel:

I always thought the c in csv stood for comma.

It may have started as “comma”, but it is more accurately used as “character” – tabs and semicolons are frequent characters for the separators.

PeterJones

@Mihai-Ilie said in Semicolons lost in Notepad++ after editing in Excel:

I open the file in Excel to do some editing, save the changes and then I open it again in Notepad++ to change the format (Windows to Unix) but the semicolons at the end of the lines are gone. Is there any way to avoid that?

In what situation does it matter whether or not those extra semicolons are there? Those semicolons are obviously the separator character in your CSV, so they are indicating that you have a cell with TEST followed by 4 empty cells in each row. From a spreadsheet perpsective, there is no difference between an empty cell and a cell beyond the right edge of your last piece of data.

After it’s saved again without semicolons, from an Excel/spreadsheet perspective, it’s identical data. If you have another application that does for some poorly-coded reason think there’s a difference between ;TEST;;;; and ;TEST, then the only way to prevent Excel from getting rid of those semicolons is to have something in the farthest-right column (maybe define a header row with headers for all columns). (But, as others have said, that is an Excel question.)

let’s say I forget to open the file in Notepad++ first and I edit it directly in Excel, I might not even know that there were semicolons or how many at the end of the lines.

And that guarantees it’s an Excel question; if you take Notepad++ out of the loop, there is no way for us to give you a Notepad++ answer that would prevent it from happening.

artie-finkelstein

@PeterJones
A good backronym.
I started w/ CSV, then TSV was an accepted variation, I never noticed when semicolons were accepted. But, perhaps now it should be known as PSV (as in poorly separated value), given how incorrectly many commercial program export functions perform. [It at least keeps us humans employed fixing up the leaky joints in the computer program to computer program pipeline. <smile>]

Terry R

@artie-finkelstein said in Semicolons lost in Notepad++ after editing in Excel:

I started w/ CSV, then TSV was an accepted variation,

In my experience a CSV file can actually have any specified character as the delimiter, preferably a character that isn’t used in the data or at least infrequently. The tab, semi-comma and space are fairly common ones.

I looked here to confirm my suspicions. CSV is not a standardized file format. Further bolstering that we see that Excel will happily accept almost any character as a delimiter.

Terry

Mihai Ilie

Thank you all for your answers!