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



  • 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



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



  • @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")));


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



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



  • @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. :-)



  • @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()
    

Log in to reply