Ways to import a CSV file into a SQL Server table _mssql2005

Sometimes we may import data from a CSV into a table in a database, such as when doing a report analysis.
For this question, I think it is difficult not to pour the program personnel! But if SQL Server can do this task, it would be better!
Yes, SQL Server does have this feature.

First let's take a look at the CSV file, which is saved under my D: disk, named Csv.txt, which reads:

Now is the key part of SQL Server;

We are using SQL Server's BULK INSERT command, please click here for a detailed explanation of this command.
We first set up a data table in SQL Server to hold this information.

CREATE TABLE csvtable (
 Name NVARCHAR (max),
 Email NVARCHAR (max), area
 NVARCHAR (Max
)

Then execute the following statement:

BULK INSERT csvtable from
' D:\csv.txt ' with
(
 fieldterminator = ', ',
 rowterminator = ' \ n '
)
SELECT * from csvtable

By F5, the results are as follows:

What do you think? is not simpler than using the program!

But now there are a few questions to consider:

Some column values in the 1,csv file are in double quotes and some column values do not have double quotes:

If you run the above statement again, the result will be different from the previous result:

Some of these columns contain double quotes, which should not be the result we want, and to solve this problem we can only use temporary tables, import the CSV into the temp table, and then remove the double quotes from the temporary table when we import it into the final table.

The column values for the 2,csv file are all made up of double quotes:

This problem is a little more complex than the previous one, in addition to importing the CSV file into a temporary table, you must also modify the code that imports the CSV file into the temporary table:

Notice the part in the circle.

The columns for the 3,csv file are more than the columns of the datasheet:

And our datasheet has only three columns, what happens if you execute the import code above?

The result is:

It puts all the back in the area column, and to deal with this problem, in fact, it is also very simple that we have the column values we want in the data table in order to create a column, and the unwanted column values, but also in the datasheet to create a, but only a temporary column, when the data table into the final table, Ignoring this temporary column is OK.