Tuesday, July 15, 2008 9:18 AMHi,
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.
How can I do?
- Moved by nobugzMVP Tuesday, July 15, 2008 4:06 PM Not a bcl question
Tuesday, July 15, 2008 9:32 AMI 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.
Tuesday, July 15, 2008 10:03 AMHi 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:
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?
Tuesday, July 15, 2008 1:15 PMJinglecat,
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 ....
Tuesday, July 15, 2008 5:26 PMJohn
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 10:53 PMJingleCat,
I got it working here. The code I used was this:
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:
Check that you have the format correct, at one point I didn't and I got the same results as you.
- Edited by Laughing John Tuesday, July 15, 2008 10:59 PM !!!!
Wednesday, July 16, 2008 6:47 AMJohn,
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:
Can you download them and run a test on your machine?
Thanks a lot.
Wednesday, July 16, 2008 9:55 AMOk, 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").
Wednesday, July 16, 2008 4:15 PMAha, 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 10:31 PMThat 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!