RT: 大家好! 我现在需要把.csv档案的资料读取到DataTable 查资料稍微了解了一下.csv 存文本文档,数据列之间以英文逗号分隔 但在读取时还是需要注意一些规则 1.某数据列中本身有逗号 2.有引号 3.等等 有谁手上有代码,并且通过测试的 可否发给我,mail:[email protected] 谢谢!
调试欢乐多
StreamReader sr = new StreamReader("c:\\1.txt");
while (!sr.EndOfStream)
{
string s = sr.ReadLine();
string[] arr = s.Split(',');
//把数组arr写到datatable
split就行了。
为什么还需要特别的符号?首先不管每列中的资料结构是怎样的,读取csv档案时用Split到N个逗号,就表示有N+1列然后再逐行的对没列去解析所以我的想法是不需要用这些特殊的符号了不知道思路对?
"1,",2,3,4,5文件2:(五个字段,每个后面都有逗号)
"1,","2,","3,","4,","5,"
using System;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.Text.RegularExpressions;
using System.Web;
using System.IO;
using System.Data.OleDb;
using System.Data;
using System.Xml;
using System.Collections;
using System.DirectoryServices;
using System.Security.Cryptography.X509Certificates;
下面是自己写的,本来想扩充成可同样解析html的方法,没完成
/// <summary>
/// 读取csv新 示例: dt = Class1.ReadFromCSV(pathfile, 0, new string[] { "," }, new string[] { "\n" }, new string[][] { new string[] { "\"", "\"" } });
/// </summary>
/// <param name="source">数据源,文件或字符串</param>
/// <param name="type">类型0,是读文件,1是读字符串,默认是0</param>
/// <param name="spits">分隔字符串,默认是,</param>
/// <param name="breaks">换行字符串,默认是\n</param>
/// <param name="pattern">保护匹配对,默认是""</param>
/// <returns>一个datatable或者null</returns>
public static DataTable ReadFromCSV(string source, int type, string[] spits, string[] breaks, string[][] pattern)
{
///声明公共变量
DataTable dt = new DataTable();
int rownum = 0; ///逻辑主体
if (type == 0)
{
//读文件
if (!File.Exists(source))
{
return null;
}
else
{
StreamReader streamReader = new StreamReader(source, System.Text.Encoding.GetEncoding("gb2312"));
string tempstr = "";
bool bl;
int m = 0;
while (streamReader.Peek() > -1)
{
string[] str = streamReader.ReadLine().ToString().Split(spits, System.StringSplitOptions.None); if (tempstr == "")
{
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
m = 0;
rownum += 1;
}
for (int i = 0; i < str.Length; i++)
{
if (i == 0 && m != 0)
{
tempstr += "\n" + str[i];
}
else
{
tempstr += str[i];
}
for (int k = 0; k < pattern.Length; k++)
{
int x = 0;
int y = 0;
if (pattern[k][0] == pattern[k][1])
{
x = DataProcess.FindCount(tempstr, pattern[k][0]);
x = x % 2;
bl = !Convert.ToBoolean(x);
}
else
{
x = DataProcess.FindCount(tempstr, pattern[k][0]);
y = DataProcess.FindCount(tempstr, pattern[k][1]);
bl = (x == y);
}
if (bl || tempstr.Length == 0)
{
bl = (tempstr.StartsWith(pattern[k][0]) && tempstr.EndsWith(pattern[k][1]));
}
if (bl || (x == 0 && y == 0))
{
//新建数据列,填充数据
if (dt.Columns.Count <= m)
{
//DataColumn dc = new DataColumn("col" + m.ToString(), typeof(String));
dt.Columns.Add(new DataColumn());
}
if (tempstr.Length != 0)
{
if (tempstr.StartsWith(pattern[k][0]))
{ tempstr = tempstr.Substring(1, tempstr.Length - 2).ToString();
} }
tempstr = tempstr.Replace("\"\"", "\"");
dt.Rows[rownum - 1][m] = tempstr.ToString();
tempstr = "";
m += 1;
break; }
} }
}
streamReader.Close();
streamReader.Dispose(); if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (dt.Rows[0][i] != null && dt.Rows[0][i].ToString().Length != 0 && !dt.Columns.Contains(dt.Rows[0][i].ToString()))
{
dt.Columns[i].ColumnName = dt.Rows[0][i].ToString();
}
}
dt.Rows[0].Delete();
} return dt;
} }
else if (type == 1)
{
//读字符串
return dt;
}
else
{
//返回空
return null;
}
} /// <summary>
/// 读取csv新
/// </summary>
/// <param name="source">源文件</param>
/// <returns></returns>
public static DataTable ReadFromCSV(string source)
{
return ReadFromCSV(source, 0, new string[] { "," }, new string[] { "\n" }, new string[][] { new string[] { "\"", "\"" } });
}下面是OLE的方法,不过会有弱点,好处是执行效率高:
/// <summary>
/// 读csv到datatable中,没有做太好的异常处理
/// </summary>
/// <param name="path">文件路径</param>
/// <returns>datatable</returns>
public static System.Data.DataTable ReadCsv(string path)
{
int spits = path.LastIndexOf("\\");
string filePath = path.Substring(0, spits + 1);
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='text;HDR=YES;FMT=Delimited;'";
string commandText = "select * from " + path.Replace(filePath, "");
OleDbConnection olconn = new OleDbConnection(connStr);
olconn.Open();
OleDbDataAdapter odp = new OleDbDataAdapter(commandText, olconn);
System.Data.DataTable dataTable = new System.Data.DataTable();
odp.Fill(dataTable);
return dataTable;
}
/// 查找一个字符串在另一个字符串中的长度
/// </summary>
/// <param name="str">源字符串</param>
/// <param name="pattern">要查找的字符串</param>
/// <returns>次数</returns>
public static int FindCount(string str, string pattern)
{
if (pattern.Length == 0 || pattern == null || str == null)
{
return 0;
}
else
{
int i = str.Length - str.Replace(pattern, pattern.Substring(1)).Length;
return i;
} }