SQL Server: Import CSV files to SQL Server tables.

Sometimes we may import CSV data to a database table, such as when performing report analysis.
I think it's hard to solve this problem at all.ProgramPersonnel! But it would be better if SQL server can complete this task!
Yes, SQL Server does have this function.
First, let's take a look at the CSV file, which is saved in my D: disk and named csv.txt. The content is:

It is now a key part of SQL Server;

We use the bulk insert command of SQL Server. For more information about this command, click here;
Create a data table in SQL Server to save the information,
Create Table csvtable (
Name nvarchar (max ),
Email nvarchar (max ),
Area nvarchar (max)
)
Then execute the following statement:

 
Bulk insert csvtablefrom'D: \ csv.txt'With (fieldterminator =',', Rowterminator ='\ N') Select * From csvtable
 

 
Press F5 and the execution result is as follows:
 

How is it? Is it simpler than a program!

But there are several issues to consider:

1. Some column values in the CSV file use double quotation marks, while some column values do not use double quotation marks:


If you run the preceding statement again, the result is different from the previous one:

Some columns contain double quotation marks. This should not be the expected result. To solve this problem, we can only use the temporary table. First, import the CSV into the temporary table, remove the double quotation marks when importing the temporary table to the final table.

2. The column values in the CSV file are all composed of double quotation marks:


This problem is a little more complex than the previous one. In addition to importing a CSV file to a temporary table, you must also modify the code for importing a CSV file to a temporary table:

Pay attention to the section in the circle.

3. the CSV file contains more columns than the data table:

Our data table has only three columns. If we execute the above import code, what will happen?

The result is:

It places all the back parts in the area column. To solve this problem, it is actually very simple. That is, we create a column in the data table in order of the column values we want, instead, you can create a column value that is not required in the data table. It is only a temporary column. When importing the data table to the final table, ignore this temporary column.

-- EOF --

Author: xingbaifang

Web: http://xbf321.cnblogs.com/

Time: 2010.3.14