Ack... I remember now. Yes, you're right, Noonie. It's a combination of things:
According to the KB Article: *Setting IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric. * So it's a combination of IMEX=1 and increasing the TypeGuessRows in the registry to get the data-types right. On Tue, Feb 22, 2011 at 12:00 PM, noonie <[email protected]> wrote: > Nathan, > > The IMEX=1 setting won't get the driver to treat "all" fields as strings > only "mixed" data in a column. If the scanned rows show no mixed data types > the driver will still go ahead an "guess" a data type other than string. > > See: <http://support.microsoft.com/kb/194124> > > -- > Regards, > noonie > > > On 22 February 2011 12:39, Nathan Schultz <[email protected]> wrote: > >> I've used OleDB several times in production systems, and so long as you >> know of a few quirks it's fine. >> It determines data-types from the first 8 rows by default (not 5) and I >> think there is a registry hack that can increase this. >> >> Personally I just add IMEX=1 to the connection string during read, and >> this tells OleDB to import all fields as strings. I can then use my >> application to cast the fields to their appropriate types. >> >> >> >> On Mon, Feb 21, 2011 at 3:49 PM, <[email protected]> wrote: >> >>> Hi, >>> >>> Oledb will give you loads of problems, the data types in the spreadsheet >>> are defined by the best match of the first 5 rows or so. Really not good. >>> >>> If you were just reading the file then nexcel is my preferred way and it >>> works on asp.net with no hassle. >>> >>> But as you are updating there isn't a simple solution at all. >>> >>> Davy >>> "When all you have is a hammer, every problem looks like a nail." I feel >>> much the same way about xml >>> >>> -----Original Message----- >>> From: "etmilis" <[email protected]> >>> Sender: [email protected] >>> Date: Mon, 21 Feb 2011 16:02:27 >>> To: 'ozDotNet'<[email protected]> >>> Reply-To: ozDotNet <[email protected]> >>> Subject: RE: Excel in .NET (C# or VB) >>> >>> Thanks Craig and Arjang, >>> >>> Concern noted. >>> >>> We are asked to automate/integrate files (i.e. invoice, inventory, etc.) >>> received from customer (in Excel via email) with internal system and need >>> to >>> update some databases/tables too. >>> We will also need to send back the updated Excel file (original file + >>> added/updated columns) to the customers. >>> >>> It looks like there are 2 ways to do it, using the Excel object model or >>> the >>> OLEDB, though I am leaning more to the object model. >>> So, is it a good design if we create a service or a .net assembly with >>> scheduled job to it? The frequency is pretty low, a few times in a day >>> during business hours only. >>> >>> Cheers, >>> Etmilis >>> >>> -----Original Message----- >>> From: [email protected] [mailto: >>> [email protected]] >>> On Behalf Of Arjang Assadi >>> Sent: Monday, 21 February 2011 3:37 PM >>> To: ozDotNet >>> Subject: Re: Excel in .NET (C# or VB) >>> >>> Hi Etmilis, >>> >>> as Craig said ( also from personal experience ), do not try reading and >>> writing excel files on the server, there is no end to problems that need >>> to >>> be solved. >>> >>> What is the original problem that you think it requires reading and >>> writing >>> to Excel Files? >>> >>> Regards >>> >>> Arjang >>> >>> On 21 February 2011 15:10, etmilis <[email protected]> wrote: >>> > Hi Everyone, >>> > >>> > In the current DNA with .NET, is it much easier now to deal with EXCEL? >>> > Is COM still in the game? >>> > >>> > What I am after is reading from and writing to an EXCEL file(s). >>> > Also will it be possible to do it without installing EXCEL at all, for >>> > example just referencing some of the EXCEL assemblies??? >>> > >>> > Thanks and Regards, >>> > Etmilis >>> > >>> > >>> >>> >> >
