Bulk Insert Into SQL SERVER from CSV / text file

This is very common request recently - How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? Let us see the solution in quick steps.

CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.

Create TestTable

USE TestData GO CREATE TABLE CSVTest

(ID INT,FirstName VARCHAR(40),LastName VARCHAR(40),BirthDate SMALLDATETIME) GO  

Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt

1,James,Smith,19750101 2,Meggie,Smith,19790122 3,Robert,Smith,20071101 4,Alex,Smith,20040202

Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK INSERT CSVTest FROM ‘c:\csvtest.txt’ WITH(FIELDTERMINATOR = ‘,’,ROWTERMINATOR = ‘\n’) GO  

Check the content of the table.

SELECT *     FROM CSVTest GO  

Drop the table to clean up database.

SELECT *     FROM CSVTest GO  

ASP.NET 2.0 and "Validation of ViewState Mac failed" exception

If you get this Exception

[HttpException (0x80004005): Validation of viewstate MAC failed. If this application is hosted by a Web Farm or cluster, ensure that <machineKey> configuration specifies the same validationKey and validation algorithm. AutoGenerate cannot be used in a cluster.]

and

• you know *for sure* that you aren't using a web farm
• it seems that it appears when using built-in databound controls such as GridView, DetailsView or FormView which utilize DataKeyNames.
• it appears if you have a large page which loads slowly for any reason

If following preconditions are true and you click a postbacking control/link while the Page hasn't loaded completely, you might get the "Validation of ViewState MAC failed"  exception. In this case be sure to check following post on ASP.NET Forums where this has been discussed quite thoroughly : http://forums.asp.net/1173230/ShowPost.aspx

It appears because GridView using DataKeyNames requires ViewState to be encrypted. And when ViewState is encrypted, Page adds  <input type="hidden" name="__VIEWSTATEENCRYPTED" id="__VIEWSTATEENCRYPTED" value="" /> field just before closing of the <form> tag. But this hidden field might not bet yet rendered to the browser with long-running pages, and if you make a postback before it "gets down", browser initiates postback without this field (in form post collection)

End result is that if this field is omitted on postback, Page doesn't "know" that viewstate is encrypted and causes the prementioned Exception. E.g Page expects to be fully-loaded before you can make a postback. And by the way similar problem is with event validation since __EVENTVALIDATION field is also rendered on the end of the form.

A way to overcome the problem is to set in web.config

• <pages enableEventValidation="false" viewStateEncryptionMode ="Never" />

  Just note the security implications of these!