Import CSV file into SQL Server

Question

I have a few fundamental questions and I need assistance importing a.csv file into SQL Server using BULK INSERT.

Issues:

• How can I make the import process handle the data that may have a comma (,) in between it in the CSV file (for example, the description)?
• The data with commas are surrounded in "" (double quotes) [as in the example below] if the customer prepares the CSV from Excel. How can the import manage this, then?
• How can we determine which rows the import skipped because of incorrect data? When importing, rows that cannot be imported are skipped.

Here is the sample CSV with the header:

Name,Class,Subject,ExamDate,Mark,Description
Prabhat,4,Math,2/10/2013,25,Test data for prabhat.
Murari,5,Science,2/11/2013,24,"Test data for his's test, where we can test 2nd ROW, Test."
sanjay,4,Science,,25,Test Only.

The SQL statement to import:

BULK INSERT SchoolsTemp
FROM 'C:\CSVData\Schools.csv'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)

Can someone please help me with this?