将csv数据读入sql表 [英] Reading csv data into a sql table

问题描述

我有一项任务是根据源列可能变化的事实将数据插入表中。

I have a task to insert data into a table, based on the fact that the source column could vary.

列名本身与表上的列名不匹配，但我有那些映射。

The column names themselves do not match the column names on the table, but I have those mapped.

我遇到的问题是管理内存，并获得内存不足异常。

The issue I am having is managing memory, and get an out of memory exception.

1）该过程通过SSIS（2014年DB版本）运行。 有没有办法让SIS包ootb处理丢失的列？  （谷歌提供的所有内容都表明它不是）

1) The process is run through SSIS (2014 DB version).  IS there a way to get SIS package ootb to handle column that go missing?  (Everything that google provides suggests that it isn't)

2）假设我必须编写解决方案的脚本，那么有办法从csv文件中获取数据（x百万行） ）进入sql表

2) Assuming that I have to script the solution then is there a way to get the data from the csv files (x million rows) into the sql table

a）使用一些管理内存消耗的方法？

a) With some method of managing the memory consumption?

b）将验证作为阅读过程的一部分？  ; （确保列长度有效，读取数据和基于验证规则的amen）

b) With validation as part of the read process?  (make sure column lengths are valid, read data, and amen based on validation rules)

我添加了导致我出现问题的代码片段（注意top 1000000会停止内存问题）发生）

I have added the snippet of code that is causing me issues (note top 1000000 stops the memory issue from occurring)

conn =

new OleDbConnection （strConnString.Trim（））;

newOleDbConnection(strConnString.Trim());

                     sql_select =

                    sql_select =

" select top 1000000 *来自[&<; +
System.IO。 路径 。GetFileName（fileName ）
+ "]" ;

"select top 1000000 * from ["+ System.IO.Path.GetFileName(fileName) + "]";

         & NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; conn.Open（）;

                    conn.Open();

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;

                   

<跨度风格=" 颜色：＃2b91af;字体大小：小 "> <跨度风格=" 颜色：＃2b91af;字体大小：小"> OleDbCommand的 cmd =
new OleDbCommand （sql_select，
conn）;

OleDbCommandcmd = newOleDbCommand(sql_select, conn);

         ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NB SP;

                   

OleDbDataAdapter obj_oledb_da =
new OleDbDataAdapter （cmd）;

OleDbDataAdapterobj_oledb_da = newOleDbDataAdapter(cmd);

             ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ; obj_oledb_da.Fill（dt）;

                    obj_oledb_da.Fill(dt);

（道歉，如果这是在错误的论坛，但无法确定一个更好的）

(Apologies if this is in the wrong Forum, but couldn't identify one that was better)

推荐答案

Hi Tractor Boy 99，

Hi Tractor Boy 99,

您可以将csv文件读入Temp表，然后通过insert into ... select语句插入到Sql表中。像这样：

You could read csv file into a Temp table, then insert into Sql table via insert into... select statement. like this:

CREATE TABLE dbo.TempImport
(
    Test1 varchar(255),
    Test2 varchar(255),
    Test3 varchar(255)
)
GO
BULK INSERT dbo.TempImport FROM 'PathToMyTextFile' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')
GO
INSERT INTO dbo.ExistingTable
(
    FirstName ,
    LastName ,
    Country
)
SELECT  Test1,
       Test2,
       Test3
FROM       dbo.TempImport
GO
DROP TABLE dbo.TempImport
GO

祝你好运，

张龙

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