Каков наилучший способ загрузить большой файл данных csv на SQL-сервер с помощью С#? Файл содержит около 30 000 строк и 25 столбцов.
Загрузка CSV файла на SQL-сервер
Ответ 1
1-й, вам не нужно программировать. Вы можете напрямую загружать CSV файлы в базу данных SQL с помощью инструментов управления SQL. Однако, если вам действительно нужно это сделать с помощью программирования, просто прочитайте ниже.
Лично я считаю, что этот подход является наиболее эффективным и простым способом программирования.
В общем, вы можете достичь этого в два этапа
Шаг 1st - прочитать CSV файл и сохранить записи как DataTable.
Шаг 2nd хранит извлеченный DataTable в таблицу базы данных SQL в качестве массовой записи
Это функция, которая возвращает CSV File Data как DataTable. Позвоните и сохраните его в памяти, и вы можете делать с ним все, что хотите.
Эта функция вернет файл CSV Read в DataTable.
private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
{
DataTable csvData = new DataTable();
try
{
using(TextFieldParser csvReader = new TextFieldParser(csv_file_path))
{
csvReader.SetDelimiters(new string[] { "," });
csvReader.HasFieldsEnclosedInQuotes = true;
string[] colFields = csvReader.ReadFields();
foreach (string column in colFields)
{
DataColumn datecolumn = new DataColumn(column);
datecolumn.AllowDBNull = true;
csvData.Columns.Add(datecolumn);
}
while (!csvReader.EndOfData)
{
string[] fieldData = csvReader.ReadFields();
//Making empty value as null
for (int i = 0; i < fieldData.Length; i++)
{
if (fieldData[i] == "")
{
fieldData[i] = null;
}
}
csvData.Rows.Add(fieldData);
}
}
}
catch (Exception ex)
{
return null;
}
return csvData;
}
}
SQLBulkCopy - используйте эту функцию для вставки восстановленного DataTable в таблицу Sql
static void InsertDataIntoSQLServerUsingSQLBulkCopy(DataTable csvFileData)
{
using(SqlConnection dbConnection = new SqlConnection("Data Source=ProductHost;Initial Catalog=yourDB;Integrated Security=SSPI;"))
{
dbConnection.Open();
using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
{
s.DestinationTableName = "Your table name";
foreach (var column in csvFileData.Columns)
s.ColumnMappings.Add(column.ToString(), column.ToString());
s.WriteToServer(csvFileData);
}
}
Ответ 2
Вот еще один способ загрузки CSV в базу данных с помощью программирования. Cinchoo ETL, библиотека с открытым исходным кодом, может выполнять загрузку CSV файла в базу данных с использованием DataReader, что является потоковым подходом с минимальными или нулевыми накладными расходами памяти.
Вот пример, как это сделать
string connectionstring = @"#YOUR DB ConnectionString#";
using (SqlBulkCopy bcp = new SqlBulkCopy(connectionstring))
{
using (var p = new ChoCSVReader("#YOUR CSV FILE#").WithFirstLineHeader())
{
bcp.DestinationTableName = "#TABLENAME#";
bcp.EnableStreaming = true;
bcp.BatchSize = 10000;
bcp.BulkCopyTimeout = 0;
bcp.NotifyAfter = 100;
bcp.SqlRowsCopied += delegate (object sender, SqlRowsCopiedEventArgs e)
{
Console.WriteLine(e.RowsCopied.ToString("#,##0") + " rows copied.");
};
bcp.WriteToServer(p.AsDataReader());
}
}
Ответ 3
Лучший способ импортировать большие файлы CSV в SQL Server - использовать SqlBulkCopy вместе с реализацией IDataReader. Хорошая вещь в том, что вы не читаете весь файл в память (как в случае с подходом DataTable), и вы можете контролировать размер пакета, который отправляется на SQL Server. Плохая вещь в этом заключается в том, что вам нужно реализовать IDataReader который является одним из самых длинных интерфейсов MS, которые я видел.
Я написал пакет Nuget, который поможет вам. Он использует удивительный пакет CsvHelper, поэтому требуется очень мало настроек. Самый простой сценарий будет выглядеть так:
//Instantiate the reader, providing the list of columns which matches 1 to 1 the data table structure.
var dataReader = new CsvDataReader(filePath,
new List<TypeCode>(5)
{
TypeCode.String,
TypeCode.Decimal,
TypeCode.String,
TypeCode.Boolean,
TypeCode.DateTime
});
bulkCopyUtility.BulkCopy("TableName", dataReader);
Есть также дополнительные параметры конфигурации для более сложных сценариев (гибкое сопоставление столбцов, дополнительные статические значения столбцов, которых нет в файле csv, преобразование значений). Если вам интересно, проект находится на Github и доступен как пакет nuget.
Для справки, вот как использовать SqlBulkCopy с IDataReader:
public void BulkCopy(string tableName, IDataReader dataReader, Action<SqlBulkCopy> configureSqlBulkCopy)
{
using (SqlConnection dbConnection = new SqlConnection(connectionString))
{
dbConnection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(dbConnection))
{
bulkCopy.BatchSize = 3000; //Data will be sent to SQL Server in batches of this size
bulkCopy.EnableStreaming = true;
bulkCopy.DestinationTableName = tableName;
//This will ensure mapping based on names rather than column position
foreach (DataColumn column in dataReader.GetSchemaTable().Columns)
{
bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
//If additional, custom configuration is required, invoke the action
configureSqlBulkCopy?.Invoke(bulkCopy);
try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(dataReader);
}
finally
{
dataReader.Close();
}
}
}
}
Ответ 4
Используйте System.Data.SqlClient.SqlBulkCopy класс для вставки данных в таблицы Sql. Чтобы использовать этот класс, вам также необходимо преобразовать данные CVS в DataTable, см. здесь одним из способов.
Ответ 5
Это похоже на идеальную работу для SSIS. Это бесплатная часть SQL Server, может циклически перебирать все файлы csv в папке, очень быстро и имеет отличную обработку ошибок и протоколирование.
Ответ 6
Этот метод использует средство SQLBulkCopy(), но не считывает весь файл в память.
Фокус в том, что он реализует класс IDataReader для чтения CSV файла.
https://www.codeproject.com/Tips/1029831/Fast-and-Simple-IDataReader-Implementation-to-Read
Ответ 7
Вы также можете использовать Bulk Insert
Public Shared Function bulkQuery()
Dim query As StringBuilder = New StringBuilder
query.Append("USE Import_DB BULK INSERT dbo.[Insert_Table] FROM")
query.Append(" 'C:\Insert_Table.csv' ")
query.Append("With (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')")
Return query.ToString
End Function
Будьте осторожны, хотя, так как имя таблицы и имя CSV должны быть идентичны, а количество столбцов в CSV должно быть таким же, как в предопределенной таблице.
Ответ 8
private void GetDataTabletFromCSVFile(string fileName)
{
DataTable dt = new DataTable();
//dt.TableName = fileName;
try
{
using (TextFieldParser csvReader = new TextFieldParser(fileName))
{
csvReader.SetDelimiters(new string[] { "," });
csvReader.HasFieldsEnclosedInQuotes = true;
string[] colFields = csvReader.ReadFields();
//foreach (string column in colFields)
//{
// DataColumn datecolumn = new DataColumn(column);
// datecolumn.AllowDBNull = true;
// dt.Columns.Add(datecolumn);
//}
dt.Columns.AddRange(new DataColumn[8] {
new DataColumn("Symbol", typeof(string)),
new DataColumn("ISIN", typeof(string)),
new DataColumn("Company", typeof(string)),
new DataColumn("FirstListingDate", typeof(string)),
new DataColumn("FaceValue", typeof(string)),
new DataColumn("PaidUpValue", typeof(string)),
new DataColumn("MarketLot",typeof(string)),
new DataColumn("industry",typeof(string))
});
while (!csvReader.EndOfData)
{
string[] fieldData = csvReader.ReadFields();
//Making empty value as null
for (int i = 0; i < fieldData.Length; i++)
{
if (fieldData[i] == "")
{
fieldData[i] = null;
}
}
dt.Rows.Add(fieldData);
}
var builder = new ConfigurationBuilder()
.SetBasePath(Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location))
.AddJsonFile("appsettings.json");
var configuration = builder.Build();
string DBconnection = configuration.GetSection("ConnectionString").Value;
using (SqlConnection dbConnection = new SqlConnection(DBconnection))
{
dbConnection.Open();
using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
{
s.DestinationTableName = "Static.dbo.Securitiesinfo";
foreach (var column in dt.Columns)
s.ColumnMappings.Add(column.ToString(), column.ToString());
s.WriteToServer(dt);
}
}
}
}
catch (Exception ex)
{
var x = ex;
}
}