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

[Mark E Chapman]

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

[PeterJones]

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

[Alan Kilborn]

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

[Mark E Chapman]

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

[PeterJones]

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

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

[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()