Help for a SpreadSheet newbie?

[Tom Wiley]

I have to parse several years worth of timecards so that the bean counters
in the front office can do an audit. With Perl it is dead simple to
extract the info and build a nice set of reports. My problem is that they
want the results in a pretty Excel format and I know just enough about
spreadsheets to do a sum() - sometimes. I have pulled out the info and
put it into a comma delimited format and it imports into OO or Excel just
fine, but of course it has no formatting.

So, my questions... Can I build formatting into a CSV type file? I have
a feeling that the answer is no since when a file is saved as comma
delimited a message pops up informing that some formatting will be lost.

Or, are pre-made templates the answer? Never used them but I assume that
you can preformat rows and columns and then import a file into it,
although so far I have failed to find an import menu item. I think the
major problem here is my lack of an Excel book.

Or what?

Any spreadsheet gurus out there that can point me toward the proper
direction?

Thanx
Tom Wiley

 

[Dave Peterson]

You're right about .csv files. They're just plain text and don't save
formatting (or formulas or anything but text).

It sounds like you have to do this more than once.

If that's true, how about:

If the CSV files you work with are always the same layout, maybe you could:

1. Start a new workbook
2. Record a macro when you open it
3. Add headers/print layout/filters/subtotals, whatever else you can think of.
4. Add a button from the Forms toolbar to the first worksheet in that workbook.
5. Save that workbook.

Save all your data files as .csv and use "WorkbookToImport.xls", click the giant
button and select their filename.

If you need help with your recorded macro, post back.

 

[bgeier]

No, a comma separated file, is flat ascii only (at least to the best of
my knowledge).

As a "down and dirty" quick fix you could do the import, then manually
format the columns as they need to be.

For example, after you import the data, select the date column so it is
highlighted, then right click, format cells -> select the Number tab,
then select your preferred date format, repeat for each column you
have, probably start time, end time, etc...

Of course, you would have to do this for each year of data you have.
You could probably record a macro to do it for you as you do the first
one.

 

[Puppet_Sock]

Tom Wiley wrote:
[snip]

So, my questions... Can I build formatting into a CSV type file? I have
a feeling that the answer is no since when a file is saved as comma
delimited a message pops up informing that some formatting will be lost.

Or, are pre-made templates the answer? Never used them but I assume that
you can preformat rows and columns and then import a file into it,
although so far I have failed to find an import menu item. I think the
major problem here is my lack of an Excel book.

Or what?

You have a few choices.

First, to get the basic steps of much of this, the macro recorder
is your friend. Tools > Macro > Record New Macro, and then you
go through the steps for doing it manually. This will record VBA
that you can then easily modify to make a program instead of
just the exact same steps.

One choice is kind of a template. You make yourself a blank
Excel file with all the formats ready to go. Then you import the
CSV file into another sheet or workbook. Then copy the data
into the cells it belongs. You might choose this if there is a
lot of data in the CSV files that you don't want to keep, or if
you need it in different order or arrangement from what it is
in the CSV file.

Another choice is to import the CSV into a worksheet, and
then use VBA to modify the formatting to the required form.
You might choose this if nearly everything in the CSV files
is needed, and it is in nearly the right order to start.
This way you just need to run around deleting one or two
rows or columns, setting the formats, maybe putting lines
around boxes, put in the fonts, that sort of thing.

 

[Tom Wiley]

O

1. Start a new workbook
2. Record a macro when you open it
3. Add headers/print layout/filters/subtotals, whatever else you can
think of. 4. Add a button from the Forms toolbar to the first worksheet
in that workbook. 5. Save that workbook.

Save all your data files as .csv and use "WorkbookToImport.xls", click the
giant button and select their filename.

If you need help with your recorded macro, post back.

Thanks. I knew about macros, but don't think I ever tried them on a
spreadsheet before. One problem that I am just beginning to realise is
that there must be a half dozen or more versions of Excel plus OpenOffice
1 and 2. Of course all of them work the same at the basic or even
advanced level, but not once you start moving into esoteric stuff. Tried
something at the office that worked pretty well, then came home to refine
it and couldn't even find the menu item - the realised that one was Office
XP and the other was Office 200x something or other.

Thanx

Tom

 

[Tom Wiley]

First, to get the basic steps of much of this, the macro recorder is your
friend. Tools > Macro > Record New Macro, and then you go through the
steps for doing it manually. This will record VBA that you can then easily
modify to make a program instead of just the exact same steps.

Thanks. Good reply and I will give them a try. Used to be pretty good on
VB6 and VBA a while ago ('bout a hundred years, or so) before I moved to
Perl, so that may be the just the thing. I totally forgot that a sheet
will use a script.

Thankx
Tom