Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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 actualy 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.)

share|improve this question
1  
It's not trivial because of the quoting. And your output format could be ambiguous because of that too. Have you considered importing to an sqlite database and exporting from there (see .mode and .separator commands)? (Won't remove the quoting problem.) –  Mat Nov 19 '11 at 14:53
    
Well Wikimedia and Wiktionary dump files can be extremely huge. I remember some imports taking days to import. I think the quoting issue was why I chose Perl too. –  hippietrail Nov 19 '11 at 15:36
    
You'll get better performance with restricted tools such as sed than with general-purpose tools such as Perl, and you'll get better performance if you exploit the specific format of your input rather than process SQL in full generality. Can you post a small representative sample of the data you want to process? –  Gilles Nov 20 '11 at 0:09
    
If you really feel there are only nontrivial solutions that should be an acceptable answer by the way. At least then people working with such SQL files as input data will have a definitive answer and know not to bother wasting too much time scratching their heads. –  hippietrail Nov 20 '11 at 19:00
    
@Gilles: I posted a sample that I hope is representative. –  hippietrail Nov 23 '11 at 10:58
add comment

1 Answer 1

This would be trivial:

1) Import the SQL data into an SQL database

2) Output the data in the format you want with any of the SQL tools for doing this that already exist. E.g. SELECT INTO OUTFILE

And that is totally scriptable. If there are speed issues, get faster hardware, especially drives. If you absolutely want to parse this in some other language, you will probably find yourself writing a full SQL parser, just to deal with all the quotation / braces / reserved words etc. While this is definitely doable, I wouldn't consider it trivial.

share|improve this answer
    
No I've tried this. Parsing it in Perl took minutes, importing into a database took days. Faster hardware just does not magic away that many orders of magnitude. Just the parser wasn't very elegant like I imagined I should be able to make it. If you read the question you will see it's nowhere near full SQL but a trivial limited predictable subset of it. –  hippietrail Jan 1 '12 at 9:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.