INSERT INTO `bs_province` VALUES ('1', '110000', '北京', '北京', '116.405289', '39.904987', '1', '2019-02-28 17:16:58', '2019-02-28 17:17:05', '', '0', '00000000');
INSERT INTO `bs_province` VALUES ('2', '120000', '天津', '天津', '117.190186', '39.125595', '2', '2019-02-28 17:16:58', '2019-02-28 17:17:05', '', '0', '00000000');
我希望把上面的数据变成如下的json
[
{"code":"110000","name":"北京","lng":116.405289,"lat":39.904987,"sort":1},
{"code":"120000","name":"天津","lng":117.190186,"lat":39.125595,"sort":2}
]
代码使用minimist处理参数,使用流来读写文件,使用readline来逐行读文件,并把lng, lat, sort列转化为数字。
// usage:
// node sql2json.js -i bs_province.sql -o province.json --columns="code, name, lng, lat, sort" --numbers="1, 2, 4, 5, 6"
// node sql2json.js -i bs_city.sql -o city.json --columns="code, name, pcode, lng, lat, sort" --numbers="1, 2, 4, 5, 6, 7"
// node sql2json.js -i bs_area.sql -o district.json --columns="code, name, pcode, lng, lat, sort" --numbers="1, 3, 2, 5, 6, 7"
// node sql2json.js -i bs_street.sql -o street.json --columns="code, name, pcode, lng, lat, sort" --numbers="1, 3, 2, 5, 6, 7"
const readline = require("readline");
const fs = require("fs");
const minimist = require("minimist");
const args = minimist(process.argv.slice(2));
if (args.i) {
args.input = args.i;
}
if (args.o) {
args.output = args.o;
}
const columns = args.columns.split(",");
const numbers = args.numbers.split(",");
const count = numbers.length;
if (columns.length !== numbers.length) {
console.error("columns.length != numbers.length");
return 1;
}
for (let i = 0; i < count; ++i) {
columns[i] = columns[i].trim();
numbers[i] = numbers[i].trim();
}
let firstLine = true;
const input = fs.createReadStream(args.input);
const output = fs.createWriteStream(args.output);
output.write("[\n");
const rl = readline.createInterface({ input });
rl.on("line", line => {
if (line.startsWith("INSERT INTO")) {
if (!firstLine) {
output.write(",\n");
}
line = line.replace(/^INSERT INTO `.*` VALUES \(/, "");
line = line.replace(/\);$/, "");
const parts = line.split(", ");
const o = {};
for (let i = 0; i < count; ++i) {
const part = parts[numbers[i]];
const column = columns[i];
o[column] = part.substring(1, part.length - 1);
if (column === "lng" || column === "lat" || column === "sort") {
o[column] = Number(o[column]);
}
}
const str = "\t" + JSON.stringify(o);
output.write(str);
firstLine = false;
}
});
input.on("end", () => {
output.write("\n]\n");
});
return 0;