none
Read tab-delimited flat file with Jet engine

    Question

  • Hi,

    These codes should work well to read my tab-delimited text file. But it does not do exactly the right things. Let's say I have three fields separated by tab in every line. It read three fields into one field. It seems that the engine does not recognize tab as the delimiter.

            string dataSourcePath = @"C:\";  
            string dataFileName = "tab-delimited.txt";  
            string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dataSourcePath + @";Extended Properties=""text;HDR=NO;FMT=TabDelimiter""";  
            OleDbConnection conn = new OleDbConnection(connString);  
            OleDbCommand cmd = conn.CreateCommand();  
            cmd.CommandText = String.Format("SELECT * FROM [{0}]", dataFileName);  
            DataSet ds = new DataSet();  
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);  
            da.Fill(ds); 

    How can I do?

    Thanks.
    • Moved by nobugzMVP Tuesday, July 15, 2008 4:06 PM Not a bcl question
    Tuesday, July 15, 2008 9:18 AM

Answers

  • Aha, I got it. I found the definition body should start from the 2nd line just leaving the 1st line blank space or even  anything else.

    If you put one name of the file (enclosed in square brackets) in the 1st line, the ODBC Text Driver will not touch it.  It will skip all the definition block of the file, I think. So the engine will process this file with the default setting in the system registry.

    Too mysterious!  It's worse no existing documentation mentions this point.

    • Marked as answer by Xiaofeng Wang Thursday, July 17, 2008 5:43 PM
    Wednesday, July 16, 2008 4:15 PM

All replies

  • I believe you need to modify the regisry or create a schema.ini file. Check out this link : http://msdn.microsoft.com/en-us/library/ms974559.aspx and look for the bits on tab delimited and schema.ini.

    LJ

    Tuesday, July 15, 2008 9:32 AM
  • Hi John

    Thanks for your response. 

    It's fine if I modify the registry value(HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\Format) from CSVDelimited to TabDelimited.
    But it will reproduce the wrong behavior as before if I only create an schema.ini file within the same directory. It seems the schema.ini doesnot override the setting in the registry.

    My schema.ini is:

    [tab-delimited.txt]  
    Format=TabDelimited 

    To modify the registry will be a bit tedious bcz maybe I need to process multiple formats, tab-delimited, comma-delimited, pipe-delimited.... Specially, I won't have permission to access the registry.

    I wonder why the setting "FMT=TabDelimited" in the connection string is effective? It would be most convenient if it works.

    Can you tell me more info about this?

    Thanks again.
    Tuesday, July 15, 2008 10:03 AM
  • Jinglecat,

    I've not had the need to read a tab-delimited file using this method myself, although I've used it with CSV and Excel before. The "FMT=TabDelimited" looks like it should work and would be your best option. Have a try and see ....



    LJ.
    Tuesday, July 15, 2008 1:15 PM
  • John

    The "FMT=TabDelimited
    " in the connection string does not work as expected, and neither does the schema.ini file.

    Have anybody adopted them successfully?
    Tuesday, July 15, 2008 5:26 PM
  • JingleCat,

    I got it working here. The code I used was this:


                string dataSourcePath = @"C:\Users\John\Documents";  
                string dataFileName = "tab-delimited.txt";  
                string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dataSourcePath + @";Extended Properties=""text;HDR=YES;FMT=Delimited""";  
                OleDbConnection conn = new OleDbConnection(connString);  
                OleDbCommand cmd = conn.CreateCommand();  
                cmd.CommandText = String.Format("SELECT * FROM [{0}]", dataFileName);  
                DataSet ds = new DataSet();  
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);  
                da.Fill(ds);  
     

    It didn't seem to matter whether I had FMT=Delimited or FMT=TabDelimited. In the same directory I had a schema.ini file with this in:

    [tab-delimited.txt]
    Format=TabDelimited

    Check that you have the format correct, at one point I didn't and I got the same results as you.

    Tuesday, July 15, 2008 10:53 PM
  • John,

    I'm sure my schema.ini is writen correctly.  But it still cant override the registry's default setting.

    I posted my codes, a sample file and schema.ini here:
    http://www.cnblogs.com/Files/Jinglecat/ReadSeparatedValuesTest.rar

    Can you download them and run a test on your machine?

    Thanks a lot.
    Wednesday, July 16, 2008 6:47 AM
  • Ok, very strange indeed. I downloaded your source and it didn't work on my pc either. After trying a variety of things I used winmerge to compare your schema.ini against mine and noticed that you had an extra line in yours (Possibly with some extraneous non-alphanumeric chars - I can't tell now as I've overwritten it). So I copied my version over and it all worked.

    Either way the problem is definitely with the schema.ini - recreate it if necessary and make sure the latest version from your source folder is in the bin folder ("copy only when changed" often seems to be a bit flaky especially in VS2008 so use "copy always").

    LJ.
    • Proposed as answer by Xiaofeng Wang Wednesday, July 16, 2008 10:23 AM
    • Unproposed as answer by Xiaofeng Wang Wednesday, July 16, 2008 10:23 AM
    Wednesday, July 16, 2008 9:55 AM
  • Aha, I got it. I found the definition body should start from the 2nd line just leaving the 1st line blank space or even  anything else.

    If you put one name of the file (enclosed in square brackets) in the 1st line, the ODBC Text Driver will not touch it.  It will skip all the definition block of the file, I think. So the engine will process this file with the default setting in the system registry.

    Too mysterious!  It's worse no existing documentation mentions this point.

    • Marked as answer by Xiaofeng Wang Thursday, July 17, 2008 5:43 PM
    Wednesday, July 16, 2008 4:15 PM
  • That is odd - I'm sure I had just the 2 lines one with the file name in brackets and one with the tabdelimited bit. Unfortunately I don't have access to my laptop at the moment. Glad you got it working anyway!
    Wednesday, July 16, 2008 10:31 PM