有些项目中可能会遇到这种情况，就是需要把wrod文档或者Excel里面的数据转换成为一个SQL 脚本，一般情况下，如果数据量较少，咱们可以手动写脚本，但是如果数据量大的时候，那简直要命，重复工作量大且容易出错，下面我介绍一种技巧去做这件事。
这个技巧的思路是：
Word文档 > Excel文档 > Json数据 >SQL脚本
一、整理word文档为Excel
image
上面是一个word文档，现在需要新建一个Excel并把数据构造好，向下面这样：
image
二、在线工具将Excel转换为JS数据（或JSON）
百度一下 “excel 转 json”，就会发现很多在线工具，我用的是这个 在线工具
image
三、用js脚本拼接出字符串
到此我们有了基础数据，那么就用js遍历数组把sql字符串拼接出来，下面是我做的示例：
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Arr to Str</title>
</head>
<body class="content">
<textarea id="my-app" rows="100" style="width:1000px;"></textarea>
</body>
<script>
var arr = [
["WebPros", "Website ranks well for main global terms."],
["WebPros", "Website ranks well for some individual servicer terms."],
["WebPros", "Website ranks well for some individual auto makes"],
["WebPros", "Website is mobile friendly & Responsive"],
["WebPros", "Website is mobile friendly (But not Responsive)"],
["WebPros", "Website design is modern"],
["WebCons", "Site ranks poorly for main keywords. This causes poor traffic and loss of revenue."],
["WebCons", "Website doesn’t rank well for individual services such as “??? Repair” or “??? Repair”. Website will miss on opportunity to have visitors who search as such. This leads to loss of traffic & revenues."],
["WebCons", "Website doesn’t rank well for individual auto makes such as “??? Repair” or “??? Repair”. Website will miss on opportunity to have visitors who search for auto make repair. This leads to loss of traffic and revenues."],
];
var tempHtml = function (template, obj) {
return template.replace(/\$\w+\$/gi, function (matches) {
var returns = obj[matches.replace(/\$/g, "")];
return (returns + "") === "undefined" ? "" : returns;
});
};
var template = "SET @index = @index + 1;\r\nINSERT INTO dbo.SBA_PredefinedTexts(PredefinedTextId, ParentId, PredefinedTextType, Text) VALUES (@index, NULL, ''$textType$'', ''$text$'');\r\n\r\n"
var str = "DECLARE @index INT\r\nSET @index = (SELECT MAX(PredefinedTextId) as maxId FROM [DriveModel].[dbo].[SBA_PredefinedTexts])\r\n\r\n";
arr.forEach(element => {
if (element && element.length > 0) {
var obj = {
textType: element[0].replace(/\s+/g, ' '),
text: element[1].replace(/\s+/g, ' ').replace('/“/g', '"').replace('/”/g', '"').replace("/’/g", "''''")
};
str += tempHtml(template, obj);
}
});
document.getElementById("my-app").value = str;
</script>
</html>
这里有一个技巧使用了模板的概念，tempHtml方法用正则表达式替换关键字，可以减少重复工作量
四、打开html，复制出字符串
image.png
结束！！
网友评论