I often have to insert header records into .csv files. I’ll copy a record (only column A populated) from one file, highlight the top record in another file, right-click, and choose Insert Copied Cells.
Half the time, it works. The other half of the time, I get this:
You can’t paste this here because the Copy area and paste area aren’t the same size.
Select just one cell in the paste area or an area that’s the same size, and try pasting again.
So then I have to Esc to clear the copied record, Insert a record into the target file, go back to the other file and re-copy the record, and then paste it into the first cell of the new, blank record.
Isn’t there some way to tell Excel, ‘Don’t argue. Just insert the record and make it fit!’
If you copy a single cell and then select the target row and chose “insert copied” you’ll have a problem.
If you copy a single row, then select the target cell and choose “insert copied” you’ll have a problem.
Cell copy/inserted above selected cell or row copy/inserted above row should always work. If you’re really having this fail I’m baffled.
IIRC you’re doing some of this over remote connections. It might be easy for less than perfect aim with your mouse to be having you inadvertently selecting something at one end or the other different from what you intend.
To directly answer your question, AFAIK no there isn’t a “make it fit override”. In the cases where it can make it fit it already did so. It’s just the case(s) where there’s no logical way to do it that you get the failure. Hence my suggestion that you’re not actually requesting Excel to do what you think you’re requesting it to do.
That’s how it should work. Just insert the bleedin’ record above the record I’ve selected. Half the time it works, half the time it doesn’t. AFAICT it always works with some files, and always fails on some files.
By “record” I infer that you mean row. If that is the case, instead of just selecting a cell or set of cells, just select the entire row and insert and you won’t get an error. Excel can be really obnoxious in cut and paste behavior but this one is pretty easy.
If you are having to do this or any other simple appending of text regularly, you are better off creating a macro in Excel that copies from some template or just inserts a row and fills in the appropriate cells. Or, since you specify that these are .CSV (text) files, just append a text file with the necessary header to your data file, which you can do with a one line PowerShell or Bash command (depending on whether you are on Windows or MacOS), or better yet write a simple Perl or Python program to automagically do that to a list of files you provide to it.
I don’t know how to create macros, and I don’t know Perl or Python. (I wanted to, but I need a classroom environment for that sort of thing.)
It’s actually more than one file. First I save it as .xlsx so I can use the current file as the previous file next month. I save it to one folder as a .csv file with one name for one database, a .csv file with another name for another database, and (where applicable) with an inserted database-specific header and another name for another database.
The thing is, I don’t see any logic as to why Excel won’t simply insert a record row.
Copy the headers. Not the entire row, just the populated cells.
In target spreadsheet, Rightclick in the far left to highlight Row 1; Insert (to insert a row); then click in cell A1 and paste, plain old Command-V (or Control-V).
I’m not following you. It sounds like you’re saying to insert a row, then copy the header from the other file, and paste it into cell A1. That’s what I have to do half the time. I’m wondering if there’s a way to copy a the header row from one file, and then insert it above the header row (yes, two ‘headers’) in the other file such that it always works instead of trying it, and then doing it the more time-consuming way.
FWIW, there’s no ‘insert’ where you can say Insert, and then choose where to insert it. Insert Copied Cells is the only option. If you want to insert above a row, it may or may not work. Otherwise, you have to Esc to get rid of your copied row, Insert a new row, go back and copy the row again, and then go back to cell A1 and paste the copied row.
I can’t make Excel misbehave in the way you describe but regardless if this is just inserting a line of header text above a comma-separated text file you could automate this easily without ever having to open up or deal with Excel. Here’s one quick tutorial an appending and filtering .CSV files.
I’m not sure what your end application is but it sounds like the only reason you are bringing this data into Excel at all is to add a header line and dump it back out to a set of .CSVs to be imported into different databases. If all of this is just renaming, copying, and appending text to files, ginning up a script to do this without ever having to import anything into Excel would save you a lot of time and trouble. You should be able to pay some reasonably clever high school student a few bucks to work do this and never have to fuss with Excel again.
Usually that error occurs because you are trying to insert an entire column or row at an offset from A1. You cannot insert an entire copied row starting in column B nor can you insert an entire copied column starting in row 2.
In the following images, I have x-ed out the company name, and have changed the data records to white.
In the source file, you can see that the header record is selected and copied.
In the target file, I right-clicked to get the options window. Unfortunately, MS won’t let me take a picture of it. I get the menu, and when I click on Snipping Tool it goes away. I found an image on the web, and have included it. The only option to insert is Insert Copied Cells.
Again: Sometimes I can successfully insert a row, and sometimes I get this error. The errors happen on the same files, and the successes happen on the same files.
If I’m looking at this right (and that’s a big “if”), it looks like the first picture shows you trying to copy a row, but the formula window above seems to indicate that all of your values are in cell A1, and are separated by a tab, but are NOT distributed across multiple columns the way I presume you want them to be.
If you understand what I’m saying, can you verify this?
ISTM that it would explain the error.
ETA: and IF I’m right, then I think – in cases like this one – you’re looking at a “text-to-columns” play where you only select the single cell, then copy it, paste it to the A column in the appropriate next row, and then convert the text to columns with the appropriate delimiter (tab? space?) selected in the wizard.
Only A1 is populated in the header record. The field is formatted as Text, and the elements of the header are separated by spaces. There are no delimiters in A1.
The entire row was selected and copied, including all of the blank cells (A2 through An).
I want to Insert the entire row above the row with the column headers.
Any chance that you have more than one worksheet “grouped?”
IOW, if you have multiple tabs for multiple sheets within your destination workbook, simply click on ONLY the tab for the destination worksheet that you’re aiming at.
That wouldn’t explain this being intermittent, but … worth ruling out.
ISTR, too, that Compatibility Mode can play games on this sort of thing in certain situations. Again, though, shouldn’t cause an intermittent issue like you describe.
ETA: if you’re using filters via that header row, that could possibly bang you up, too.
