Creating file from Clipboard results in incorrect Tabs and Lineshifts

Questions & Suggestions
1

Hi,

Trying to learn some more things here, and have been running into an issue with creating files from a clipboard. One of the most annoying tasks I frequently do is: copy something from Excel, paste it into TextMate, run some regex search and replace, and store the result down as a file.

The last section has been simplified a lot now, by having a KM Macro that runs all the different regex on whatever file I've got selected in Finder - which is great. But I thought I'd take the automation one step further by also including the actual creation of the file.

My end goal for the Macro will be that I select the text I want in Excel, execute the Macro which then copies the selected text, asks me for the name of the File, creates the file, runs the regex covered by the existing Macro, and voila. All good. :slight_smile:

I think I've got most of this covered, but I'm struggling with getting the content of the Clipboard to work as expected.

If I paste the old fashion way (manually) after copying from Excel, the result would look like this (with \t representing a tab and \n representing a line shift):

ColumnHeader1**\tColumnHeader2\n**
Column1Row1**\tColumn2Row1\n**
Column1Row2**\tColumn2Row2\n**

However, if I copy from Excel and run my test "Create File" Macro, which is just writing a Test File with Clipboard as content, it ends up looking like this (with \n representing a line shift):

ColumnHeader1**\n**
ColumnHeader2**\n**
Column1Row1**\n**
Column2Row1**\n**
Column1Row2**\n**
Column2Row2**\n**

So the structure is changed for some reason, with tabs seemingly being turned into line shifts. Any idea what I'm doing wrong or how I could get around this issue?

Thanks!

2

Hey @herrevjan,

Da.

You're running into an unexpected consequence of working with Excel's proprietary clipboard.

I'm a bit surprised that Keyboard Maestro's Remove-Styles filter doesn't solve this, but it's easy enough to work around.

-Chris

Microsoft Excel -- Write Clipboard to Text File.kmmacros (5.6 KB)

1 Like
3

Is there any particular reason you chose TextMate to do the RegEx?
I'd suggest BBEdit for this task, as it has an excellent RegEx engine, and when I paste a copy from Excel into BBEdit, it pastes exactly as you would expect:

  • TABs between columns
  • LFs between rows

BBEdit is also very scriptable, so it should be easy to automate your entire task.

Questions?

1 Like
4

Thanks! I found a thread about this issue after posting this, but didn't quite understand how to get around it. I tried your suggested Macro now, and it makes it much better. There's stil a bit of a difference though.

In my normal workflow, where I've manually copy/pasted, running the KM Macro I have for some regex runs among other things a replace \n with "\n". (Basically adding a quote at the end and beginning of each line in the file except the first and last where I have another search and replace... I might merge all this into one run now that I'm getting better at this maybe. :stuck_out_tongue:)

But with a file created through the setup from you, running the same regex replace results in a suddenly being in there before the quote at the end of each line. So I updated the KM-action that does the replace to search for "\r\n", at which point I get the right end result.

It is a bit funny though, because if I create a file via the KM-method, but do the regex manually either in TextMate or BBedit, it works fine just as before without the appearing in the file when I only do a \n-based search, and if I search for \r\n I don't get any results.

I guess there must be some kind of difference in some implementation of something... way beyond my skill-level to understand - but not it's at least within my skill-level to solve for my use case! :slight_smile: Thanks again for that!

The Macro with a pop-up asking for File Name and everything is in place. I'll expand on it further with some more enhanced selection of file placement etc later on, but I've gotten far enough now that it really helps.

Only accidental. :smiley: I actually looked into BBEdit after it got a splendid endorsement on the Mac Power Users podcast, but never got beyond installing a trial and opening it a few times. I did another run with it now while looking into the issue mentioned above, and I think I'll unlock it from Free mode and start looking into the scriptable things in it. Thanks for the nudge!

5

Hey @herrevjan,

I'm not seeing any oddities from the file created with either Excel 2011 or 2016, so I'm guessing this is somehow created at your end.

If you want to post an example Excel file and a macro that recreates the problem, I'll look at it.

-Chris

6

Strange. I'm running Excel for Mac via Office365.

I've attached a sample file and the resulting files from the full macros, where the only difference is that the relevant Search and Replace is running with replace \n with "\n" (the file called "Created with N") and \r\n with "\n" (the file called "Created with RN").

Excel and resulting CSVs.zip (7.6 KB)

7

Via the local application – or via the web application?

8

Sorry, should have been clear about that. The local application. The web application is horrible. :smiley:

9

Is there a RegEx scenario that BBEdit handles that TextMate wouldn't ? I'm curious as I also preferentially use TextMate, but wonder in what instances I might find BBEdit more capable ?

10

I don't know since I've never used TextMate. But I really love the BBEdit Find/Replace UI. Maybe Chris (@ccstone) would know more about this.

11

I don' t know what flavor of RegEx TextMate uses, but I'd be surprised if it's not PCRE.

If so then BBEdit and TextMate are pretty similarly equipped for search and replace.

The biggest difference is that BBEdit is hugely AppleScriptable, whereas TextMate is not.

BBEdit is my daily driver for this and other reasons.

That said, TextMate's bundle system is very powerful.

I own a copy and use it for testing on occasion.

-Chris

2 Likes
12

Hey @herrevjan,

After fooling with this for a while I realized my tools (BBEdit and Script Debugger) were not displaying CRLF's the way I expected, so I was thinking I was seeing LFs when I was not.

Further investigation shows Excel 2011 producing CRs and Excel 2016 producing CRLFs.

CRs are not shocking from 2011, because that was still fairly common for Mac apps at the time (even a decade after Mac OS X was born).

CRLFs from 2016 on the other hand are really quite unbelievable on a Mac at this stage of the game.

The easiest fix is to use a regular expression:

Search:

\R ⇢ Covers both CRs and CRLFs.

Replace:

\n

Place this after the Execute AppleScript action, and go-to-town.

-Chris

1 Like
13

Remembering that “\R” only works in 10.11+ (maybe the OP said they have a recent OSX, but having been bitten by this too many times, it is worth repeating).

2 Likes
14

Right. I verified now that \R works the same way as the \r\n version I used. And apparently, in TextMate \n also covers both CRs and CRLFs (and you don't see the difference in the files when viewing them - but the KM macro only removed the LF-part leaving the CR at which point it is visible as a separate thing in TextMate).

Thanks for the heads-up. I'm on Mojave already, so at least this part is covered.