如何将字符串列分配给变量并在Bash脚本的输出中将其引用 [英] How to assign a string column to variable and have it quoted in output in Bash script

问题描述

在此 post ，我问了一个问题，该如何使我的脚本读取CSV标头和 auto-quote 字符串值；即自动包装其数据类型为字符串并且需要"的列以便将其插入到SQL表中.显然，这超出了Bash的处理能力?

In this post, I had asked a question as to how I can have my script read a CSV header and auto-quote string values; i.e. automatically wrap those columns whose data type is string and would need "" in order to be inserted into a SQL table. Apparently, this would be more than what Bash can handle?

无论如何，我欢迎任何有关如何使以下脚本正常工作的帮助:在这里，我的脚本基本相同，但是我尝试手动定义了单独的列并将其分配给col3是字符串列的变量，因此被引用.不用说，它并没有实现我想要的功能(即为我提供col3下所有字符串数据的引用值).谢谢！

At any rate, I'd welcome any help regarding how I can make the following script work: here, I have basically the same script but I tried to manually define separate columns and assign them to variables with col3 being a string column, hence quoted. Needless to say, it doesn't do what I want it to do (i.e. give me quoted values for all the string data under col3). Thanks!

#!/bin/bash

echo Path to to-be-imported CSV:
read csv_file
echo Table name to import into:
read table

echo "INSERT INTO $table VALUES" > SQL_INSERT_$table.txt
while read col1 col2 col3 col4
do
 echo "($col1 $col2 "$col3" $col4),"
done < <(tail -n +2 $csv_file) >> SQL_INSERT_$table.txt && sed -i '' '$ s/.$/;/' SQL_INSERT_$table.txt

推荐答案

给出以下csv文件:

/tmp/csv:

Year,Make,Model,Description,Price
1997,Ford,E350,moon,-3000.00
1997,Ford,E350,moon,3000.00
1999,Chevy,Venture Extended Edition,,4900.00
1999,Chevy,Venture Extended Edition Very Large,,5000.00

和代码:

awk -F, 'OFS=FS {for (i=1;i<=NF;i++) {if (match($i, /^[0-9.-]+$/)==0) {printf "\"" $i "\""} else {printf $i}; if (i<NF) printf OFS}; printf "\n"}' /tmp/csv

它输出:

所有字符串类型的值都用引号引起来.

All the string types of values are quoted.

"Year","Make","Model","Description","Price"
1997,"Ford","E350","moon",3000.00
1999,"Chevy","Venture Extended Edition","",4900.00
1999,"Chevy","Venture Extended Edition Very Large","",5000.00

代码基于以下假设:

• 字段值内没有文字定界符(此处为逗号).
• 字段值中没有文字换行符.
• 整数或十进制字段没有空值.

如果这些假设不代表您的数据，则可能需要CSV解析器，例如

If the assumptions don't stand for your data, you may need a CSV parser like this. And the exceptional field values should be well quoted, otherwise, even a parser won't know what to do.

顺便说一句:除非您的数据非常简单且行号很少，否则建议不要使用这种方法来处理数据.许多DBMS都有一个导入/加载实用程序，可以在程序中使用它来完成这项工作.

BTW: Unless your data is very simple and the row number is a few, this is not the recommended way to manipulate the data. Many DBMS has an import/load utility that can be used in a program to do such a job.

这篇关于如何将字符串列分配给变量并在Bash脚本的输出中将其引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！