cvs转datatable_CSV文件解析到DataTable

1 public classCsvParsingHelper2 {3 ///

4 ///将csv文件的数据转成datatable5 ///

6 /// csv文件路径

7 /// 是否将第一行作为字段名

8 ///

9 public static DataTable CsvToDataTable(string csvfilePath, boolfirstIsRowHead)10 {11 DataTable dtResult = null;12 if(File.Exists(csvfilePath))13 {14 string csvstr =File.ReadAllText(csvfilePath, Encoding.Default);15 if (!string.IsNullOrEmpty(csvstr))16 {17 dtResult =ToDataTable(csvstr, firstIsRowHead);18 }19 }20 returndtResult;21 }22

23 ///

24 ///将CSV数据转换为DataTable25 ///

26 /// 包含以","分隔的CSV数据的字符串

27 /// 是否将第一行作为字段名

28 ///

29 private static DataTable ToDataTable(string csv, boolisRowHead)30 {31 DataTable dt = null;32 if (!string.IsNullOrEmpty(csv))33 {34 dt = newDataTable();35 string[] csvRows = csv.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);36 string[] csvColumns = null;37 if (csvRows != null)38 {39 if (csvRows.Length > 0)40 {41 //第一行作为字段名,添加第一行记录并删除csvRows中的第一行数据

42 if(isRowHead)43 {44 csvColumns = FromCsvLine(csvRows[0]);45 csvRows[0] = null;46 for (int i = 0; i < csvColumns.Length; i++)47 {48 dt.Columns.Add(csvColumns[i]);49 }50 }51

52 for (int i = 0; i < csvRows.Length; i++)53 {54 if (csvRows[i] != null)55 {56 csvColumns =FromCsvLine(csvRows[i]);57 //检查列数是否足够,不足则补充

58 if (dt.Columns.Count

73 returndt;74 }75 ///

76 ///解析一行CSV数据77 ///

78 /// csv数据行

79 ///

80 public static string[] FromCsvLine(stringcsv)81 {82 List csvLiAsc = new List();83 List csvLiDesc = new List();84

85 if (!string.IsNullOrEmpty(csv))86 {87 //顺序查找

88 int lastIndex = 0;89 int quotCount = 0;90 //剩余的字符串

91 string lstr = string.Empty;92 for (int i = 0; i < csv.Length; i++)93 {94 if (csv[i] == '"')95 {96 quotCount++;97 }98 else if (csv[i] == ',' && quotCount % 2 == 0)99 {100 csvLiAsc.Add(ReplaceQuote(csv.Substring(lastIndex, i -lastIndex)));101 lastIndex = i + 1;102 }103 if (i == csv.Length - 1 && lastIndex

111 lastIndex = 0;112 quotCount = 0;113 string revStr =Reverse(lstr);114 for (int i = 0; i < revStr.Length; i++)115 {116 if (revStr[i] == '"')117 {118 quotCount++;119 }120 else if (revStr[i] == ',' && quotCount % 2 == 0)121 {122 csvLiDesc.Add(ReplaceQuote(Reverse(revStr.Substring(lastIndex, i -lastIndex))));123 lastIndex = i + 1;124 }125 if (i == revStr.Length - 1 && lastIndex

131 }132 string[] tmpStrs =csvLiDesc.ToArray();133 Array.Reverse(tmpStrs);134 csvLiAsc.AddRange(tmpStrs);135 }136 }137

138 returncsvLiAsc.ToArray();139 }140 ///

141 ///反转字符串142 ///

143 ///

144 ///

145 private static string Reverse(stringstr)146 {147 string revStr = string.Empty;148 foreach (char chr instr)149 {150 revStr = chr.ToString() +revStr;151 }152 returnrevStr;153 }154 ///

155 ///替换CSV中的双引号转义符为正常双引号,并去掉左右双引号156 ///

157 /// csv格式的数据

158 ///

159 private static string ReplaceQuote(stringcsvValue)160 {161 string rtnStr =csvValue;162 if (!string.IsNullOrEmpty(csvValue))163 {164 //首尾都是"

165 Match m = Regex.Match(csvValue, "^\"(.*?)\"$");166 if(m.Success)167 {168 rtnStr = m.Result("${1}").Replace("\"\"", "\"");169 }170 else

171 {172 rtnStr = rtnStr.Replace("\"\"", "\"");173 }174 }175 returnrtnStr;176

177 }178 }