利用Microsoft.VisualBasic中TextFieldParser解析器把CSV格式倒入数据库
写了个Demo，利用Microsoft.VisualBasic这个程序集中的TextFieldParser解析器解析CSV格式的文件，然后将解析的数据插入到相关表，这样的好处是不用去用令人头疼的ODBC去操作CSV格式文件，如之前是这样去操作:
利用ODBC去操作
string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + this.dirCSV.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
string sql_select;
OdbcConnection conn;
conn = new OdbcConnection(strConnString.Trim());
conn.Open();
OdbcCommand commandRowCount = new OdbcCommand("SELECT COUNT(*) FROM [" + this.FileNevCSV.Trim() + "]", conn);
this.rowCount = System.Convert.ToInt32(commandRowCount.ExecuteScalar());
sql_select = "select * from [" + this.FileNevCSV.Trim() + "]";
OdbcCommand commandSourceData = new OdbcCommand(sql_select, conn);
OdbcDataReader dataReader = commandSourceData.ExecuteReader();
DataTable dt;
dt = dataReader.GetSchemaTable();
利用TextFieldParser操作
namespace ImportCSV
{
class Program
{
//连接字符串
private static readonly string connStr = @"Data Source=BEAR\EYESSQLSERVER;Initial Catalog=Test;Integrated Security=True";
//表明，最好做成是客配置，如Winform程序下拉框
private static string tableName = "Customer";
/// <summary>
/// 执行查询，返回DataTable数据源
/// </summary>
/// <param name="connStr"></param>
/// <param name="cmdText"></param>
/// <param name="parameters"></param>
/// <returns></returns>
static DataTable ExecuteDataTable(string connStr,string cmdText,params SqlParameter[] parameters)
{
using (SqlConnection conn=new SqlConnection(connStr))
{
using (SqlCommand cmd=conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(parameters);
using (SqlDataAdapter adapter=new SqlDataAdapter(cmd))
{
DataTable dataTable=new DataTable();
adapter.Fill(dataTable);
return dataTable;
}
}
}
}
/// <summary>
/// 得到主键列
/// </summary>
/// <returns></returns>
static List<string> GetKeyWords()
{
//SQL Server 系统试图得到主键列
string sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME=@TABLE_NAME";
DataTable dt=ExecuteDataTable(connStr, sql, new SqlParameter("TABLE_NAME", tableName));
List<string> listKeyWords=new List<string>();
foreach (DataRow row in dt.Rows)
{
string keyWord = Convert.ToString(row["COLUMN_NAME"]);
listKeyWords.Add(keyWord);
}
return listKeyWords;
}
/// <summary>
/// 得到指定表所有的列
/// </summary>
/// <returns></returns>
static List<string> GetAllColumns()
{
//系统视图得到所有列
string sql = "select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@TABLE_NAME";
DataTable dt = ExecuteDataTable(connStr, sql, new SqlParameter("TABLE_NAME", tableName));
List<string> listCols = new List<string>();
foreach (DataRow row in dt.Rows)
{
string columnName = Convert.ToString(row["COLUMN_NAME"]);
listCols.Add(columnName);
}
return listCols;
}
/// <summary>
/// 得到初主键外所有列
/// </summary>
/// <returns></returns>
static List<string> GetAllColumnsWithoutKeyWords()
{
List<string> listAllColumns = GetAllColumns();
List<string> listKeyWords= GetKeyWords();
return listAllColumns.Except(listKeyWords).ToList();
}
/// <summary>
/// 得到除Identity（标识）外所有列
/// </summary>
/// <returns></returns>
static List<string> GetAllColumnsWithoutIdentity()
{
//得到Identity标志列
string sql = @"select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
and TABLE_NAME=@TABLE_NAME";
DataTable dt = ExecuteDataTable(connStr, sql, new SqlParameter("TABLE_NAME", tableName));
List<string> listColumnsWithoutIdentity = new List<string>();
foreach (DataRow row in dt.Rows)
{
listColumnsWithoutIdentity.Add(Convert.ToString(row["COLUMN_NAME"]));
}
List<string> listAllColumns = GetAllColumns();
//从所有列中排除
return listAllColumns.Except(listColumnsWithoutIdentity).ToList();
}
static void Main(string[] args)
{
using (SqlConnection connection = new SqlConnection(connStr))
{
using (SqlCommand cmd = connection.CreateCommand())
{
//得到所有列除Identity标志列
string[] columnsWithoutIdentity = GetAllColumnsWithoutIdentity().ToArray();
//SQL参数
string[] columnsParameters = (from c in columnsWithoutIdentity select "@" + c).ToArray();
StringBuilder sb=new StringBuilder();
//拼接Insert SQL语句
sb.AppendLine("insert into " + tableName + "(" + string.Join(",", columnsWithoutIdentity) +
") output inserted.id values(" + string.Join(",",columnsParameters)+ ")");
cmd.CommandText = sb.ToString();
//从路径得到csv的文件，可以做成打开框
using (var myCsvFile = new TextFieldParser(@"C:\Users\eyeswang\Desktop\xxxx.csv",Encoding.Default))
{
myCsvFile.TextFieldType = FieldType.Delimited;
myCsvFile.SetDelimiters(",");//设置解析器分割符
connection.Open();
//循环，一行一行读
while (!myCsvFile.EndOfData)
{
string[] fieldArray;
try
{
//读取一行
fieldArray = myCsvFile.ReadFields();
for (int i = 0; i < fieldArray.Count(); i++)
{
//给参数赋值，如果是NULL，则DBNULL.Value插入相关列
cmd.Parameters.Add("@" + columnsWithoutIdentity[i], fieldArray[i].ToUpper() == "NULL" ? (object)DBNull.Value : fieldArray[i]);
}
//执行完毕后记得Parameters Clear
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
catch (Microsoft.VisualBasic.FileIO.MalformedLineException ex)
{
continue;
}
}
}
}
}
Console.WriteLine("OK");
Console.ReadKey();
}
}
}
