small medium large xlarge

21 Nov 2012, 23:13
Eric Freed (1 post)

Along the lines of knowing the strengths of a variety of tools, Excel has been an irreplaceable tool for me that other programmers I know have never considered.

This is mainly design-time uses, not specifically writing applications in it.

The strength of Excel is in parsing batches of strings.

For example:
When given a spreadsheet of data submitted by a client that needs to be imported into a database. In a new column on the right, write a formula using the CONCATENATE function that builds a full insert statements, pulling data from relevant cells. With one click and drag, the formula is copied down across all rows, now select the column and copy to the clipboard. The clipboard now contains the finished sql statements, one on each line.
In some cases, this is an annual recurring task where a new spreadsheet from the customer comes in. I just open the spreadsheet from last year, paste in the new data and the new sql statements are ready to be copied out.

You must be logged in to comment