时间戳

首页 > 解决方案 > 将 CSV 文件移动到数据库

c# - 将 CSV 文件移动到数据库

问题描述

我在第一行有 CSV 文件

offerId;monthlyFee;newContractsForMonth;sameContractsForMonth;CancelledContractsForMonth

从第二行到最后我有两列 - 例如：

Second row first column 1;38 Second row second column 66;98;68;28
Third row first column 2;10 third row second column 11;99;180;15

等等

我写了这段代码

using (var streamReader = new StreamReader(@path))
{
    using (var csvReader = new CsvReader(streamReader, config))
    {
        var records = csvReader.GetRecords<OfferWithoutInteger>().ToList();

        foreach (var record in records)
        {
            record.offerId = Regex.Replace(record.offerId, ",", "");
            record.monthlyFee = Regex.Replace(record.monthlyFee, ",", "");
            record.newContractsForMonth = Regex.Replace(record.newContractsForMonth, ",", "");
            record.sameContractsForMonth = Regex.Replace(record.sameContractsForMonth, ",", "");
            record.CancelledContractsForMonth = Regex.Replace(record.CancelledContractsForMonth, ",", "");
        }

        await this._context.AddRangeAsync(records);
        await this._context.SaveChangesAsync();
    }
}

并设法将我的数据以字符串格式放入数据库。我的问题是如何合并两列以及如何将数据从字符串转换为整数。

谢谢

标签： c#asp.netdatabasecsvcsv-import

解决方案

找到了解决方案。我用了

Map(row => row.CustomClassField).Name("CSVColumnName").Convert(row => int.Parse(row.Row.GetField("CSVColumnName").Replace(",","")));

下面是代码。

public async Task<IActionResult> Index()
        {
            var @path = @"pathToFile";
            var config = new CsvConfiguration(CultureInfo.CurrentCulture) { Delimiter = ";", Encoding = Encoding.UTF8 };

            var offersWithIntegerValues = new OfferWithIntegers();
            using (var streamReader = new StreamReader(@path))
            {
                using (var csvReader = new CsvReader(streamReader, config))
                {
                    csvReader.Context.RegisterClassMap<OffersWithIntegersMap>();
                    var records = csvReader.GetRecords<OfferWithIntegers>().ToList();                        
                }
            }

            return View();
        }

        public class OffersWithIntegersMap : ClassMap<OfferWithIntegers>
        {
            public OffersWithIntegersMap()
            {
                Map(row => row.offerId).Name("offerId").Convert(row => int.Parse(row.Row.GetField("offerId").Replace(",","")));
                Map(row => row.monthlyFee).Name("monthlyFee").Convert(row => int.Parse(row.Row.GetField("monthlyFee").Replace(",","")));
                Map(row => row.newContractsForMonth).Name("newContractsForMonth").Convert(row => int.Parse(row.Row.GetField("newContractsForMonth").Replace(",","")));
                Map(row => row.sameContractsForMonth).Name("sameContractsForMonth").Convert(row => int.Parse(row.Row.GetField("sameContractsForMonth").Replace(",","")));
                Map(row => row.CancelledContractsForMonth).Name("CancelledContractsForMonth").Convert(row => int.Parse(row.Row.GetField("CancelledContractsForMonth").Replace(",","")));
            }
        }

推荐阅读