将csv文件读取到sql [英] read csv file to sql

问题描述

我有一个CSV文件，就像你可以在Excel中保存的那样。


我想用C＃.NET读取文件并将数据放入sql2k数据

base。然后导入另一个将更新这些相同记录的文件。


假设file1.csv文件包含由逗号分隔的5个字段，名为a，b，c，d，e

和sql数据bse有5个char字段，名为f1，f2，f3，f4，f5

我希望字段''a'从csv导入到字段中'f1''的sql db

依此类推到''e''转到''f5''。


然后我有了file2 .csv具有相同的字段，数据可能是
或者可能不需要更新到数据库。 csv文件的字段''a''对于

该行是唯一的。所以我需要读取file2.csv，检查字段''a''

中的数据是否匹配db的字段''f1''中的一行数据，如果它找到了匹配，然后

用file2.csv中的新数据替换db行。如果不匹配则只需

不管它。


谢谢你


Paul

I have a CSV file, like the one you can save in Excel.

I want to use C# .NET to read the file and place the data into a sql2k data
base. and then import another file that would update those same records.

Assume file1.csv file contains 5 fields separated by commas named a,b,c,d,e
and the sql data bse has 5 char fields named f1,f2,f3,f4,f5

I want field ''a'' from the csv to be imported into field ''f1'' of the sql db
and so on to ''e'' goes to ''f5''.

Then I have file2.csv which has the same fields a thru e with data that may
or may not need updating to the db. Field ''a'' of the csv file is unique for
that row. So I need to read file2.csv, check to see if the data in field ''a''
matches a row of data in field ''f1'' of the db, if it finds a match, then
replace the db row with the new data from file2.csv. If no match then just
leave it alone.

Thanks You

Paul

推荐答案

您好Paul，


首先，我想确认一下我对您的问题的理解。从

您的描述中，我了解您需要将数据从CSV文件导入

到SQL Server。如果有任何误解，请随时告诉我

知道。


实现这一目标的最简单方法是使用SQL DTS。你可以找到

导入和导出数据从开始菜单中的SQL Server文件夹中。您可以通过设置其中的选项来定制转移过程。


如果您需要通过编程来完成，您必须将CSV文件设置为

首先是ODBC数据源。我们可以在

管理工具/数据源（ODBC）下添加一个新的ODBC数据源。然后我们使用ODBC数据提供程序

将CSV文件中的数据导入DataSet，并使用SQL数据提供程序将它们更新到SQL

服务器数据库。


因为你需要f1字段是唯一的，我建议你制作f1字段

表的主键。传输数据时，我们可以在一个循环中逐个插入一个记录。当违反主键约束时，将抛出异常

，我们只是捕获该异常并忽略它。因此，

重复的行将被忽略。


HTH。


Kevin Yu

=======

此帖子已提供按原样没有保证，并且不授予

权利。

Hi Paul,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to import data from a CSV file
to SQL Server. If there is any misunderstanding, please feel free to let me
know.

The simplest way to achieve this goal, is to use SQL DTS. You can find
"Import and Export Data" from the Start menu, in SQL server folder. You can
customize the transfer process by setting the options in it.

If you need to do it through programming, you have to make the CSV file as
an ODBC data source first. We can add a new ODBC data source under
administrative tools/Data Sources(ODBC). Then we use an ODBC data provider
to get data from the CSV file into a DataSet, and update them to a SQL
server database using SQL data provider.

Since you need to f1 field to be unique, I suggest you make f1 field
primary key of the table. When transfering data, we can insert records one
by one in a loop. When the primary key constraint is violated, an exception
will be thrown, we just catch that exception and ignore it. Thus,
duplicated rows will be ignored.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

好的，我可以将CSV文件变为像这样的数据集：


string ConnectionString =

@" Driver = {Microsoft Text Driver（* .txt; * .csv）}; DBQ = c ：\" ;;

OdbcConnection conn = new OdbcConnection（ConnectionString）;

conn.Open（）;

OdbcDataAdapter da = new OdbcDataAdapter （" Select * FROM Test.txt"，conn）;

DataSet ds = new DataSet（）;

da.Fill（ds，" TheTable"）;

conn.Close（）;


现在我用什么代码将数据集放到一个现有的SQL表中。

SQL表的主要字段不在CSV文件中，因此我不能仅仅重新构建SQL表的结构并对数据集进行更新。 />

就像你说的我可以循环遍历将每一行插入一个SQL

表的数据集，同时这样做我可以我想要更新的记录。


你能告诉我你用来循环数据集的代码和

