Why?I have to read a csv using System.Data.OleDb.
DbDataReader
. My understanding is that the Sylvan CSV data reader, can present itself as a DbDataReader for those want to use ADO.NET code.It is not OLEDB that is slow. It is the ACE database driver exposing an OLEDB interface that is slow. You are basically at the mercy of Microsoft. Considering that they abandoned ACE and went with JET, you'll likely not get much help there. The ACE database driver was written primarily for use with Access databases. It was only tangentially, expanded by the Excel development team to also accept a few other data formats so that data imports could be performed. It (as well as JET) was not meant to be a way for you to use a CSV files (or Excel files) as a full blown database.Is there any way to speed up the read speed?
Again, why do you need to use OLEDB? If all the column types are text, then theI have to read a csv using OleDb.
The number of columns in the csv file is 43. All are text type.
TextFieldParser
should be all you need, since you generate your own row numbers anyway.TextFieldParser
even easier.var parser = new TextFieldParser(csvFile)
{
parser.TextFieldType = FieldType.Delimited;
parser.Delimiters = new[] { "," };
parser.CommentTokens = new[] { "#" };
};
// Read the first row to find out the ID column so that we can sort later
var headers = parser.ReadFields();
var idColumn = headers.IndexOf("ID");
var sortedRowsWithRowNumber =
GetRows(parser)
.OrderBy(row => row[idColumn])
.Select((row, index) => new { RowNumber = index + 1, Data = row})
.ToList();
foreach(var row with sortedRowsWithRowNumber)
{
// Print out row number and data in column 13 as example of using the data
Console.WriteLine($"{row.RowNumber} : {row.Data[12]})
}
<IEnumerable<string[]> GetRows(TextFieldParser parser)
{
while (!parser.EndOfData)
yield return parser.ReadFields();
}
But why are you migrating?I am migrating the existing sqlite( System.Data.SQLite.dll ) to oledb( System.Data.Oledb.dll ).
This is linked to System.Data.Common (System.Data.SQLite.dll & System.Data.Oledb.dll).
My PC : Windows10 (64bit)The JET driver is only available when running 32-bit. That is why you are getting that error about the driver not being registered.
There is no need to install it. It comes built into .NET Framework. You need to make sure that you are building for 32-bit. Show us a screenshot of your build options page.My PC : Windows10 (64bit)
Visual Studio2019, C#.NET compile AnyCPU & 32bit
No matter how much I tried to install the JET driver, it didn't install.
So you are migrating the code. Why are you migrating the data from a perfectly good database to a CSV file?I have to do a code migration.
Other than moving the .CSV file to a faster drive, turning off all antivirus, and upgrading your CPU, no.Is there any way to speed it up?
No.Does switching to a JET driver speed it up?
"Drive" as in hard drive (HD) or solid state drive (SSD).Your Answer) Other than moving the .CSV file to a faster drive
==> what is that drive?