Reading 10 million records Speedily

Question

Hi I am trying to fill the combobox control on my form by 10 million records using the datasource property & using teh displaymember & valuemember property. The problem is that it is taking a lot of time for this ( abt 8.75 minutes) Is there a quicker way to it ? I also tried teh datareader it helps but I also need the values associated with these records which r not accesible be datareader!!!( Maybe I am wrong) Plz heelp me ASAP

Answer 1

I did but it returns an error, 'Couldn't find installable ISAM'. So, I retain my connection but I append the IMEX. ;Extended Properties='text;IMEX=1; HDR=Yes;FMT=Delimited(,)' And still zeroes were vanished.

Answer 2

I believe you need to set the option in your connection string to force textual import rather than auto detecting it.

Provider=Microsoft.ACE.OLEDB.12.0;
    Data Source=c:\path\to\myfile.xlsx;
    Extended Properties=\"Excel 12.0 Xml;IMEX=1\";

The IMEX=1 extended property tells Excel to treat intermixed data as text.

Answer 3

But then, I am retrieving the ID from file using OleDB, and it turns out the the zeroes of the ID in the dataset was already ommitted.

Answer 4

Format the column in the Excel sheet where the IDs are stored as TEXT instead of numeric. If you are storing the values programmatically, put the value in a quote ''. Single quote is used as an escape character and it preserves all the values enclosed in quotes. This should solve your problem.