blocks|key|935179|text|显然，您可以使用OLEDB打开CSV文件see+this+for+example，从那里将数据从一个数据库获取到另一个数据库应该很容易。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|935180|我不确定它是否真的解决了内存问题，但它比在内存中构造XML副本要好得多。|935181|entityMap|0|LINK|mutability|MUTABLE|url|http://www.codeproject.com/KB/cs/UsingJetForImport.aspx^0|K|K|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

Apparently you can use OLEDB to open a CSV file <a href="http://www.codeproject.com/KB/cs/UsingJetForImport.aspx" rel="nofollow">see this for example</a>, and from there it should be easy to get the data from one DB to the other.

I'm not sure if it actually gets rid of the memory problem, but it is a lot nicer than constructing an XML copy in-memory.

blocks|key|2836684|text|如果使用的是SQL+Server，则可以使用BULK+INSERT命令|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2836685|http://msdn.microsoft.com/en-us/library/ms188365.aspx|offset|length|2836686|基本上，您设置了一个SQLCommand对象，将CommandText设置为如下所示：|2836687|sqlCommand.CommandText+=+"BULK+INSERT+"+&+tableName+&+"+FROM+'"+&+file+&+"'+WITH(TABLOCK,+FIELDTERMINATOR=',')
sqlCommand.ExecuteNonQuery()|code-block|syntax|javascript|2836688|tableName+-是要插入的表格的名称。|2836689|文件-是希望SQL+Server读取的文件的名称。您需要确保SQL+Server可以到达存储文件的位置。这包括ACL权限和网络连接。|2836690|BULK+INSERT命令有很多选项，我以前用过它，它工作得很好。|2836691|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|1H|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|10|8|@]|9|@$D|11|E|12|1|13]]|A|$]]|$1|F|3|G|5|6|7|14|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|15|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|16|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|17|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|18|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|19|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|C]]]]

If you're using SQL Server, you can use the BULK INSERT command

<a href="http://msdn.microsoft.com/en-us/library/ms188365.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms188365.aspx</a>

Basically, you set up a SQLCommand object, set the CommandText to something like:

<pre><code>sqlCommand.CommandText = "BULK INSERT " &amp; tableName &amp; " FROM '" &amp; file &amp; "' WITH(TABLOCK, FIELDTERMINATOR=',')
sqlCommand.ExecuteNonQuery()
</code></pre>

tableName - is the name of the table you want to insert into. 
file - is the name of the file you want SQL Server to read. You need to ensure that SQL Server can get to the location where your file is stored. This includes ACL permissions, and network connectivity.

There's lots of options with the BULK INSERT command, I've used it in the past and it works really well.

blocks|key|935263|text|不管怎样，你都会有多个插入。如果文件不太大，最简单的方法是首先创建一个数据库事务，然后逐行读取并为每行创建一个insert命令。一旦所有行都被读取，您就可以提交事务。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|935264|对于一个100+up的文本文件，我会首先将其拆分成小得多的块，以便插入。也许每1000行提交一次事务？|935265|对我来说，"Id“和"LinkId”字段看起来就像一个经典的父>子关系。因此，只要先插入父对象，就不会有问题。|935266|您没有提到这将是哪种类型的数据库，但是如果它是MSSQL，那么您还可以使用Linq+to+Sql每行创建一个新对象并将其添加到列表中。假设你使用的是C#，你可以使用：|935267|List<YourObject>+YourList+=+new+List<YourObject>();

using(System.IO.StreamReader+file+=+new+System.IO.StreamReader("C:\yourstorage.txt"))+{
++++while((line+=+file.ReadLine())+!=+null)+{
++++++++string[]+fields+=+line.Split(',');

++++++++YourObject+obj+=+new+YourObject();
++++++++obj.FieldX+=+fields[0];
++++++++obj.FieldY+=+fields[1];
++++++++obj.FieldZ+=+fields[2];

++++++++YourList.Add(obj);
++++}
}

using(YourDataContext+db+=+new+YourDataContext())+{
++++db.YourObjects.InsertAllOnSubmit(YourList);
++++db.SubmitChanges();
}|code-block|syntax|javascript|935268|其中YourObject是已经添加到项目中的Linq+to+Sql模型，YourObjects是数据库中的实际表名。但是对于一个巨大的文件来说，这可能会占用大量的内存。|935269|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|S|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|U|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

You're going to have mulitiple inserts regardless. If the file isn't too large, the easiest would be to create a database transaction first and then read line-by-line and create an insert command per line. Once the lines are all read, you can commit the transaction.

For a 100GB text file, I would first break it up into much smaller chunks for the inserts. Maybe commit the transaction every 1000 lines? 

The "Id" and "LinkId" fields look like a classic Parent > Child relationship to me. So as long as the parent is inserted first, there shouldn't be a problem.

You didn't mention what type of database this would be, but if it's MSSQL, then you can also use Linq to Sql to create a new object per line and add it to a list. Assuming you're using C#, you can use :

<pre><code>List&lt;YourObject&gt; YourList = new List&lt;YourObject&gt;();

using(System.IO.StreamReader file = new System.IO.StreamReader("C:\yourstorage.txt")) {
 while((line = file.ReadLine()) != null) {
 string[] fields = line.Split(',');

 YourObject obj = new YourObject();
 obj.FieldX = fields[0];
 obj.FieldY = fields[1];
 obj.FieldZ = fields[2];

 YourList.Add(obj);
 }
}

using(YourDataContext db = new YourDataContext()) {
 db.YourObjects.InsertAllOnSubmit(YourList);
 db.SubmitChanges();
}
</code></pre>

Where YourObject is a Linq to Sql model that's already added to the project and YourObjects is the actual table name in the database. But this can get very memory intensive for a huge file.

Yesterday in an interview I was asked this question and the answers that I provided seem to have not impressed the interviewer. :(

Here is the scenario... A file has comma delimited attribute values in new-line seperated records. There is a requirement that this file has to go into database table that has matching sequence of columns. The file has a self referencing column i.e. One column "Id" holds the unique not null id for the record and there is another column "LinkId" which contains the id of some other record. This value can be empty.

Requirement: The file has to be inserted into the DB in the same order but all at once! That means no multiple inserts.

Question: How can this be best achieved in .Net framework (you can use the latest 4.0 framework)

Solution that I gave: Read file line by line and parse it into a required XML. Then use a DataTable.ReadXML() call to load the file at once into a data table and commit the data set that holds the datatable.

Challanges:

What about a 100 GB text file? Will it work this way? How slow would the functionality get? If we have to hold an XML into memory for 100 GB of data, will virtual address space of the system support it? Wouldnt the page swapping cause issues and become slow?

Would DataTable.ReadXML() work for such huge XML? Will dataset be able to commit?

My answer: :-/

Any ideas guys?

Thx
Harish.

Reading file and writing into DB

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

昨天在一次面试中，我被问到这个问题，我提供的答案似乎没有给面试官留下深刻的印象。:(这是一个场景。文件在新行分隔的记录中具有逗号分隔的属性值。要求此文件必须进入具有匹配的列序列的数据库表中。该文件有一个自引用列，即一列" id“保存该记录的唯一非空id，另一列"LinkId”包含其他记录的id。该值可以为空。要求:该文...

问读取文件并写入数据库
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问读取文件并写入数据库EN