17

Background

I build a lot of text files in my $DAYJOB, which are then submitted to a review board in a very complicated Excel document. The contents of each text file must be pasted into a TextBox on individual Excel tabs (named appropriately). Any given Excel sheet could have between 30 and 220 different tabs in it for these text files.

What used to work

Originally I pointed a Python script at a windows folder full of text files and asked it to build a blank exel document as such; then I copied the tabs into the formatted Excel template. However, our corporate desktop security measures changed in a way that now breaks that script. All Excel documents are encrypted by DRM Security Middleware which breaks my scripted excel sheets. My pleas to stop the insanity have gone nowhere.

The Problem

Now my solution is manual copy & paste (admittedly I'm more of a unix programmer than a windows person). Clicking on a hundred text files and copying the contents into excel manually is mind-numbing, and it multiplies with every click / keystroke event required. For every file, I seem to spend over half my effort on:

  • Double-click to Open file (Events 1 & 2 - Clicks)
  • Select all with Cntla (Event 3 - Keystroke)
  • Copy to clipboard with Cntlc (Event 4 - Keystroke)
  • Paste into waiting Excel TextBox with Cntlv (Event 5 - Keystroke)
  • Close opened file, so they don't accumulate (Event 6 - Click)

I would love to find an acceptable way around this problem. I found something called Copy Text Contents, which allegedly does a two-click file-contents-copy into the clipboard; however the software is free but closed source. I can't install closed-source freeware on our desktops; however, open-source freeware solutions are acceptable.

Question

I would like an open-source solution to this problem; I'm asking for an open-source alternative to Copy Text Contents, but if someone comes up with an alternative software solution that works with excel, I'm all ears.

Note: line breaks in the text files must be preserved.

  • your request is still semi-automatic - in case you're rather into Unix (and probably PHP scripting, as well) - I'd suggest this library phpexcel.codeplex.com in order to handle OpenOffice file-formats, like XLSX. what I'm trying to tell is, that one could copy it directly into the spreadsheet instead of the clipboard (of course only makes sense, while no intelligence is required to paste into the proper cell). – Martin Zeitler May 28 '14 at 18:41
12

Here, I created an open-source application for you that should do the stuff you want.

https://bitbucket.org/mrexodia/copyfile

I accept pull-requests if you want to improve the thing.

EDIT:

Overview: CopyFile is a small program that registers a 'CopyFile' entry in the context menu of Windows Explorer. It copies the contents of an ASCII file (UNICODE files are not supported).

  • Nice! It would be even better if you bound a hotkey combo to it (to copy the file contents from the keyboad, but this is great). It's also depressingly small... I'll have to see if I can understand what you did here – Mike Pennington Mar 11 '14 at 16:36
  • Could you add more details specifically telling other users the features of this application? – aman207 Mar 11 '14 at 16:46
  • @aman207: sure, there's a readme on the repo though. Mike Pennington: binding a hotkey to a context menu item directly is not possible. That would require some nasty hooking, but the source is all yours to do with whatever you like, so go ahead and add it :P – mrexodia Mar 11 '14 at 17:55
8

Ah, I see, you want to do xsel -bi data.txt; xdotool search --class Excel key ctrl+v, only on Windows.

This is a job for a script, not an ad hoc application.

AutoHotkey is a general-purpose desktop automation scripting environment for Windows. It's open source. The scripting language combines the elegance of Basic syntax with the intuitiveness of Unix shell parsing and the regularity of TeX expansion, but it gets the job done.

Here's a script that does what I described above.

#NoEnv
; #Warn
SendMode Input

;; Read the file passed on the command line (or prompt for one)
If %0% = 0
{
    FileSelectFile, 1
    IfNotEqual, ErrorLevel, 0, Exit, 2
}
FileRead, data, %1%
IfNotEqual, ErrorLevel, 0, Exit, 2
;; Copy the content to the clipboard
clipboard = %data%

;; Locate and activate the main Excel window.
;; To reach a dialog box, you may need to change the class. Run the AutoHotKey
;; companion program Window Spy to determine the class name.
WinGet, w, ID, A
WinActivate, ahk_class XLMAIN
IfNotEqual, ErrorLevel, 0, Exit, 1
WinGet, ww, ID, A

;; Send the paste shortcut
Send ^v

;; Restore the original active window
WinActivate, ahk_id %w%

From the command line, run

autohotkey \path\to\copy-to-excel.ahk data.txt

You can add the command \path\to\autohotkey \path\to\copy-to-excel.ahk to the right-click menu in Explorer; see How can I add a program to the context menu of all files?, Change which programs Windows uses by default or How to set File associations in Windows 7 (not 'open', but others) or Customize the Windows 7 or Vista Send To Menu depending of exactly where and when you want the command to show up in the menu.

  • Gilles, thank you for the answer. You're over-achieving a bit when you added the piece to paste into Excel (I want things in a specific place inside the worksheet, and there are multiple text boxes per sheet); however, this certainly illustrates the concept very well. Thank you – Mike Pennington Mar 13 '14 at 11:33
3

I answer to this part of your question:

if someone comes up with a scripted excel alternative, I'm all ears.

AutoIt is a script language which allow you to automate the Windows's user interface, and then to by-pass your security restriction.

To cover your needs AutoIt provides basic file access function (like you did it in Python I think) to read your .txt files' content. Then with its Windows gui's automation you can write the content into your Excell file (I recommend to use key trigger instead of mouse one's) like a human would do it.

It's a programming script language, so it´s very powerful, but it could be complicated for an uninitiated public, but you seem to be confortable with programmation. Actually a macro recorder is provided, very useful for learning it.

AutoIt is well known for the large amount of game bots created with it. Personnaly I used it for automating text copy/paste and sending it throught the network.

  • Thank you for your answer; however, I'm asking for a file-contents-copy solution instead of a programming language to build the solution. If however you posted the AutoIT script to do such, that answers my question. – Mike Pennington Mar 11 '14 at 15:00
  • @MikePennington yes it´s not a ready to use solution. Actually I was answering to you last sentence : if someone comes up with a scripted excel alternative, I'm all ears. – Fractaliste Mar 11 '14 at 15:05
  • I can see the ambiguity in that sentence; however, my intention is asking for solutions, not languages to build the solution. – Mike Pennington Mar 11 '14 at 15:07
  • Okay, so it's why you didn't build it directly in VBA Excel? – Fractaliste Mar 11 '14 at 16:08
  • Is "AutoIt" open-source? – MrWhite Mar 12 '14 at 1:02
2

I use Ditto, which is an Open Source clipboard manager.

Ditto allows you to copy & paste contents into any Windows application. The things which made me select Ditto -

  • Searchable clipboard history
  • Assign hotkeys to top 10 clipboard copies, allowing you to easy paste
  • Can use without having to touch the mouse
  • Global hotkeys for paste
  • Separate list for applications which are included/excluded from clipboard operations
  • Organize text to be pasted into groups, allowing for easy organization
  • Configurable settings for number of days & amount of clipboard entries to be retained

Ditto is available as a GPLv3 licensed program.

  • Thank you for answering, I tried using Ditto before I asked this question, but I couldn't find a way to get the text files into Ditto without more keystrokes than "the manual method". Could you elaborate on how I could solve my problem with it? – Mike Pennington Mar 12 '14 at 12:15
  • @Mike you mention the Python script - the script can open the text files, and copy the contents to clipboard. The contents will be available in Ditto, and you can use the global hotkeys to paste the copied text into Excel. Ditto supports hotkeys for only 10 recent clipboard entries however, so this might be a problem – Sathyajith Bhat Mar 12 '14 at 12:21
  • Ok, Ditto's UI doesn't seem to be friendly towards the large number of files I'm dealing with, but this is a useful idea... – Mike Pennington Mar 12 '14 at 12:22
2

Windows comes with a utility called "clip.exe" which I think does exactly what you want. (Unless you're dead-set on driving things from explorer.)

Usage:

C:>clip /?

CLIP

Description: Redirects output of command line tools to the Windows clipboard. This text output can then be pasted into other programs.

Parameter List: /? Displays this help message.

Examples:

DIR | CLIP          Places a copy of the current directory
                    listing into the Windows clipboard.

CLIP < README.TXT   Places a copy of the text from readme.txt
                    on to the Windows clipboard.

Although I'm not completely familiar with your workflow, it would seem sensible to wrap this in a script which iterates over all the files you want to get into the spreadsheet with a step to wait for user input between each one.

1

You can use my clipboard manager program Clipjump to do so. It has a built-in feature to copy selected file's contents to its multiple clipboards.

All you have to do is select a file in Windows Explorer (or any other file manager) and press the shortcut. The file contents will be captured by its multiple clipboards. When you have copied all text files to clipjump, then press and hold Ctrl and use key V (Getting started) to navigate through all copied data and paste them one by one by releasing Ctrl. The current clip which is active will be remembered.

The default system-level shortcut for Copy File Path is not defined. You will to set it up in the Settings.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.