Connection String for Excel and CSV using OLEDB connection

Connection string to read Excel File

Data source is the physical path of Excel file.

Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Temp\YourFile.xlsx; 

Extended Properties=""Excel 12.0;HDR=NO;"";

HDR is  option to indicate that excle file contain column header or not.

Command Text : Select * from [YourSheetName$]

Connection string to read CSV File 

data source is the folder that csv file stored.

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Temp
; Extended Properties=""text;HDR=NO;FMT=Delimited;IMEX=1;"

Command Text : Select * from [YourCSVFIleName]

Data type mismatch in criteria expression in Excel File Search in OLEDB conection

when i try to use a where cause to filter empty rows in Excel file query

if the column data type is string, then i can use [ColumnName] <>' ' to filter all empty rows.

however the column fill with numeric value, then you will receive this error message "Data type mismatch in criteria expression"

the trick to solve this issue is that you can use Is Not NULL to filter all empty rows when the excel file is read.

Here is the sample Query

Select [Column1],[Column2],[Column3],[Column4],[Column5],[Column6] from [Table$] where [Column1] is Not NULL

The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine

when you import excel sheet data into sql server. you might encounter this error "The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine"

the main reason of this cause is that the your application is running in 64 bit machine.


there are two approach to solve this issue.

1. change your visual studio build platform target to X86 and deploy your applicaiton again.




























2. change Enable 32 bit applicaiton property to be true in the application pool advance setting section.

Alter Column Default value in SQL Server 2008

since i use the bulkcopy to import data from excel sheet to sql server 2008. there is no such date added column

in the excel sheet to match the dateadded column in sql server, i decide to update the column dateadded to default value GetDate() to have the current date.

in google there are lots of suggestion... even the MSDN sample is not working

finally i found a working example http://sqlserverplanet.com/ddl/add-column-default-value/

here is the sql syntax to update existing column with default value.

ALTER TABLE [dbo].MyTable ADD  DEFAULT (getdate()) FOR DateAdded