Inserting Records From Csv File To Database Table (SQL Server)

What I need to do is to create a SQL Server Script to insert records from a CSV file to a table. I know that this can be done easily using "BULK Insert".

BULK
INSERT TempTable
FROM 'C:\Records.csv'
WITH
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)
GO

Unfortunately, the CSV file contains inconsist开发者_高级运维ent qualifiers ("). Some fields may or may not have this qualifiers when the CSV file is created (manually) like the sample below:

10001,LeBron Quitter,CompanyA
10002,"Insane, Charlie",CompanyB
10003,Donald Punk,"CompanyC,CA"

If I use the above code for the said CSV format, there will be errors because:

1. Qualifier will be included in the table (Ex: "Insane)

2. Since comma (,) is the fieldterminator, the 2nd record will be considered as 4 fields.

So I have to think of something else since I don't want to preprocess the CSV file. It came down to this solution - using MICROSOFT.JET.OLEDB.4.0.

INSERT INTO MyRealTable
SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;Database=C:\Temp\;','SELECT * FROM [Test.csv]')

Note: Before executing the above code be sure to have access right/permission & execute the following:

EXEC
sp_configure 'show advanced options', 1
Reconfigure
EXEC
sp_configure 'Ad Hoc Distributed Queries', 1
Reconfigure
EXEC
sp_configure 'OLE Automation Procedures', 1;
Reconfigure
EXEC
sp_configure 'Agent XPs', 1;
Reconfigure

Questions:

1. Is my solution the right one?

2. Are there any other better solution?

3. Since I'm using MICROSOFT.JET.OLEDB.4 solution, what should be installed/prerequisite?

I'm very open to any suggestion, criticism, or anything because I just want to learn more... Thanks you very much in advance...

Trying to do this in SQL Server alone is probably quite a challenge - I would most like do this as a separate, stand-alone console app or something.

Basically, I'd use the excellent FileHelpers 2.0 library to handle the CSV import - works like a charm and is really easy to use. You can import any fixed-width or delimited file into a DataTable.

Once you have that, you can then turn around and use SqlBulkCopy from your app to bulk load these into SQL Server.

The code would look something like this:

// define your input record - what fields are there, how are the rows and
// fields delimited - the flexibility and power of FileHelpers is amazing!

[DelimitedRecord(",")]
public class InputRecord
{
    public int ID;
    [FieldQuoted('"', QuoteMode.OptionalForBoth)]
    public string PersonName;
    [FieldQuoted('"', QuoteMode.OptionalForBoth)]
    public string CompanyName;
}

// in your console app, grab a file, import it into memory using FileHelpers,
// then convert it into a DataTable and use SqlBulkCopy to insert it into SQL Server
static void Main(string[] args)
{
    // grab file name to import from command-line arguments
    string fileNameToImport = args[0];

    // instantiate FileHelpers engine
    FileHelperEngine engine = new FileHelperEngine(typeof(InputRecord));

    // read the data from the file into a DataTable
    DataTable records = engine.ReadFileAsDT(fileNameToImport);

    // create your SqlBulkCopy object
    SqlBulkCopy bc = new SqlBulkCopy("server=(local);database=TEST;Integrated Security=SSPI;");

    bc.DestinationTableName = "TempTable";

    // bulk copy the data into SQL Server
    bc.WriteToServer(records);
}