在 .Net 中，如何使用 OleDB 以我想要的格式从 CSV 导入值？
我有一个 CSV 文件，其中有一列包含看起来像整数的字符串。 也就是说，它们应该作为字符串处理，但由于它们是数字，因此它们似乎作为整数导入（去掉前导零）。
示例数据：
- 0000000000079
- 0000999000012
- 0001002000005
- 0004100000007
我看到的问题是最后一个示例数据点作为 DBNull.Value 出现。 我假设这是因为 OleDB 将该列视为整数（数据点也没有前导零）并且 0004100000007 大于最大整数值。
有没有办法说“列 [0] 是一个字符串，不要将其读取为整数”？ 读取数据时？
我当前使用的代码是：
OleDbConnection dbConn = new OleDbConnection(SourceConnectionString);
OleDbCommand dbCommand = new OleDbCommand("SELECT * FROM test.csv", dbConn);
dbConn.Open();
OleDbDataReader dbReader = dbCommand.ExecuteReader();
while (dbReader.Read())
{
if (dbReader[0] != DBNull.Value)
{
// do some work
}
}
I have a CSV file that has a column that contains strings that look like integers. That is they should be dealt with as strings, but since they are numbers they appear to be imported as integers (dropping off the leading zeroes).
Example Data:
- 0000000000079
- 0000999000012
- 0001002000005
- 0004100000007
The problem I'm seeing is that the last example data point comes through as DBNull.Value. I'm assuming this is because OleDB is treating that column as an integer (the data points come through without their leading zeroes also) and that 0004100000007 is greater than the largest integer value.
Is there some way to say "column [0] is a string, don't read it as an integer"? When reading the data?
The code I am currently using is:
OleDbConnection dbConn = new OleDbConnection(SourceConnectionString);
OleDbCommand dbCommand = new OleDbCommand("SELECT * FROM test.csv", dbConn);
dbConn.Open();
OleDbDataReader dbReader = dbCommand.ExecuteReader();
while (dbReader.Read())
{
if (dbReader[0] != DBNull.Value)
{
// do some work
}
}
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
当您需要将列读取为字符串时，请尝试在阅读器中使用 GetString() 方法：
Try to use GetString() method in the reader when you need to read the column as string:
您可以控制出口流程吗？ 如果是这样，数据可以导出到 CSV 并在字符串项周围加上引号吗？
如果这是一项工作，则只需使用 Integration Services 将文件导入到预定义的 SQL 表中，但我怀疑这将是一项重复任务。
Do you have control of the export process? If so can data be exported to CSV with quotes around the string items?
If this is a one of job then just import the file into a predfined SQL table using Integration Services, but I suspect this will be a recurring task.
有一个 Schema.ini 文件需要用于指定有关该文件的信息。 它包括字段类型和长度、是否有列标题以及字段分隔符是什么。
这是有关它的 MSDN 信息。
http://msdn.microsoft.com/en-us/library/ms709353。 ASPX
There's a Schema.ini file that needs to be used to specify the info about the file. It includes field types and lengths, whether there are column headers and what the field delimiter is.
Here's the MSDN Info on it.
http://msdn.microsoft.com/en-us/library/ms709353.aspx
您是否尝试过使用此 CSV 阅读器？ 一般来说，它是非常受尊重的。 或许可以尝试一下...
Have you tried using this CSV reader? It is generally very respected. Perhaps give it a go...
我不是专家，但你能处理固定文件格式吗？
http://csharptutorial.com/blog/exclusive-how-to-export-a-datatable-to-a-fixed-file-format-the-easy-way-using- odbc-or-jet-engine/
I'm no expert but do you cope with fixed file format ?
http://csharptutorial.com/blog/exclusive-how-to-export-a-datatable-to-a-fixed-file-format-the-easy-way-using-odbc-or-jet-engine/