使用Schema.ini文件的CSV文件中的日期缺少列 [英] Missing column of Dates in CSV file using Schema.ini file

问题描述

_{注意:回答我自己的问题以将来帮助他人.}

我正在使用OleDB读取CSV文件:

I'm reading a CSV file with OleDB:

DataTable csvTableSchema = new DataTable();

//Open the CSV
string csvFilePath = "C:\\temp\\WithDateColumn.csv";
var connString = string.Format(
    @"Provider=Microsoft.Jet.OleDb.4.0; Data Source={0};Extended Properties=""Text;HDR=YES;FMT=Delimited""",
    Path.GetDirectoryName(csvFilePath)
);

//To read the csv with DataTypes we specify the columns and their datatypes in the Schema.ini
//REF https://docs.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver

using (var conn = new OleDbConnection(connString))
{
    conn.Open();
    var query = "SELECT * FROM [" + Path.GetFileName(csvFilePath) + "]";
    using (var adapter = new OleDbDataAdapter(query, conn))
    {
        var ds = new DataSet("CSV File");
        adapter.Fill(ds);
        csvTableSchema = ds.Tables[0];
    }
}

这是CSV文件的样子:

This is what the CSV files look like:

这是schema.ini的示例

Here is a sample of the schema.ini

[ABC_20171128.csv]
Format = Delimited(|)
CharacterSet=ANSI
ColNameHeader=True
DateTimeFormat=dd MM yyyy hh:mm:ss

Col1 = Date DateTime
Col2 = AccountID Text
Col3 = SubAccount Text
Col4 = MarketCode Text
Col5 = SecurityCode Text
Col6 = Units Single

我尝试了不同的语法:

DateTimeFormat = dd MM yyyy hh:mm:ss

DateTimeFormat=dd MM yyyy hh:mm:ss

此异常失败:

System.Data.OleDb.OleDbException:'在文本文件规范'WithDateColumn.csv'中，DateTimeFormat选项无效.'

DateTimeFormat = dd MM yyyy

DateTimeFormat=dd MM yyyy

此语法不会引起错误，但date列为空.

This syntax doesn't cause an error but the date column is empty.

如何使用Schema.ini文件从CSV读取DateTime?

How do I read DateTimes from a CSV using the Schema.ini file?

推荐答案

结果证明OleDB在日期时间中使用 nn 而不是 mm .

Turns out OleDB uses nn instead of mm for minutes in DateTimes.

所以正确的语法是:

DateTimeFormat = dd MM yyyy hh:nn:ss

DateTimeFormat=dd MM yyyy hh:nn:ss

在互联网上藏匿的参考文献: http://forums.codeguru.com/showthread.php?333106-schema-ini-DateTimeFormat-legal-values

Ref stashed in the internet: http://forums.codeguru.com/showthread.php?333106-schema-ini-DateTimeFormat-legal-values

我在官方文档中看不到此内容:

I couldn't see this in the official documentation: https://docs.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver

这篇关于使用Schema.ini文件的CSV文件中的日期缺少列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！