CSV文件導(dǎo)入到數(shù)據(jù)

1、usingSystem;usingSystem.Collections.Generic;usingSystem.Text;usingSystem.Data;usingSystem.Data.OleDb;usingSystem.Data.Sql;usingSystem.Data.SqlClient;usingSystem.IO;namespaceHPRSP.CommonDataObjectpublicclassCSVHelperprivatestringpath;privatestringfileName;/Z構(gòu)造函數(shù)/publicCSVHelper(stringfilePath,stringf

2、ileName)this.path=filePath;this.fileName=fileName;/從Csv中讀取數(shù)據(jù)/publicDataTableRead()returnRead(null);/通過(guò)文件流的方式來(lái)讀取CSV文件，默認(rèn)第一列為標(biāo)題列，列之間用逗號(hào)分隔/publicDataTableReadCsvFileToTable()returnReadCsvFileToTable(true,);/通過(guò)文件流的方式來(lái)讀取CSV文件，默認(rèn)列之間用逗號(hào)分隔/paramname=files0件名稱/param/paramname=HeadYes第一行是否為列標(biāo)題/param/returns/r

3、eturnspublicDataTableReadCsvFileToTable(boolHeadYes)returnReadCsvFileToTable(HeadYes,);/summary/通過(guò)文件流的方式來(lái)讀取CSV文件/paramname=files文件名稱/param/paramname=HeadYes第一行是否為列標(biāo)題/param/paramname=span分隔符/param/returns/returnspublicDataTableReadCsvFileToTable(boolHeadYes,charspan)/文件路徑和文件名stringfiles=path+fileName

4、;DataTabledt=newDataTable();StreamReaderfileReader=newStreamReader(files,Encoding.Default);try/是否為第一行(如果HeadYesDTRUE,則第一行為標(biāo)題行)intlsi=0;/列之間的分隔符charcv=span;while(fileReader.EndOfStream=false)stringline=fileReader.ReadLine();stringy=line.Split(cv);/第一行為標(biāo)題行if(HeadYes=true)/第一行if(lsi=0)for(inti=0;iy.Len

5、gth;i+)dt.Columns.Add(yi.Trim().ToString();lsi+;/從第二列開(kāi)始為數(shù)據(jù)列elseDataRowdr=dt.NewRow();for(inti=0;iy.Length;i+)dri=yi.Trim();dt.Rows.Add(dr);/第一行不為標(biāo)題行elseif(lsi=0)for(inti=0;iy.Length;i+)dt.Columns.Add(Col+i.ToString();lsi+;DataRowdr=dt.NewRow();for(inti=0;iy.Length;i+)dri=yi.Trim();dt.Rows.Add(dr);ca

6、tch(Exceptionex)throwex;finallyfileReader.Close();fileReader.Dispose();returndt;/從CSV中讀取數(shù)據(jù)/paramname=colNames名列表，可為空/param/priVateDataTableRead(stringcolNames)stringsql=CreateSql(colNames);returnExecuteTable(sql);/summary/通過(guò)執(zhí)行sql語(yǔ)句，從Csv中讀取數(shù)據(jù)/summary/paramname=sqlsql語(yǔ)句/param/returns/returnsprivateDat

7、aTableExecuteTable(stringsql)DataTabletable=newDataTable();using(OleDbConnectionconnection=newOleDbConnection(GetConnString(true)OleDbCommandcommand=connection.CreateCommand();command.CommandText=sql;OleDbDataAdapteradapter=newOleDbDataAdapter(command);adapter.Fill(table);returntable;#region私有方法priv

8、atestringCreateSql(stringcolNames)stringcols=*;if(null!=colNames&colNames.Length0)StringBuildercolList=newStringBuilder();for(inti=0;icolNames.Length-1;i+)colList.AppendFormat(0,colNamesi);colList.AppendFormat(0,colListcolList.Length);cols=colList.ToString();returnstring.Format(select0from1,cols,fil

9、eName);privatestringGetConnString(boolIsHeaderRow)returnstring.Format(Provider=Microsoft.Jet.OLEDB.4.0;ExtendedProperties=Text;FMT=Delimited;HDR=0;datasource=1,IsHeaderRow?Yes:No,path);#endregion在D:VSS2005政務(wù)HPRSP_黃埔海關(guān)風(fēng)險(xiǎn)管理平臺(tái)源代碼CommonDataObject下增加了CSV文件導(dǎo)入數(shù)據(jù)庫(kù)的操作類：CSVHelper.cs.用法很簡(jiǎn)單，只要傳入CSV文件的文件路徑和文件名就行

10、。下面是一個(gè)例子：stringconn=ConfigurationManager.ConnectionStringsHPRSP.ToString();/獲取并保存上傳的文件if(fuUpload.PostedFile!=null)fuUpload.PostedFile.SaveAs(Server.MapPath()+fuUpload.FileName);else!);/thrownewException(請(qǐng)選擇需上傳的文件!);/構(gòu)造函數(shù)中傳入文件路徑和文件名HPRSP.CommonDataObject.CSVHelperCsvFile=newHPRSP.CommonDataObject.CS

11、VHelper(Server.MapPath()+,fuUpload.FileName)/調(diào)用Read()方法讀取文件，具體的實(shí)現(xiàn)過(guò)程請(qǐng)查看類：CSVHelper.csDataTabledt=CsvFile.Read();foreach(DataRowdrindt.Rows)stringStoreName=uspZFPG_INDEX_UPDATE;ParamCollectionparmsInsert=newParamCollection(下面/下面內(nèi)為列名，即第一行對(duì)應(yīng)的列名下面如下圖的測(cè)試數(shù)據(jù)nrAECDE11關(guān)瓦，指標(biāo)ID目標(biāo)值完咸值是習(xí)達(dá)標(biāo)f10001009003100022001200

12、4100031002001/,dr關(guān)區(qū)parmsInsertAdd(newParam(CustomsCode,ToString(),ParamType.Varchar,4,ParameterDirection.Input);廠一”dr指標(biāo)ID_parmsInsertAdd(newParam(IndexID,ToString(),ParamTypeVarchar,10,ParameterDirectionInput);”dr目標(biāo)值parmsInsertAdd(newParam(StandardValue,ToString(),ParamTypeVarchar,50,ParameterDirectionInput);ParamTypeVarchar,50,ParameterDirectionInput);drparmsInsertAdd(newParam(Value,完成值_ToString(),ParamTypeVarchar,50,ParameterDirectionInput);”dr是否達(dá)標(biāo)parmsInsertAdd(newParam(IsReachStandard,ParamTypeNvarchar,50,ParameterDirectionInput);trySqlDatabaseExecuteNonQuery(conn,CommandTypeStore