Ok, I can use either Numbers or Excel (both on a Mac, I don't have office for Windows). In a pinch I can also dump the content into an SQL database and then use PHPMySQL to accomplish what I'm looking for. I can even export this whole thing as CSV and then use a script to manipulate the text file. Whatever is easiest for whomever can think of a solution. First I have to apologize because this isn't going to be as interesting or complex as Doug's Excel kung-fu.


I have multiple rows that I've currently got sorted by a numerical (integer) column called "ID" - the current ID numbers start at 0 and end around 248, however there are many missing.

I want to add rows corresponding to the missing IDs and fill in those cells with the missing ID numbers. I don't care whether the rows are inserted into the existing table or simply added at the end. A sort on the ID column can put everything in order after all this is taken care of.

Example:



Given the above example, I'd like to automatically create rows with IDs 0-1, 3-7 and 9-12. The content of the other cells for the new rows is unimportant (should be blank).

Darn, join(1) so nearly does this (for CSV files), but it won't do numeric sort order. If you can arrange to zero-pad the IDs, then that wouldn't be a problem:
'cos then you could:
and you'd get:
which is fairly nearly right...

Peter

Oh, sorry, they are zero-padded - or rather can be. I should have mentioned that.

What kind of operations are those that you've described? Excel?

Unix shell commands (specifically, GNU Coreutils as found on Linux; MacOS equivalents might not work the same).

Peter

Ok, that's better than Excel, as I have Ubuntu running in a vm if I need it.

So, the joining/merging gave me an idea to speed up another part of this puzzle.

Does the first command generate a CSV with row IDs from 0 to 13? And the join replaces existing rows in that file with the numbered rows from the source file?

If so, then that definitely going to be the big time saver. I don't need to use the command line to generate the original CSV as I've actually already got another one I can use for this, with IDs from 0 to 255.

If I can join/merge the second csv into this one then that will be perfect. Replacing any existing lines in the target with the corresponding ones from the source.

Yes, that should work. The first command makes what's effectively a "degenerate" CSV file, i.e. just one field, so no commas. The second command, join, is effectively the same as the SQL feature of the same name: it produces lines where fields match in the two files. The "-1 1" makes it match on the first (and only) field in the first file, the 0-13 CSV; the "-2 3" makes it match on the third field in the second file, the codes CSV. The "-a1" makes it a left join, i.e. lines from the 0-13 CSV still get printed even when there's no match in the codes CSV.

Peter

I'm now starting with both files having the exact same number of columns - I'll remove the column headers in the working files if it matters. I can move columns around at will before starting the join if it's preferable.



So, would it be "-2 1" for the first field in the second file? And does it matter that the first file has more than just the one field per row?

Here are the results...

Yes, and no respectively.


Oh dear, what does "join --version" say? Mine is 8.15. If you miss out the "-o auto" you'll get a less helpful (but probably still usable) output.

Peter

Join version 8.5 - and I didn't really see any useful information in the man file explaining any different formats for the -o option.

Omitting the "-o auto" gets rid of the error, but produces a file that's not so helpful.

All the lines from the "all" file are still there in the output (which I redirected to a new file). None have been replaced by the corresponding matches from the source file and they have all had a space prepended to them.

The lines from the source appear in the file without the first field, so they start with a comma, and are placed immediately after the lines they should have replaced.

I can probably make a macro in TextPad (in Windows) to correct this.

Done. Thanks for the help Peter.

This is what the lookup functions are for.

Starting with the existing table.
Create another table, empty except for the first I'd "000"
Fill in the other values for 000 using lookup functions that reference the original table.
Use the fill down drag handle to populate the entire table.
There will be errors where the original table is missing id's, unless the formulas employ error trapping.
Convert the new table to static text via copy> paste special as values.

Glenn, you may as well have been describing a procedure for heart or brain surgery.

My knowledge of spreadsheet intricacies is somewhere below novice.

Whoa! You are making this a lot more complicated than it need be. Well, I should temper that by saying it is pretty easy IF you are more than passingly familiar with Excel. Here is how I'd do it.

1) Start with an Excel spreadsheet that is formatted like the sample you posted.

2) Go to the end of column C, and type in "1", then in the next row "2". Note that since the previous cells were zero-filled, the new ones you typed automatically zero-filled to three places.

3) Highlight the two cells you just typed, then put your cursor on the bottom right corner of the cell with the "002". The cursor will become a plus sign. Left-click and drag downwards and column "C" will fill with incremental numbers, from 003 to however far down you want to drag.

4) Now highlight the whole spreadsheet and click Data --> Sort. Choose "ID" for your sort key.

5) Highlight column "C" by clicking on the "C" column identifier at the top of the column.

6) Click Data --> Filter --> Advanced Filter; select "Filter the List in-place" and "Unique records only." Click "OK".

It looks complicated with 6 steps, but each step is pretty self-explanatory and takes just a moment to execute, and the process has the advantage of being self-contained all within Excel. After replicating your four-row sample, I ended up with the attached in less than a minute. Is that what you were after?

One important caveat: These instructions work with an old version of Excel (Excel 2002, if you can believe it) and newer versions (after, I think about Excel 2007) will have their menu structure completely different from what I have described.

Another curious thing... after you do the Filter in place step, the series fill thing (where you dragged the plus-sign cursor down and it filled in the cells incrementally) will no longer work. A bug in Excel?

tanstaafl.

The unique values op, just hides various rows. Fill doesn't work across the skips.

I thought about suggesting a fill sort filter unique values - but could not figure how to make sure the original data was chosen over the new rows. Is the choice of unique records a first found first chosen kind of operation.

Yeah, that thought occurred to me as I was falling asleep last night. I hadn't noticed the missing row numbers when I looked at the result. That makes a real ugliness in the result, doesn't it?

It's fixable by copying the spreadsheet into the clipboard, then doing a "Paste Special" to a new location and deleting the original columns. Pretty kludgy, though.

Yes, because the filter criteria is column "C" only. Since we sorted on column "C" before we filtered, and because Excel sorts blank records after non-blanks, the filter retains the first row (with the data in it) based on column "C" criteria but deletes hides any duplicates.

tanstaafl.