SQL: A stored procedure used to import CSV file data to a specified ms SQL table

Recently, due to work needs, from an applicationProgramExport a CSV data table and then import it to the ms SQL data table of another application environment, because it involves the import of multiple CSV export files
Therefore, a stored procedure is written to give it some flexibility. You can specify the path where the CSV file is stored, the name of the CSV file, and the name of the imported ms SQL data table. In this way, we
In. netCodeTo import the specified CSV file at the specified position to the specified ms SQL data table.
Note: before importing a CSV file, you must create a data table corresponding to the CSV file in ms SQL.
For example:
CSV file content:
Area machine Job
Plant 1 l02m35 1774-dp5156
Plant 1 l02m67 1831-dp5156
Plant 1 l02m02 1832-dp5156
Plant 1 l03m44 1792-d115
Plant 1 l05m29 1725-dp4975
Plant 1 l05m29 1723-dp4975
Plant 1 l05m29 1820-dp4975
Then we need to create a ms SQL data table with fields:

Area, machine, job

The fields of the ms SQL data table correspond one to one with the fields of the CSV file content.

The stored procedure can be used for import. The content of the stored procedure is as follows:

Create procedure [DBO]. [my_import_csvfile]
@ Sourcefilepath nvarchar ( 200 ), -- The folder Where You upload the CSV file on your server. eg: ' D: \ dataexchange \ csvtmp '
@ Sourcefilename nvarchar ( 100 ), -- The CSV filename that you want to import. eg: ' Exportfileone.csv '
@ Desttablename nvarchar ( 100 ) -- The destination table name eg: ' Mssqlexportfiletableone '
As
Begin

Declare @ delsqlstr nvarchar (600)
Declare @ impsqlstr nvarchar (600)

--Clear the content of the destination table
Set @ delsqlstr='Delete from' +@ Desttablename
Exec sp_executesql @ delsqlstr

-- Import CSV data into the destination table
Set @ impsqlstr = ' Insert ' + @ Desttablename
+ ' Select * From OpenRowSet ( '' Msdasql '' , '' Driver = {Microsoft text Driver (*. txt; *. CSV )};
Defaultdir = ' + @ Sourcefilepath + ' ; Extensions = CSV; '' , '' Select * From " '+ @ Sourcefilename +' "'' ) '

Exec sp_executesql @ impsqlstr

Select @ rowcount

End

-- ================ Excute example ==================================
--
-- Use [mytestdb]
-- Go
--
-- Declare @ return_value Int
--
-- Exec @ return_value = [DBO]. [my_import_csvfile]
-- @ Sourcefilepath = N ' D: \ dataexchange \ csvtmp ' ,
-- @ Sourcefilename = N ' Exportfileone.csv ' ,
-- @ Desttablename = N ' Mssqlexportfiletableone '
--
-- Select ' Return Value '   = @ Return_value
--
-- Go
--
-- Select * From DBO. mssqlexportfiletableone

 

This code is successfully debugged in MS sql2005.