Community
    • Login

    Semicolons lost in Notepad++ after editing in Excel

    Scheduled Pinned Locked Moved Help wanted · · · – – – · · ·
    8 Posts 5 Posters 833 Views
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • Mihai IlieM
      Mihai Ilie
      last edited by

      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++.
      d58207b2-7f59-49af-a5d1-19d643e8963a-image.png
      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 !

      EkopalypseE PeterJonesP 2 Replies Last reply Reply Quote 0
      • EkopalypseE
        Ekopalypse @Mihai Ilie
        last edited by

        @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.

        1 Reply Last reply Reply Quote 2
        • artie-finkelsteinA
          artie-finkelstein
          last edited by

          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.

          PeterJonesP 1 Reply Last reply Reply Quote 0
          • PeterJonesP
            PeterJones @artie-finkelstein
            last edited by

            @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.

            artie-finkelsteinA 1 Reply Last reply Reply Quote 0
            • PeterJonesP
              PeterJones @Mihai Ilie
              last edited by 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.

              1 Reply Last reply Reply Quote 0
              • artie-finkelsteinA
                artie-finkelstein @PeterJones
                last edited by

                @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>]

                1 Reply Last reply Reply Quote 0
                • Terry RT
                  Terry R
                  last edited by

                  @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

                  1 Reply Last reply Reply Quote 3
                  • Mihai IlieM
                    Mihai Ilie
                    last edited by

                    Thank you all for your answers!

                    1 Reply Last reply Reply Quote 0
                    • First post
                      Last post
                    The Community of users of the Notepad++ text editor.
                    Powered by NodeBB | Contributors