插入所有记录将数据集放入现有SQL表中。

" Kevin Yu [MSFT]"写道：

OK, I am able to get the CSV file into a dataset like this:

string ConnectionString =
@"Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\";
OdbcConnection conn = new OdbcConnection(ConnectionString);
conn.Open();
OdbcDataAdapter da = new OdbcDataAdapter("Select * FROM Test.txt", conn);
DataSet ds = new DataSet();
da.Fill(ds, "TheTable");
conn.Close();

So now what code do I use to get the dataset into an exsisting SQL table.

The primary field of the SQL table is not in the CSV file so I can''t just
recreate the structure of the SQL table and do an update on the dataset.

Like you said I can loop through the dataset inserting each row into an SQL
table, while doing that I can check for the records I want to update.

Can you just show me the code you would use to loop through the dataset and
insert all the records of the dataset into the exsisting SQL table.
"Kevin Yu [MSFT]" wrote:

嗨保罗，

首先，我想确认一下我对你的问题的理解。从您的描述中，我了解您需要将数据从CSV文件导入到SQL Server。如果有任何误解，请随时让我知道。

实现这一目标的最简单方法是使用SQL DTS。你可以找到
导入和导出数据。从开始菜单中的SQL Server文件夹中。您可以通过设置其中的选项来自定义传输过程。

如果您需要通过编程来完成，您必须将CSV文件设为
ODBC数据源第一。我们可以在管理工具/数据源（ODBC）下添加新的ODBC数据源。然后我们使用ODBC数据提供程序将CSV文件中的数据导入DataSet，并使用SQL数据提供程序将它们更新到SQL服务器数据库。

因为您需要要f1字段是唯一的，我建议你制作f1字段
表的主键。传输数据时，我们可以循环插入一个记录。当违反主键约束时，将抛出异常
，我们只是捕获该异常并忽略它。因此，
重复的行将被忽略。

HTH。

Kevin Yu
=======
这个发布是按原样提供的。没有保证，也没有授予
权利。

Hi Paul,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to import data from a CSV file
to SQL Server. If there is any misunderstanding, please feel free to let me
know.

The simplest way to achieve this goal, is to use SQL DTS. You can find
"Import and Export Data" from the Start menu, in SQL server folder. You can
customize the transfer process by setting the options in it.

If you need to do it through programming, you have to make the CSV file as
an ODBC data source first. We can add a new ODBC data source under
administrative tools/Data Sources(ODBC). Then we use an ODBC data provider
to get data from the CSV file into a DataSet, and update them to a SQL
server database using SQL data provider.

Since you need to f1 field to be unique, I suggest you make f1 field
primary key of the table. When transfering data, we can insert records one
by one in a loop. When the primary key constraint is violated, an exception
will be thrown, we just catch that exception and ignore it. Thus,
duplicated rows will be ignored.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

嗨保罗，


由于f1字段不能是主键字段，我们必须首先检查

是否存在。这里我使用SELECT COUNT sql语句。如果记录

存在，则忽略它。如果没有，请插入INSERT语句。


这是一个示例代码。


SqlCommand cmdCheck = new SqlCommand（ SELECT COUNT（*）FROM Table1 Where

f1 = @ f1"，cnn）;

foreach（ds.Tables [" Table1"]中的DataRow dr。行）

{

cmdCheck.P​​arameters.Clear（）;

SqlParameter p = cmdCheck.P​​arameters.Add（" @ f1"， SqlDbType.NVarChar，50）;

p.Value = dr [" a"];

int iCount =（int）cmdCheck.ExecuteScalar（）;

if（iCount == 0）

{

//运行插入命令

}

}

Kevin Yu

=======

此帖子已提供按现状没有保证，也没有赋予

权利。

Hi Paul,

Since the f1 field cannot be the primary key field, we have to check for
existence first. Here I use a SELECT COUNT sql statement. If the record
exists, just ignore it. If it doesn''t, insert it with INSERT statement.

Here''s a sample code.

SqlCommand cmdCheck = new SqlCommand("SELECT COUNT(*) FROM Table1 Where
f1=@f1", cnn);
foreach(DataRow dr in ds.Tables["Table1"].Rows)
{
cmdCheck.Parameters.Clear();
SqlParameter p = cmdCheck.Parameters.Add("@f1", SqlDbType.NVarChar, 50);
p.Value = dr["a"];
int iCount = (int)cmdCheck.ExecuteScalar();
if(iCount == 0)
{
//run insert commands
}
}
Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

这篇关于将csv文件读取到sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！