我有个这样的CSV文件,我用记事本打开的
#支付宝帐务明细查询,,,,,,,,,,
#账号: [2088001390707977],,,,,,,,,,
#起始日期: [2010年09月01日] 终止日期: [2010年09月30日],,,,,,,,,,
#-----------------------------------------帐务明细列表----------------------------------------,,,,,,,,,,
帐务流水号,业务流水号,商户订单号,发生时间,对方帐号,收入金额(+元),支出金额(-元),账户余额(元),交易发生地,业务类型,备注
"111272929621 ","2010082648530764 ","T200P45653312371762 ",2010年09月01日 00:05:28,"20880063000888880156 ",9.95,0,87627.06,支付宝,转账,
"111273006641 ","2010081999335157 ","T200P44958219274462 ",2010年09月01日 00:06:06,"20880063000888880156 ",34,0,87661.06,支付宝,转账,
"111273208641 ","2010082750240091 ","T200P45694130641962 ",2010年09月01日 00:07:30,"20880063000888880156 ",68,0,87729.06,支付宝,转账,
"111273615710 ","2010090180745297 ","208800139070797720100901001038 ",2010年09月01日 00:10:49,"20880020829867930156 ",0,-12,87717.06,支付宝,在线支付,
"111273784721 ","2010081900449056 ","T200P44955189658362 ",2010年09月01日 00:12:22,"20880063000888880156 ",29,0,87746.06,支付宝,转账,
"111276058331 ","2010082754373382 ","T200P45766043396562 ",2010年09月01日 00:35:13,"20880063000888880156 ",72,0,87818.06,支付宝,转账,
"111276061560 "," "," ",2010年09月01日 00:35:16,"20880013684318990156 ",0,-0.72,87817.34,支付宝,收费,信用卡支付服务费[2010082754373382]
"111276141970 "," "," ",2010年09月01日 00:36:08,"20880014517248550156 ",0,-5.76,87802.46,支付宝,转账,代扣款(扣款用途:淘宝客佣金代扣款cps_45766043416562)如何将它绑定到数据库中呢?麻烦大侠们代码写详细点!
#支付宝帐务明细查询,,,,,,,,,,
#账号: [2088001390707977],,,,,,,,,,
#起始日期: [2010年09月01日] 终止日期: [2010年09月30日],,,,,,,,,,
#-----------------------------------------帐务明细列表----------------------------------------,,,,,,,,,,
帐务流水号,业务流水号,商户订单号,发生时间,对方帐号,收入金额(+元),支出金额(-元),账户余额(元),交易发生地,业务类型,备注
"111272929621 ","2010082648530764 ","T200P45653312371762 ",2010年09月01日 00:05:28,"20880063000888880156 ",9.95,0,87627.06,支付宝,转账,
"111273006641 ","2010081999335157 ","T200P44958219274462 ",2010年09月01日 00:06:06,"20880063000888880156 ",34,0,87661.06,支付宝,转账,
"111273208641 ","2010082750240091 ","T200P45694130641962 ",2010年09月01日 00:07:30,"20880063000888880156 ",68,0,87729.06,支付宝,转账,
"111273615710 ","2010090180745297 ","208800139070797720100901001038 ",2010年09月01日 00:10:49,"20880020829867930156 ",0,-12,87717.06,支付宝,在线支付,
"111273784721 ","2010081900449056 ","T200P44955189658362 ",2010年09月01日 00:12:22,"20880063000888880156 ",29,0,87746.06,支付宝,转账,
"111276058331 ","2010082754373382 ","T200P45766043396562 ",2010年09月01日 00:35:13,"20880063000888880156 ",72,0,87818.06,支付宝,转账,
"111276061560 "," "," ",2010年09月01日 00:35:16,"20880013684318990156 ",0,-0.72,87817.34,支付宝,收费,信用卡支付服务费[2010082754373382]
"111276141970 "," "," ",2010年09月01日 00:36:08,"20880014517248550156 ",0,-5.76,87802.46,支付宝,转账,代扣款(扣款用途:淘宝客佣金代扣款cps_45766043416562)如何将它绑定到数据库中呢?麻烦大侠们代码写详细点!
string curFile = string.Format("{0}_sc_{1}.csv", usr, DateTime.Now.ToString("yyyyMMdd"));
string curPath = this.Server.MapPath("../upload/" + curFile); if (File.Exists(curPath) == false)
{
this.Jscript.Text = Msg.FileNotFound;
return;
} try
{
StreamReader sr = new StreamReader(curPath, System.Text.Encoding.Default);
string readLine = null;
string[] keyWord;
string sql = ""; while ((readLine = sr.ReadLine()) != null)
{
if (readLine != "") //跳过第一行(字段名)和空行
{
keyWord = readLine.Split(',');
if (keyWord[0] != "调拨单号") //跳过第一行字段名
{
sql = sql + " UPDATE Contract_Order SET "
//+ "Contract_No=" + keyWord[0] + ", " //调拨单号
//+ "Contract_Date=" + keyWord[1] + ", " //调拨日期
+ "Design_Fee=" + DBA.GetInt32Value(keyWord[2]) + ", " //设计费用
+ "Should_Rcv_Date='" + keyWord[3] + "', " //应回款日期
+ "Should_To_TA_Date='" + keyWord[4] + "', " //特安要求回款日期
+ "Is_TA_Amt='" + keyWord[5] + "', " //是否回款特安
+ "Mortgage_Amount=" + DBA.GetInt32Value(keyWord[6]) + ", " //抵押金额
+ "Mortgage_Date='" + keyWord[7] + "', " //抵押日期
+ "Surpass_Quality_Margin_Fee=" + DBA.GetInt32Value(keyWord[8]) + ", " //超质保金比例应扣费用
+ "Surpass_Quality_Margin_UnrecvFee=" + DBA.GetInt32Value(keyWord[9]) + ", " //超质保期未回应扣费用
+ "Last_Modified_Date=GetDate() "
+ "WHERE Contract_No='" + keyWord[0] + "' ";
}
}
}
sr.Close(); int n = DAL.ExecuteNonQuery(sql);
if (n > 0)
{
FillData(1); //导入数据后刷新
this.curHeaderKey.Value = "";
this.Jscript.Text = Msg.ImportOk;
}
else this.Jscript.Text = Msg.ImportFail;
}
catch
{
this.Jscript.Text = Msg.ImportFail;
} File.Delete(curPath); //删除上传临时文件
{
string CSVFile { get; set; }
DataTable Read();
bool Write(DataTable dt);
}
public class CSVHelper:ICSVWriterReader
{
private string _csvFile;
public CSVHelper(string csvFile)
{
this._csvFile = csvFile;
}
public string CSVFile
{
get{ return _csvFile; }
set{ _csvFile=value ;}
}
public DataTable Read()
{
FileInfo fi = new FileInfo(this._csvFile);
if (fi == null || !fi.Exists) return null;
StreamReader reader = new StreamReader(this._csvFile);
string line = string.Empty; int lineNumber = 0;
DataTable dt = new DataTable();
while ((line = reader.ReadLine()) != null)
{
if (lineNumber == 0)
{
dt = CreateDataTable(line);
if (dt.Columns.Count == 0) return null;
}
else
{
bool isSuccess = CreateDataRow(ref dt, line);
if (!isSuccess) return null;
}
lineNumber++;
}
return dt;
}
public bool Write(DataTable dt)
{
FileInfo fi = new FileInfo(this._csvFile);
if (fi == null || !fi.Exists) return false;
if (dt == null || dt.Columns.Count == 0 || dt.Rows.Count == 0) return false;
StreamWriter writer = new StreamWriter(this._csvFile);
string line = string.Empty;
line = CreateTitle(dt);
writer.WriteLine(line);
foreach (DataRow dr in dt.Rows)
{
line = CretreLine(dr);
writer.WriteLine(line);
}
writer.Flush();
return true;
}
private DataTable CreateDataTable(string line)
{
DataTable dt = new DataTable();
foreach (string field in
line.Split(FormatSplit, StringSplitOptions.None))
{
dt.Columns.Add(field);
}
return dt;
}
private bool CreateDataRow(ref DataTable dt, string line)
{
DataRow dr = dt.NewRow();
string[] fileds=line.Split(FormatSplit, StringSplitOptions.None);
if (fileds.Length == 0 || fileds.Length != dt.Columns.Count) return false;
for (int i = 0; i < fileds.Length; i++)
{
dr[i] = fileds[i];
}
dt.Rows.Add(dr);
return true;
}
private char[] FormatSplit
{
get { return new char[] { ',' }; }
}
private string CreateTitle(DataTable dt)
{
string line = string.Empty;
for (int i = 0; i < dt.Columns.Count; i++)
{
line += string.Format("{0}{1}", dt.Columns[i].ColumnName,FormatSplit[0].ToString());
}
line.TrimEnd(FormatSplit[0]);
return line;
}
private string CretreLine(DataRow dr)
{
string line = string.Empty;
for (int i = 0; i < dr.ItemArray.Length;i++)
{
line += string.Format("{0}{1}", dr[i], FormatSplit[0].ToString());
}
line.TrimEnd(FormatSplit[0]);
return line;
}
}
}
foreach(string line in File.ReadAllLines("文件.csv"))
{
string[] arr= line.split(',');}
我的想法是,第一步:先要找到这个CSV文件!
再将CSV中文件中的数据读取出来,放到一个类似于dataset中;
再将dataset中的数据读取出来存到数据库中!
#账号: [2088001390707977],,,,,,,,,,
#起始日期: [2010年09月01日] 终止日期: [2010年09月30日],,,,,,,,,,这样的无用的东西,该怎么解决?
直接用sql导入的时候,有一个选项是跳过多少行再导入的.