SQL Server - Read Excel, CSV 2003, 2007 files using OpenRowSet

In SQL Server, we can read the data directly from an Excel file or a .csv file using OpenRowSet function as given below:

For OPENROWSET function to work properly in SQL Server 2005, you need to check "Enable OPENROWSET and OPENDATASOURCE Support" using SQL Server Surface Area Configuration for Features.

 Once you are done with that you can make use of the below queries to read the data:

--Read .CSV file from a 32 bit SQL Server 
SELECT * FROM OPENROWSET
('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=D:\DWP Deliverables\CSPRojectIntegration\;Extensions=CSV;','SELECT * FROM CSProjects.csv')

--Read Excel file from a 32 bit SQL Server 
SELECT *  FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\DWP Deliverables\CSProjects.xls',
'SELECT * FROM [Sheet1$]')

--Read Excel 2007 file from a 32 bit SQL Server 
Another workaround is trying to install Microsoft.Jet.OLEDB.12.0, and use your connection string with OLEDB 12.0, it can be downloaded at :
2007 Office System Driver:  http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=7554f536-8c28-4598-9b72-ef94e038c891

On the other hand, you can save the 2007 file to 2003 format and try reding it from OLEDB4.0 driver

--Read files from 64 bit SQL Server
JET OLEDB Provider works only with 32 bit. For 64-bit OLEDB Provider for ODBC you have to install MSDASQL using the below link http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20065#overview


Hope this helps!!

How to create a dropdown or picklist column with a list of values in ms excel

Say we would like to create a drop down column in excel sheet with a list of predefined values. Create a list of predefined values in a new sheet and select the list of values and right click and select Name a Range..and give some name say "MyCustomList"

Now click on the column on which you would like to create a dropdown. 
Select Data on the top menu and go to Data Validation -> Data Validation. In the validation criteria, select list in the allow section and give the Source as the name of the range you have chosen like
 =MyCustomList

Click ok to see the dropdown column with the list of values defined in the range. If you want to remove the dropdown then under Data validation, choose Allow Any value.

MS CRM 4.0 Enrich Data - Update MS CRM Records using Excel

By using native data import feature in MS CRM 4.0, we have the ability to update the existing data apart from creating new records. This is called Data Enrichment which is the new feature brought up in MSCRM 4.0. This can be done by exporting your data into an Excel Sheet. In general, when we export the data to a static excel sheet, you find the excel sheet columns start from B instead of A. This column A is in hidden state which holds the GUID of existing record.
If you are so curious, you can unhide the column A to see the GUID. Please follow the steps in this link to unhide the excel columns.

Make your changes to the data in the same excel sheet and save it as a .CSV file. Now, re-import the data using standard data import wizard. This time when you upload the file, you will be pointed to a new checkbox option 'Enrich data by updating existing records rather than creating new records'. This option is used to update the changes you made for the existing records instead of creating new ones.

MS Excel How to view hidden columns

You can unhide any column in MS Excel by using the following steps.
1. Select all the columns using Ctrl+A
2. Point the cursor on any column and right click and Unhide. That's it. Now you can see all the hidden columns in the excel sheet.

MS CRM 4.0 Export To Excel - Export Records more than 10000

In real time scenario, there are situations where you would like to import huge date from MSCRM to Excel. But, there is a limitation in the export to excel feature that, it imports a maximum of 10,000 records by default. To import all the data we need to update the column value of 'MaxRecordsForExportToExcel' in the database. 


1. Open SQL Server Management Studio
2. Open your MS CRM 4.0 Organization Database (OrganizationName_MSCRM)
3. Check the current limit by using the below query
    Select MaxRecordsForExportToExcel From OrganizationBase
4. Update the MaxRecordsForExportToExcel column in OrganizationBase to the desired 
    value.
    Update OrganizationBase Set MaxRecordsForExportToExcel=50000
5. Reset the IIS Server