使用shell脚本快速录入数据

工作中经常遇到需要录入网页上的一些报表的数据，手动录入显得既麻烦又不科学，现记录下学习到的快速录入数据的方法。

• 选中网页上的一个表格里的数据并选择复制
• 粘贴到excel单元格，做一些简单的处理，如页面上的表示状态是与否的还原回1与0。
• 编写shell脚本，主要逻辑是读取excel文件解析然后根据特定的SQL语句规则批量生成适合的SQL，windows下可以使用git bash执行shell脚本

文件样例：

#!/bin/bash

# check parameter
if [ $1'x' == 'x' ]; then
    echo usage: $0 data_file
    exit 1
fi

DATA_FILE=$1

if [ ! -d sql ]; then
    mkdir sql
fi

TODAY=`date +%Y%m%d`
TARGET_FILE=sql/sync_menu_$TODAY.sql

rm -f  $TARGET_FILE


echo "INSERT INTO TB_PRODUCT_FINANCING_PROJECT (" >> $TARGET_FILE
echo "  SERIAL_NUMBER," >> $TARGET_FILE
echo "  NAME," >> $TARGET_FILE
echo "  MIN_PERIOD," >> $TARGET_FILE
echo "  ENABLE," >> $TARGET_FILE
echo "  EDITABLE," >> $TARGET_FILE
echo "  HAS_DOWN_PAYMENT," >> $TARGET_FILE
echo "  HAS_DEPOSIT," >> $TARGET_FILE
echo "  HAS_BROKERAGE," >> $TARGET_FILE
echo "  SORT" >> $TARGET_FILE
echo ") " >> $TARGET_FILE
echo "VALUES" >> $TARGET_FILE

awk '\
{
    print "("
    if($1=="null") {
        print "\tnull,"
    } else {
        print "\t'\''"$1"'\'',"
    }
    if($2=="null") {
        print "\tnull,"
    } else {
        print "\t'\''"$2"'\'',"
    }
    if($3=="null") {
        print "\tnull,"
    } else {
        print "\t'\''"$3"'\'',"
    }
    if($4=="null") {
        print "\tnull,"
    } else {
        print "\t'\''"$4"'\'',"
    }
    if($5=="null") {
        print "\tnull,"
    } else {
        print "\t'\''"$5"'\'',"
    }
    if($6=="null") {
        print "\tnull,"
    } else {
        print "\t'\''"$6"'\'',"
    }
    if($7=="null") {
        print "\tnull,"
    } else {
        print "\t'\''"$7"'\'',"
    }
    if($8=="null") {
        print "\tnull,"
    } else {
        print "\t'\''"$8"'\'',"
    }
    if($9=="null") {
        print "\tnull,"
    } else {
        print "\t'\''"$9"'\''"
    }   
    print "),"
}
' \
$DATA_FILE >> $TARGET_FILE

F010    客户成交价   1   1   1   1   1   1   1
F050    购置税 1   1   1   1   0   0   5
F060    GPS费用   1   1   1   0   0   0   6
F070    GPS服务费  6   0   1   0   0   0   7
F091    交强险 1   1   1   0   0   0   9
F101    商业险 1   1   1   0   0   0   10
F121    车船税 1   1   1   0   0   0   12
F092    交强险第二年  18  0   1   0   0   0   13
F102    商业险第二年  18  0   1   0   0   0   14
F122    车船税第二年  18  0   1   0   0   0   16
F093    交强险第三年  30  0   1   0   0   0   17
F103    商业险第三年  30  0   1   1   1   0   18
F123    车船税第三年  30  0   1   0   0   0   20
F094    交强险第四年  42  0   1   0   0   0   21
F104    商业险第四年  42  0   1   0   0   0   22
F124    车船税第四年  42  0   1   0   0   0   24
F095    交强险第五年  54  0   1   0   0   0   25
F105    商业险第五年  54  0   1   0   0   0   26
F125    车船税第五年  54  0   1   0   0   0   28
F126    违章押金    1   1   1   0   0   0   29
F127    上牌费 1   1   1   0   0   0   30
F129    过户费 1   1   1   0   0   0   32
F130    居间费 1   1   0   0   0   0   33