【Shell编程】读取文件每一列拼接成SQL语句

原创已于 2024-04-03 14:23:30 修改 · 408 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

于 2024-04-03 14:07:02 首次发布

文章描述了一个通过Shell脚本（Bash）处理CSV文件，删除首尾行后，逐行分割并插入到数据库表中的过程，使用sed命令操作文本文件，生成一系列SQL插入语句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

原始数据：

BEGIN
40041|060|20240115100111036|999100467230|051000026287|
40041|060|20240115100111037|999100467231|051000026288|
40041|060|20240115100111038|999100467232|051000026289|
40041|060|20240115100111039|999100467233|051000026290|
40041|060|20240115100111040|999100467234|051000026291|
40041|060|20240115100111041|999100467235|051000026292|
40041|060|20240115100111042|999100467236|051000026293|
40041|060|20240115100111043|999100467237|051000026294|
40041|060|20240115100111044|999100467238|051000026295|
40041|060|20240115100111045|999100467239|051000026296|
40041|060|20240115100111046|999100467240|051000026297|
40041|060|20240115100111047|999100467241|051000026298|
40041|060|20240115100111048|999100467242|051000026299|
40041|060|20240115100111049|999100467243|051000026300|
40041|060|20240115100111050|999100467244|051000026301|
40041|060|20240115100111051|999100467245|051000026302|
40041|060|20240115100111052|999100467246|051000026303|
40041|060|20240115100111053|999100467247|051000026304|
40041|060|20240115100111054|999100467248|051000026305|
40041|060|20240115100111055|999100467249|051000026306|
40041|060|20240115100111056|999100467250|051000026307|
40041|060|20240115100111057|999100467251|051000026308|
40041|060|20240115100111058|999100467252|051000026309|
END

Shell脚本：

#!/bin/bash
 
# 文件名为test1.txt
filename="test1.txt"
 
# 使用sed命令删除第一行和最后一行，并输出剩余行，保存至test2.txt文件中
sed -e '1d;$d' "$filename" > test2.txt

# 取修改后的文件
filename1="test2.txt"

# 定义分隔符为逗号
IFS='|'
 
# 读取文件中的每一行
while read -r field1 field2 field3 field4 field5
do
    # 打印读取的字段
	echo $field1,$field2,$field3,$field4,$field5
	# 拼接sql语句 写到newfile.txt文件中
    echo "insert into table_name values ('"$field1"','"$field2"','"$field3"','"$field4"','"$field5"');" >> newfile.txt
done < $filename1

结果文件：

insert into table_name values ('40041','060','20240115100111036','999100467230','051000026287');
insert into table_name values ('40041','060','20240115100111037','999100467231','051000026288');
insert into table_name values ('40041','060','20240115100111038','999100467232','051000026289');
insert into table_name values ('40041','060','20240115100111039','999100467233','051000026290');
insert into table_name values ('40041','060','20240115100111040','999100467234','051000026291');
insert into table_name values ('40041','060','20240115100111041','999100467235','051000026292');
insert into table_name values ('40041','060','20240115100111042','999100467236','051000026293');
insert into table_name values ('40041','060','20240115100111043','999100467237','051000026294');
insert into table_name values ('40041','060','20240115100111044','999100467238','051000026295');
insert into table_name values ('40041','060','20240115100111045','999100467239','051000026296');
insert into table_name values ('40041','060','20240115100111046','999100467240','051000026297');
insert into table_name values ('40041','060','20240115100111047','999100467241','051000026298');
insert into table_name values ('40041','060','20240115100111048','999100467242','051000026299');
insert into table_name values ('40041','060','20240115100111049','999100467243','051000026300');
insert into table_name values ('40041','060','20240115100111050','999100467244','051000026301');
insert into table_name values ('40041','060','20240115100111051','999100467245','051000026302');
insert into table_name values ('40041','060','20240115100111052','999100467246','051000026303');
insert into table_name values ('40041','060','20240115100111053','999100467247','051000026304');
insert into table_name values ('40041','060','20240115100111054','999100467248','051000026305');
insert into table_name values ('40041','060','20240115100111055','999100467249','051000026306');
insert into table_name values ('40041','060','20240115100111056','999100467250','051000026307');
insert into table_name values ('40041','060','20240115100111057','999100467251','051000026308');
insert into table_name values ('40041','060','20240115100111058','999100467252','051000026309');