Jet OleDB 4.0, Miss first digit randomly RRS feed

  • Question

  • I have very strange problem, I sense it is bug in Jet OleDB 4.0. Here is exact problem:

    1. I have a CSV File with some name and phone number.

    2. We have a OleDB.Jet4.0 base connection in C#/ page.

    3. We use Schema.ini file to make sure all column are read as TEXT field.

    4. We read almost 10 million record with 1000s of file and this code works fine, but one 3-4 occassion it doesn't read One record's first Character...

    The missing character is always the first character in Row and it occur anywhere in list, not the first or last row, no particular row number. The interesting fact is that when I change the Order of Row that was miss first character then it read file. Even I try to add couple of character on and record before and it still miss that rows character in that particular order, but works fine if I change the order of that row.

    Does anyone has such problem ?

    Sumit Gupta

    • Moved by Lisa Zhu Friday, June 22, 2012 2:03 AM ADO.NET related problem (From:Visual C# General)
    Monday, June 4, 2012 6:49 PM

All replies

  • Just found interesting fact, that when I put one extra space in first row of list, the file read properly. So this make me thing if there is an error in OleDB file read pointer that misses it after particular bytes?

    Sumit Gupta

    Monday, June 4, 2012 6:53 PM
  • I would check the encoding of the file.  I've seen strange errors like this if the character encoding is incorrect, or if the line separator isn't a standard CR+LF.

    Reed Copsey, Jr. -
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, June 4, 2012 7:04 PM
  • The file is created using Microsoft Excel 2010 version, so I don't thing character encoding is problem. Maybe I should pass on the file to you to check if it is the problem ?

    Here is the file [Record at line 192, Record Name: ROXCEYA RODGERS] miss first character, all other records works fine.

    I really don't think it is encoding, because if I put space before error line, it works. If I just change /search/replace some character that doesn't change file and resave it using excel it still gives error. If I change file size before the problem row it always works fine.

    Sumit Gupta

    Monday, June 4, 2012 7:33 PM
  • Anyone on this ...

    Sumit Gupta

    Friday, June 15, 2012 1:34 PM
  • Hi Vikasumit ,

    I am sorry for not being able to solve your problem in time.

    Since I am not expert in this area,I will involve some senior engineers into this case.They will help you with this issue.


    Lisa Zhu [MSFT]
    MSDN Community Support | Feedback to us

    • Edited by Lisa Zhu Thursday, June 21, 2012 3:48 AM
    Thursday, June 21, 2012 1:44 AM
  • Hey, Don't feel sorry. I know this is not a problem this is actually seems more like a bug to me. I never submit a bug report before so wasn't sure how to do it exactly. Maybe you can do the same easily ?

    Thanks for trying to help me.

    Sumit Gupta

    Thursday, June 21, 2012 5:54 AM
  • Did you try the ACE OLEDB Provider? AFAIK, Jet has been deprecated.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, June 22, 2012 1:35 PM
  • Nope, I didn't try it for few reasons:

    1. The code that produce error was rewritten 18-20 months ago, was working fine until this problem.

    2. We are on shared hosting, and the provider is not available on server. Not sure, if I can use it by putting assemblies in bin folder ?

    3. Actually don't know about it much and not sure if it can read CSV with same speed?

    4. Will it work on 64bit server ?

    Sumit Gupta

    Friday, June 22, 2012 3:17 PM
  • I suggested ACE because it's the most current database engine for Microsoft Access. I don't believe it works any different than Jet.

    It's highly unlikely that this a bug with Jet or the Text ISAM driver. Jet has been around for so long that I'm almost certain that type of problem would have been discovered. I would suspect that there is something in the data that is causing the issue. If you can identify the line in the text file you may want to take a peek with a hex editor. Text editors don't always reveal extra characters that may be present in the file.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, June 22, 2012 4:01 PM
  • BTW, one thing I did notice, and that Reed mentioned, is that line delimiter is a carriage return (0D). When Excel saves a file as CSV it uses a carriage return/line feed (0D0A) as the delimiter.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, June 22, 2012 4:33 PM
  • Well, As you said Jet is around for quite long, and I have read almost 10Millions records with it without any problem except 3-4 occassion where only and specifically only 1 record miss it's first byte. Not sure if it every get detected before, but it looks like even if it is bug it won't get fix as a new version is out there.

    I do not go with delimiter thing, as it is reading it fine anyways. I mean it didn't read one record at certain position, if you make a problem with Buffer of 4096 byte and if you read my file it still get error, so it has some miscalculation anyways in ADO seek pointer function I guess and hence a bug for me, unless it is prove otherwise.

    Sumit Gupta

    Friday, June 22, 2012 6:02 PM
  • Hi Sumit,

    Your question falls into the paid support category which requires a more in-depth level of support.  Please visit the below link to see the various paid support options that are available to better meet your needs.;en-us;offerprophone

    If Microsoft determines that a problem is the result of a defect in a Microsoft product, you will not be charged for that incident.


    Cathy Miller

    Monday, June 25, 2012 7:06 PM
  • I have hit the specified problem and after so much Googling could reach this thread. In my case, the CSV contains a little over 21K records and out of these 4 got this problem. Fortunately my first column contains strings of length 3 and hence I am able to identify them with a query LEN <> 3.

    Moving the records around helps, but - Suppose I move record no. x to somewhere else the new record in position x inherits that problem - May be the record at x - 1 is the troublemaker?

    In those 4 problematic records, I introduced a dummy blank at the beginning. Well now some completely new set of records start facing this problem.

    Currently I am overcoming the problem by having a dummy blank at the beginning of ALL the records and my code uses TRIM :(

    Monday, January 21, 2013 4:57 AM
  • You don't need to introduce blank space in all record. Just add blank space to any one record on top, like first row, and it fix it for all at least it did for me that way. I don't like the request of Cathy for Paid support, which clearly seems the bug in Parsing code which is so rare that nobody else notice it yet. And frankly I am not able to get that bug hit myself since my first complain. And we put more strict check on file format for client and give option with suggestion to add space and retry, or use small records per file to avoid it. Thus far it is good with workaround only.

    Sumit Gupta

    Monday, January 21, 2013 6:28 AM