Community

    • Login
    • Search
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search

    How to copy columns in excel and paste into first column of text file without word wrapping

    General Discussion
    column mode
    4
    7
    2422
    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.
    • Mark E Chapman
      Mark E Chapman last edited by

      How to copy columns from excel into Note++. I have a text file for example:

      1234:text:text1
      5678:text:text2
      9087:text:text3

      I know how to select the first column of numbers. I need to be able to copy new numbers in excel column and paste into the text file. When I paste from excel, it doesn’t paste over 1234, 5678, 9087. It word wraps by pasting all three sets of numbers in each row like:

      Excel column
      5920
      3945
      4509

      5920
      3945
      4509:text:text1
      5920
      3945
      4509:text:text2
      5920
      3945
      4509:text:text3

      Thanks,

      Mark

      1 Reply Last reply Reply Quote 1
      • PeterJones
        PeterJones last edited by

        @Mark-E-Chapman said:

        How to copy columns from excel into Note++.

        The issue is that when you copy from Excel, Excel embeds the newlines in the clipboard in a way that is incompatible with column-paste on Notepad++.

        The best solution that I know of:

        1. copy from Excel, paste normally into a temporary location in Notepad++ (a tmp file, or at the end or beginning of your document, or something); after this step, it will look something like:

           1234:text:text1
           5678:text:text2
           9087:text:text3
          
           5920
           3945
           4509
          
        2. then column-copy in Notepad++, and column-paste over the column-selection you want to replace. Now it will look like

           5920:text:text1
           3945:text:text2
           4509:text:text3
          
           5920
           3945
           4509
          
        3. Delete the tmp file or the temporary paste in your real file

           5920:text:text1
           3945:text:text2
           4509:text:text3
          

        Hope this helps.

        1 Reply Last reply Reply Quote 1
        • Alan Kilborn
          Alan Kilborn last edited by

          @PeterJones said:

          embeds the newlines in the clipboard in a way that is incompatible with column-paste on Notepad++

          Just a side note, for it to be compatible, it would have to be copied (by Excel, or whatever) with a special clipboard format; here’s some code from Notepad++ 's source code that illustrates what would be needed:

          // There does not seem to be a real standard for indicating that the clipboard
          // contains a rectangular selection, so copy Developer Studio and Borland Delphi.
          cfColumnSelect = static_cast<CLIPFORMAT>(::RegisterClipboardFormat(TEXT("MSDEVColumnSelect")));
          cfBorlandIDEBlockType = static_cast<CLIPFORMAT>(::RegisterClipboardFormat(TEXT("Borland IDE Block Type")));
          
          1 Reply Last reply Reply Quote 1
          • Mark E Chapman
            Mark E Chapman last edited by

            Peter, this worked. Thanks. Alan, that is too technical for myself. I wouldn’t know where to put that code! :) Thanks though. Mark

            1 Reply Last reply Reply Quote 1
            • PeterJones
              PeterJones last edited by

              @Mark-E-Chapman said:

              Peter, this worked.

              Great news.

              Thanks. Alan, that is too technical for myself. I wouldn’t know where to put that code!

              Alan was giving a technical aside about how it’s implemented in the source code, not recommending that you try to put that anywhere.

              @Alan-Kilborn / other PythonScript gurus,

              I was able to confirm with an external clipboard-browsing tool that those two entries get set to empty content when it’s copied in column-copy mode inside Notepad++. I’m starting to research whether win32clipboard could be used to inject those two entries into the clipboard before doing the column-paste from Excel. But I might not be able to get more done on it for a few days, so if someone wants to beat me to the punch and publish here, that’d be fine with me.

              Ekopalypse 2 Replies Last reply Reply Quote 1
              • Ekopalypse
                Ekopalypse @PeterJones last edited by

                @PeterJones

                I did some research on msdn and it seems that you register a new format at process startup and then use SetClipboardData with that
                registered format to copy and call GetClipboardData, again with the registered format, to retrieve/paste it.

                So, without having any experience using clipboard object, I assume one needs to
                check if the data in the clipboard is of one of the registered formats, but at first glance I haven’t seen
                a function which would do this. Maybe it is sufficient to check if GetClipboardData returns a handle or NULL
                to see that the data is in the required format.
                You made me curious, I guess I will give it a try tomorrow to see how it actually works.

                I had some dejavu when reading that thread and found two other threads discussing similar question.
                https://notepad-plus-plus.org/community/topic/12525/replace-current-line-without-inserting-a-new-line
                https://notepad-plus-plus.org/community/topic/13085/way-to-tell-if-clipboard-has-column-block-in-it-before-pasting

                Not exactly the same but good enough to get started. :-)

                1 Reply Last reply Reply Quote 1
                • Ekopalypse
                  Ekopalypse @PeterJones last edited by Ekopalypse

                  @PeterJones

                  Looks like this works if the number of selected rows are equal to the the number of selections from the editor. Tested with LibreOffice Calc.

                  import ctypes
                  
                  def custom_paste():
                      if ctypes.windll.user32.OpenClipboard(None):
                          try:
                              clipboard_handle = ctypes.windll.user32.GetClipboardData(1)
                              if clipboard_handle:
                                  pointer_to_content = ctypes.windll.kernel32.GlobalLock(clipboard_handle)
                                  clipboard_data = ctypes.c_char_p(pointer_to_content).value
                                  ctypes.windll.kernel32.GlobalUnlock(clipboard_handle)
                              else:
                                  raise ValueError('Invalid clipboard handle')
                          except Exception as e:
                              print('Error', e.message)
                              return
                          finally:
                              # ensures that clipboard will be closed even in case of an exception
                              ctypes.windll.user32.CloseClipboard()
                  
                          # there was no exception - start replacing targets
                          lines = clipboard_data.splitlines()
                          if len(lines) == editor.getSelections():
                              for i, line in enumerate(lines):
                                  editor.setTarget(editor.getSelectionNStart(i), editor.getSelectionNEnd(i))
                                  editor.replaceTarget(line)
                      else:
                          print('Unable to open the clipboard - try again later')
                  
                  custom_paste()
                  
                  1 Reply Last reply Reply Quote 2
                  • First post
                    Last post
                  Copyright © 2014 NodeBB Forums | Contributors