Help with a OleDB connection string for excel files

by

Unfortunately, you can’t set ImportMixedTypes or TypeGuessRows from the connection string since those settings are defined in the registry. For the ACE OleDb driver, they’re stored at 

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

in the registry. So, you can simplify your connection string to:

conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Nick\Desktop\Pricing2.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=1;""";

Once you set TypeGuessRows to 0 and ImportMixedTypes to Text in the registry, you should get the behavior you are expecting. You might, however, consider using a suitably large number like 1000 instead of zero if you find import performance to be less than ideal.

More Related Contents:

  1. Excel “External table is not in the expected format.”
  2. Entity Framework change connection at runtime
  3. Get connection string from App.config
  4. How can I set an SQL Server connection string?
  5. Entity Framework Timeouts
  6. SQL Express connection string: mdf file location relative to application location
  7. Import and Export Excel – What is the best library? [closed]
  8. Read connection string from web.config
  9. How to read connection string in .NET Core?
  10. What is the connection string for localdb for version 11
  11. Parsing CSV using OleDb using C#
  12. What is IMEX within OLEDB connection strings?
  13. System.Data.SqlClient.SqlException: Login failed for user
  14. How to fix “The ConnectionString property has not been initialized”
  15. JSON.net serialize directly from oledbconnection
  16. How to check if connection string is valid?
  17. encrypt SQL connectionstring c#
  18. Get ConnectionString from appsettings.json instead of being hardcoded in .NET Core 2.0 App
  19. From Excel to DataTable in C# with Open XML
  20. How do I use Web.Config transform on my connection strings?
  21. I need a workaround for Excel Guessing Data Types problem
  22. Persist Security Info Property=true and Persist Security Info Property=false
  23. Dynamically generate LINQ queries
  24. Best and shortest way to evaluate mathematical expressions
  25. How do I exit a WPF application programmatically?
  26. Get just the domain name from a URL?
  27. What is the difference between using and await using? And how can I decide which one to use?
  28. Is it possible to bind complex type properties to a datagrid?
  29. When Should I Use Response.Redirect(url, true)?
  30. How to use credentials in HttpClient in c#?

Leave a Comment