将大型csv上传到数据库 [英] Uploading of large csv to database

问题描述

我有一个csv文件，其中包含从几百条记录到50,000条记录的任何内容，尽管我有完整的时间进行处理非常缓慢，所以正在寻找有关执行要求的最佳方式的建议。



方案是订单可以包含多达50,000个代码，订单号不存在在上传数据中因此无法执行批量插入（或者我不知道如何）。



CSV文件只有一个字段，例如



abcd123

efgh456





任何建议或链接都​​会我很感激。



我尝试过：



订单与文件详细信息一起在控制器中传递。控制器方法如下。

Hi,

I have a csv file that contain anything from a few hundred records to 50,000 records, although I have the process working the time taking to complete is painfully slow, so was looking for advice on best way of performing the requirement.

The scenario is an order can contain anything upto 50,000 codes, the order number is not present in the upload data so cannot perform a bulk insert (or i don't know how).

The CSV file only has one field such as

abcd123
efgh456


Any advise or links would be appreciated.

What I have tried:

The ID for the order is passed in the controller together with the file details. Controller method below.

[HttpPost]
public ActionResult UploadNewMailingData(int id,HttpPostedFileBase attachmentcsv)
{
    CsvFileDescription csvFileDescription = new CsvFileDescription
    {
        SeparatorChar = ',',
        FirstLineHasColumnNames = true
    };
    CsvContext csvContext = new CsvContext();
    StreamReader streamReader = new StreamReader(attachmentcsv.InputStream);
    IEnumerable<MediaSaleRecord> list = csvContext.Read<MediaSaleRecord>(streamReader, csvFileDescription);
    MediaSaleRecord mediaSaleRecord =  new  MediaSaleRecord();
    List<MediaSaleRecord> myList = new List<MediaSaleRecord>();
    for (int n = 0;n<list.Count();n++)
    {
        mediaSaleRecord.CustomerID = list.ElementAt(n).CustomerID;
        mediaSaleRecord.MediaSaleID = id;
        myList.Add(mediaSaleRecord);
    }
    db.MediaSaleRecords.AddRange(myList);
    db.SaveChanges();
    return Redirect("Index");
}

以上代码的信用额度为

在Asp.Net MVC中将CSV（其他分隔符分隔）文件上传到Sql Server - 首先使用EF代码 [ ^ ]

定义的模型很简单，只包括：

credit for the above code goes to
Upload CSV (other Delimiter-Separated) Files to Sql Server in Asp.Net MVC - Using EF Code First[^]
The model is defined is simple and only consist of:

    public class MediaSaleRecord
    {
        public int MediaSaleRecordID { get; set; }
        public int MediaSaleID { get; set; }
        public string CustomerID { get; set; }
    }
}

推荐答案

替代方案可能是 CsvHelper ： CsvHelper [ ^ ]但我怀疑这会更快。



可以使用格式调整 BCP 实用程序文件，请参阅：表格中的更多列比数据文件中的更多：使用bcp命令行实用程序 - 简单对话 [ ^ ]

但我担心这样做不允许定义常量值。



我认为最好的解决方案是使用'登台表'然后使用插入到目标表中一个 INSERT INTO DEST_TABLE SELECT ... SQL查询，请参阅： SQL Server论坛 - 为bcp中的列指定常量值。 [ ^ ]

An alternative might be CsvHelper: CsvHelper[^] but I doubt that this will be faster.

The BCP utility can be tuned using a formatting file, see: 'More Columns in Table than in Data File' in: Working with the bcp Command-line Utility - Simple Talk[^]
But I'm afraid this does not allow to define constant values.

The best solution I think is to use a 'staging table' and then insert into destination table using a INSERT INTO DEST_TABLE SELECT ... SQL query, see: SQL Server Forums - Specifying constant value for column in bcp.[^]

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