将csv加载到oleDB中，并将所有推断的数据类型强制为字符串 [英] Load csv into oleDB and force all inferred datatypes to string

问题描述

我试图使用oledb加载csv文件到数据表。

这没有问题，但不幸的是，看起来是数字的字段之一有一个字符串值在3％的字段，所以没有填充。

因为im转换csv到xml我真的不在乎推断数据类型，只需要数据在字符串，因为我可以在后来的Linq2XMl阶段。

我希望能够在连接字符串中这样做。

我不想只是复制表，使用我想要的数据类型的新列来设置它，然后将数据写入它，因为这将涉及加载csv文件两次。

$

我当前的连接字符串是

Microsoft.Jet.OLEDB.4.0; Data Source =+ thefile.DirectoryName +;扩展属性='text; HDR =是; FMT =分隔';

解决方案

进行了一些研究，答案是使用schema.ini，但为您的数据集随时生成它。

http://msdn.microsoft.com/en-us/library/ms709353（ VS.85）.aspx

包含所需的信息。
构造模式：

  public static void ConstructSchema（FileInfo theFile）
 {
 StringBuilder schema = new StringBuilder（）; 
 DataTable data = LoadCSV（theFile）; 
 schema.AppendLine（[+ theFile.Name +]）; 
 schema.AppendLine（ColNameHeader = True）; 
 for（int i = 0; i  {
 schema.AppendLine（col+（i + 1）.ToString（） =+ data.Columns [i] .ColumnName +Text）; 
} 
 string schemaFileName = theFile.DirectoryName + @\Schema.ini; 
 TextWriter tw = new StreamWriter（schemaFileName）; 
 tw.WriteLine（schema.ToString（））; 
 tw.Close（）; 
} 
  

将csv作为数据类型加载

  public static DataTable LoadCSV（FileInfo theFile）
 {
 string sqlString =Select * FROM [+ theFile.Name +];; 
 string conStr =Provider = Microsoft.Jet.OLEDB.4.0;数据源=
 + theFile.DirectoryName +; +扩展属性='文本; HDR =是;'; 
 DataTable theCSV = new DataTable（）; 
 
 using（OleDbConnection conn = new OleDbConnection（conStr））
 {
 using（OleDbCommand comm = new OleDbCommand（sqlString，conn））
 {
使用（OleDbDataAdapter adapter = new OleDbDataAdapter（comm））
 {
 adapter.Fill（theCSV）; 
} 
} 
} 
返回CSV; 
} 
  

转换为xml

  public static XElement GetXMLFromCSV（FileInfo theFile，string rootNodeName，string itemName）
 {
 XElement retVal; 
 DataTable数据; 
 data = CrateCsvAndSchema（theFile）; 
 DataSet ds = new DataSet（rootNodeName）; 
 data.TableName = itemName; 
 ds.Tables.Add（data）; 
 retVal = XElement.Parse（ds.GetXml（））; 
 return retVal; 
} 
  

Im trying to load a csv file into a datatable using oledb.

This is no problem but unfortunately one of the fields which looks numeric has a string value in about 3% of the fields and so is not being populated.

because im converting the csv into xml i really don't care about inferring datatypes and simply need the data in a string as i can cast it later in a Linq2XMl phase.

I am hoping to be able to do this in the connection string.

I don't want to just copy the table, set it up with new columns with the datatype I want and then write the data into it because that would involve loading the csv file twice.

any ideas?

my current connection string is

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + thefile.DirectoryName + ";Extended Properties='text;HDR=Yes;FMT=Delimited'";

解决方案

Did some researchand the answer is use a schema.ini but generate it on the fly for your dataset.

http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx

contains the info required. to construct the schema:

   public static void ConstructSchema(FileInfo theFile)
    {
        StringBuilder schema = new StringBuilder(); 
        DataTable data = LoadCSV(theFile); 
        schema.AppendLine("[" + theFile.Name + "]");
        schema.AppendLine("ColNameHeader=True"); 
        for (int i = 0; i < data.Columns.Count; i++)
        {
            schema.AppendLine("col" + (i + 1).ToString() + "=" + data.Columns[i].ColumnName + " Text");
        }   
        string schemaFileName = theFile.DirectoryName + @"\Schema.ini";
        TextWriter tw = new StreamWriter(schemaFileName);   
        tw.WriteLine(schema.ToString());
        tw.Close();  
    }

to load the csv as datatable

public static DataTable LoadCSV(FileInfo theFile)
    {   
        string sqlString = "Select * FROM [" + theFile.Name + "];";
        string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
            + theFile.DirectoryName + ";" + "Extended Properties='text;HDR=YES;'";
        DataTable theCSV = new DataTable();

        using (OleDbConnection conn = new OleDbConnection(conStr))
        {
            using (OleDbCommand comm = new OleDbCommand(sqlString, conn))
            {
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(comm))
                {
                    adapter.Fill(theCSV);
                }
            }
        }
        return theCSV;
    }

to convert to xml

 public static XElement GetXMLFromCSV(FileInfo theFile, string rootNodeName, string itemName)
    {
        XElement retVal;
        DataTable data;
        data = CrateCsvAndSchema(theFile); 
        DataSet ds = new DataSet(rootNodeName);
        data.TableName = itemName;
        ds.Tables.Add(data); 
        retVal = XElement.Parse(ds.GetXml());  
        return retVal;
    }

这篇关于将csv加载到oleDB中，并将所有推断的数据类型强制为字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！