Several times I've found myself working on data that has been exported to .sql
files that I want to process rather than import into a database.
I'm only talking about "pure data" sql files. Ones that have nothing other than INSERT INTO
statements. (They may have some prolog at the top that I can ignore.)
Generally the files will have many lines beginning with INSERT INTO
and ending with ;
between will be a variable number of "records" beginning with (
, ending with )
, and separated with ,
. Each "record" will be made up of a fixed number of "fields" which are also separated by ,
.
The filter should strip out all the SQL statements, separators, terminators, etc. It should output just one "record" per line with each "field" separated by say a comma or tab.
In my mind it should be totally trivial and common enough that it must've been implemented many times. But I never seem to be able to find existing tools or scripts.
I usually try to tackle it in Perl by treating ),(
as the record separator instead of \n
but I always get stuck in annoying details.
Has anyone got (or can make) a really simple script that does this? I don't care whether it's in bash, sed, awk, Perl, Python, etc.
Here's some real example data. I have altered it to have only a few records per line. The actual files have about a million characters per line /statement before they reach the ;
and begin the next one.
INSERT INTO `categorylinks` VALUES
(0,'Wikisaurus','RunJobs.php','2008-01-27
03:10:30','','','page'),(8,'Wiktionary','TEXT OF THE GNU FREE
DOCUMENTATION LICENSE','2011-01-26
23:50:34','','uppercase','page'),(12,'Wiktionary','WHAT WIKTIONARY IS
NOT\nWHAT WIKTIONARY IS NOT','2011-04-10 18:22:23','What Wiktionary is
not','uppercase','page'),(12,'Wiktionary:Help','WHAT WIKTIONARY IS
NOT\nWHAT WIKTIONARY IS NOT','2011-04-10 18:22:23','What Wiktionary is
not','uppercase','page'); INSERT INTO `categorylinks` VALUES
(2184,'Swedish_nouns','RELIGION','2011-01-06
00:04:41','','uppercase','page'),(2184,'Translations_to_be_checked_(Albanian)','RELIGION','2007-01-27
08:16:34','','uppercase','page'),(2184,'Translations_to_be_checked_(Bulgarian)','RELIGION','2007-01-27
08:16:34','','uppercase','page');
Output would be something like this. Whether fields are quoted or not probably doesn't matter. Comma or tab separated doesn't matter. Correct escaping of commas would be needed though.
0,'Wikisaurus','RunJobs.php','2008-01-27 03:10:30','','','page'
8,'Wiktionary','TEXT OF THE GNU FREE DOCUMENTATION LICENSE','2011-01-26 23:50:34','','uppercase','page'
12,'Wiktionary','WHAT WIKTIONARY IS NOT\nWHAT WIKTIONARY IS NOT','2011-04-10 18:22:23','What Wiktionary is not','uppercase','page'
12,'Wiktionary:Help','WHAT WIKTIONARY IS NOT\nWHAT WIKTIONARY IS NOT','2011-04-10 18:22:23','What Wiktionary is not','uppercase','page'
2184,'Swedish_nouns','RELIGION','2011-01-06 00:04:41','','uppercase','page'
2184,'Translations_to_be_checked_(Albanian)','RELIGION','2007-01-27 08:16:34','','uppercase','page'
2184,'Translations_to_be_checked_(Bulgarian)','RELIGION','2007-01-27 08:16:34','','uppercase','page'
The data I'm usually working with is part of the dumps of Wikipedia and Wiktionary.
.mode
and.separator
commands)? (Won't remove the quoting problem.)