Be Excellent To Each Other

Hello,

I've got a load of excel files, that I would like to compile into one big spreadsheet.

The data is spat out of our database as tab delimited RTF, which opens in excel. One file is one 'batch' and each row represents one sample, so if I can have it easily put the whole months worth of data into one sheet I can easily count the number of samples we've handled. The files are grouped in folders (by month).

Is there an easy and quick way to do this?

It's on a mac at work, office 2008 I think.

Thanks very much.

Any chance you could do it directly in the database? That's kinda what they're for.

from a cmd prompt in windows would copy all the files in that folder into one big file as it's just Rich text I think that should work...

It would work for a CSV, and I guess tab seperated would be the same (depends if there are any weird headers or footers I guess

Malc

Ideally yes, but in reality no. It will take the IT team a while to get round to writing the report it as it's such a minor concern for them. At the moment, once the sample sheet is generated the database discards all the information on those samples* (If I can compile them myself I can also count who uses the service the most, which section the samples came from, and provide my boss with a bit more of a detailed breakdown).

*A bit poor I know, but this aspect of the database was a real 'bolt on' afterthought. They are working on a full sample tracking interface but it probably won't even be ready for testing until August.



I can give this a try thanks Malc. I'll bring the files home and do it on my PC. The only available PCs at work are tied into the building management and security systems and I tend not to fuck about with those unless absolutely necessary (although I did help the facility manager retrieve all his emails onto there after his laptop lost all the user profiles - and yes, he got a stern lecture about backing up).

I like that this forum is offering me computer help at this time of the UK night.

I've just thought, this will copy them together in the order that a dir would list them (normally this is name alphabetical) but occasionaly this is in a different order.

I don't know if this matters or not (normally I am copying files together that I want copied in name order so it's fine)

Also, if the files are binary and you want to join them in the same fashion you would need to use the slash b tag



But I shouldn't think you would need to do that as RTF is just ASCII isn't it? so it should be fine without...

Malc

Heh

See bits and bobs thread for why I am on my computer at this time...

Off to bed now...

Malc

That would be useful, thanks Malc. They are listed in date of creation order, the filename is YYMMDD based. I'll give this a try when I'm back at work on Monday.

You can do the same thing in a bash shell on a mac.

cat file1.rtf file2.rtf file3.rtf file4.rtf > output.rtf

Can I use a wildcard? i don't want to type in 30-40 filenames.

Also, I have never used a bash shell before, so I might try both approaches.

I use the DOS copy command regularly. Navigate to the right directory using the 'cd' command and then as Malc says, 'copy *.* all.rtf'

Thanks for the help guys, this is useful and worked pretty much as I wanted.

You really can contente RTFs? Huh. I didn't think that would work.

When I looked properly, the files had a .xls extension, and that's what I used in the copy command. I was told (maybe incorrectly) that the reports generate tab-delimited text. But they aren't like 'regular' excel files as they can't have any formatting or anything other than plain text saved in them.

Could this xls extension be considered a 'wrapper'? While I'm OK with basic day-to-day computer and Office work (enough to be considered as a first point of contact for computer problems* in our satellite facility, before going to the IT dept at the main institute) and have heard many of the words, i'm far from skilled and tend to bluff around a fair bit.

*Mostly user error, or showing people how to use simple things. Anything serious I won't touch.

What I would suspect is highly likely is that they are just plain text files containing tab delimited data, but they've just saved them with a .xls extension because they're idiots who think it's reasonable to lie about file formats because it lets you just double click the file to open it in excel. If that's the case, you should be able to use cat without problems.

They can't be proper actual xls files because Excel native files are binary and so simply cat'ing them together from the command line wouldn't work; you'd end up a huge pile of corrupt nonsense that wouldn't open because it wouldn't be in the very specific format that Excel would expect. Which means it's as Craster suspects and the system that's outputting these files is just sticking a .xls file extension on the end of a text file purely so that whenever someone double-clicks these things they automatically open in Excel. Which is a bit of a bodge indeed, but I can't think of another way to guarantee this behaviour. There's no specific file format for tab delimited files (that I'm aware of?) so, unlike CSV for instance, no 'proper' file exension you can use which guarantees that people open them up in Excel. The alternative would be leave the technically more correct .txt extension on them, but that's just guaranteeing shitloads of support calls because the reports will open in Notepad and no one will ever bother remembering how to get them into Excel instead; they'll just bitch and whine because it's what people do.

Anyway, to answer your other question: the xls file extention isn't a 'wrapper' in this scenario; that term doesn't really hold any meaning in this context. It's just a text file with a different file extension on the end, nothing more to it than that. In my opinion at least.

Also I've just tried directly concatenating two proper RTF files together and it doesn't work; all you get is the content of the first file. Looking at it as pure text there's some specific markup at the beginning and end of each RTFe file, so presumably even after cat'ing more than one file together the application you use to open the file just sees the markup delineating the end of the original first file and gives up parsing it at that point regardless of how much content exists afterwards.

I'm fairly sure this is the case, yes. And the command line example given worked pretty well.

Idiots might be a bit strong, mind... I imagine this component was a quick fix, maybe a bit of a bodge. It does work, after all - but maybe not as well as it might. As users, we've come to learn to live with the limitations. Only a very small number of people use this part of the database anyway,

They probably did it because Excel doesn't natively open .tsv files, you have to either drag them onto an open worksheet or do a File|Open from within Excel (or set up the default program, of course).

I have a predisposition to dislike the use of file extensions that don't reflect file formats