magpiebrain

Using sed to generate code from CSV files

Continuing in my brief series of posts on the wonder of sed, this time we will be looking at using sed’s addressing features to generate code.

@Sed@ and addressing

As we looked at before, sed can match text based on a regular expression. sed can also store matched text in a series of registers in order to manipulate them. This example swaps two words over:

s/\(Hello\) \(World\)/\2 \1/g

So running the above command on “Hello World” would return “World Hello”. The \( \) are used to store the text that matched text in a register, numbered 1 – 9. We can then recall the registers using \1, \2 etc. The text we match can also be a regular expression. If we wanted to swap any two words over, we could use this:

s/\(.<strong>\) \(.</strong>\)/\2 \1/g

So “Hello World” becomes “World Hello”, “Foo Bar” becomes “Bar Foo” etc.

The scenario

You have some data in a spreadsheet. You need to load it into a database. You can only load it in using SQL. The data looks something like this:

Creating the SQL

First off save your data as a CSV file, without a header:

Bob,London,Red
Fred,Paris,Blue
Mandy,Valencia,Green

Then, write a sed expression that can match each value in turn, replacing the commas with dashes to make sure we are matching the text correctly:

s/\(.<strong>\),\(.</strong>\),\(.<strong>\)/\1-\2-\3/g

Which should give the result:

Bob-London-Red
Fred-Paris-Blue
Mandy-Valencia-Green

Now we know we can reprint the matched text inside other text, so its a simple matter to add some text to update our fictional database:

s/\(.</strong>\),\(.<strong>\),\(.</strong>\)/INSERT INTO  customer VALUES('\1','\2','\3');/g

Which gives us:

INSERT INTO customer VALUES('Bob','London','Red');
INSERT INTO customer VALUES('Fred','Paris','Blue');
INSERT INTO customer VALUES('Mandy','Valencia','Green');

Any language could of been used in this example. I tend to use it most for generating SQL due to the nature of my work, but I have even used it for generating test Java code or XML documents.

Next I’ll look at some more advanced log processing, and who knows, I may even show you how to do these things using Java…

This entry was posted on Friday, March 19th, 2004 at 12:48 pm and is filed under General. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.