Skip to content

Instantly share code, notes, and snippets.

@antiops
Created November 9, 2019 04:43
Show Gist options
  • Save antiops/93362c406a131f3394aa9741b44bb7d3 to your computer and use it in GitHub Desktop.
Save antiops/93362c406a131f3394aa9741b44bb7d3 to your computer and use it in GitHub Desktop.
Split SQL tables to CSV
BEGIN {
# file starts with DDL statements that go into header.sql
table = "header";
sql = 1
}
{
# -- step 1 --
# determine whether current line contains a DDL sql statement or
# table data
if ($0 ~ "^INSERT INTO "){
# this is a data line for the current table, it goes into a csv file
sql = 0
}
else if ($0 ~ "^DROP TABLE IF EXISTS"){
# a new table is coming up
# remember the name
# output goes to sql file
table = gensub(/DROP TABLE IF EXISTS `(.+)`;/, "\\1", "g" $0);
sql = 1
}
else {
# any other lines belong to the sql file of the current table
sql = 1
}
# -- step 2 --
# transform and write the line into target file
if (sql == 1){
# sql lines are appended to the <table_name>.sql file
print > table".sql";
}
else {
# data lines are split and written as individual csv records
# inserts are of the form:
# INSERT INTO `borders` VALUES ('A','D',784),...,('ZW','Z',797);
# splitting on three separators:
# INSERT INTO `table_name` VALUES ( -- beginning of line
# ),( -- record separator
# ); -- end of line
# split records are collected in array 'a'
n = split($0, a, /(^INSERT INTO `[^`]*` VALUES \()|(\),\()|(\);$)/)
for(i=1;i<=n;i++) {
# first and last splits may be empty strings
len = length(a[i])
if (len > 0) {
# if record is not empty, output to <table_name>.csv file
data = a[i]
print data > table".csv";
}
}
}
}
END {}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment