如何获取CSV字段并写入SQL中的列 [英] How to take a CSV field and write to columns in SQL
问题描述
我有以下代码，它接受一个CSV并写入控制台：
using（CsvReader csv = new CsvReader
new StreamReader（data.csv），true））
{
//缺少的字段不会抛出异常，
//而是会被视为是一个空值
csv.MissingFieldAction = MissingFieldAction.ReplaceByNull;
//替换为，然后使用以下操作：
//csv.MissingFieldAction = MissingFieldAction.ReplaceByEmpty;
int fieldCount = csv.FieldCount;
string [] headers = csv.GetFieldHeaders（）;
while（csv.ReadNextRecord（））
{
for（int i = 0; i
headers [i]，
csv [i] == null？MISSING：csv [i]
Console.WriteLine（）;
}
}
CSV档有7个标头，
每个 csv [i] 的最佳方法是写入然后移动到下一行？
我试图将 ccsv [i] 添加到
我也尝试过以下操作：
code> SqlCommand sql = new SqlCommand（INSERT INTO table1 [+ csv [i] +]，mysqlconnectionstring）;
sql.ExecuteNonQuery（）;
我的表（ table1 ）就是这样：
姓名地址城市邮政编码电话传真设备
您的问题很简单，但我会进一步，让您知道更好的方法来解决这个问题。
当你有问题出售，总是把它分解成部分，并应用每个部分在每个自己的方法。例如，在您的情况下：
- 1 - 从文件读取
- 2 - 创建sql查询
- 3 - 运行查询
，您甚至可以添加验证到文件（假设您的文件甚至没有一行或多行中的7个字段...）和下面的示例，只有当您的文件从未超过500行时，您应该考虑使用将您的文件直接导入数据库的SQL语句，它称为 。它需要更多的调整，但我会留给别人。解决方案用C＃，VS 2013编写。
ExtractCsvIntoSql 类如下：
public class ExtractCsvIntoSql
{
private string CsvPath，Separator;
private bool HasHeader;
private List< string []>线;
private List< string>查询;
///< summary>
/// CSV文件的标题内容
///< / summary>
public string [] Header {get;私人集合}
///< summary>
///用于每个INSERT查询语句的模板
///< / summary>
public string LineTemplate {get;组; }
public ExtractCsvIntoSql（string csvPath，string separator，bool hasHeader = false）
{
this.CsvPath = csvPath;
this.Separator = separator;
this.HasHeader = hasHeader;
this.Lines = new List< string []>（）;
//你也可以设置这个
this.LineTemplate =INSERT INTO [table1] SELECT（{0}）;;
}
///< summary>
///生成SQL查询
///< / summary>
///< return>< / returns>
public List< string> Generate（）
{
if（this.CsvPath == null）
throw new ArgumentException（CSV Path can not be empty）;
//将csv提取到对象
Extract（）;
//生成sql查询
GenerateQuery（）;
return this.Query;
}
private void Extract（）
{
string line;
string [] splittedLine;
int iLine = 0;
try
{
使用（StreamReader sr = File.OpenText（this.CsvPath））
{
while（（line = sr.ReadLine ））！= null）
{
splittedLine = line.Split（new string [] {this.Separator}，StringSplitOptions.None）;
if（iLine == 0&& this.HasHeader）
//标题行
this.Header = splittedLine;
else
this.Lines.Add（splittedLine）;
iLine ++;
}
}
}
catch（Exception ex）
{
if（ex.InnerException！= null）
while（ex。 InnerException！= null）
ex = ex.InnerException;
throw ex;
}
//行将具有所有行和每一行，列条目
}
private void GenerateQuery（）
{
foreach（var line in this.Lines）
{
string entries = string.Concat（''，string.Join（'，'，line））
.TrimEnd（'\''）。TrimEnd（'，'）; // remove last，'
this.Query.Add（string.Format（this.LineTemplate，entries））;
}
}
}
：
class Program
{
static void Main（string [] args）
{
string file = Ask（什么是CSV文件路径？（完整路径））;
string separator = Ask（什么是当前分隔符？（; or，））;
var extract = new ExtractCsvIntoSql（file，separator）;
var sql = extract.Generate（）;
输出（sql）;
}
private static void输出（IEnumerable< string> sql）
{
foreach（var query in sql）
Console.WriteLine ;
Console.WriteLine（************************************* ******）;
Console.Write（END）;
Console.ReadLine（）;
}
私人静态字符串Ask（string question）
{
Console.WriteLine（*************** ****************************）;
Console.WriteLine（question）;
Console.Write（=）;
return Console.ReadLine（）;
}
}
I have the following code which takes a CSV and writes to a console:
using (CsvReader csv = new CsvReader(
new StreamReader("data.csv"), true))
{
// missing fields will not throw an exception,
// but will instead be treated as if there was a null value
csv.MissingFieldAction = MissingFieldAction.ReplaceByNull;
// to replace by "" instead, then use the following action:
//csv.MissingFieldAction = MissingFieldAction.ReplaceByEmpty;
int fieldCount = csv.FieldCount;
string[] headers = csv.GetFieldHeaders();
while (csv.ReadNextRecord())
{
for (int i = 0; i < fieldCount; i++)
Console.Write(string.Format("{0} = {1};",
headers[i],
csv[i] == null ? "MISSING" : csv[i]));
Console.WriteLine();
}
}
The CSV file has 7 headers for which I have 7 columns in my SQL table.
What is the best way to take each csv[i] and write to a row for each column and then move to the next row?
I tried to add the ccsv[i] to a string array but that didn't work.
I also tried the following:
SqlCommand sql = new SqlCommand("INSERT INTO table1 [" + csv[i] + "]", mysqlconnectionstring);
sql.ExecuteNonQuery();
My table (table1) is like this:
name address city zipcode phone fax device
your problem is simple but I will take it one step further and let you know a better way to approach the issue.
when you have a problem to sold, always break it down into parts and apply each part in each own method. For example, in your case:
- 1 - read from the file
- 2 - create a sql query
- 3 - run the query
and you can even add validation to the file (imagine your file does not even have 7 fields in one or more lines...) and the example below it to be taken, only if your file never passes around 500 lines, as if it does normally you should consider to use a SQL statement that takes your file directly in to the database, it's called bulk insert
1 - read from file:
I would use a List<string> to hold the line entries and I always use StreamReader to read from text files.
using (StreamReader sr = File.OpenText(this.CsvPath))
{
while ((line = sr.ReadLine()) != null)
{
splittedLine = line.Split(new string[] { this.Separator }, StringSplitOptions.None);
if (iLine == 0 && this.HasHeader)
// header line
this.Header = splittedLine;
else
this.Lines.Add(splittedLine);
iLine++;
}
}
2 - generate the sql
foreach (var line in this.Lines)
{
string entries = string.Concat("'", string.Join("','", line))
.TrimEnd('\'').TrimEnd(','); // remove last ",'"
this.Query.Add(string.Format(this.LineTemplate, entries));
}
3 - run the query
SqlCommand sql = new SqlCommand(string.Join("", query), mysqlconnectionstring);
sql.ExecuteNonQuery();
having some fun I end up doing the solution and you can download it here, the output is:
The code can be found here. It needs more tweaks but I will left that for others. Solution written in C#, VS 2013.
The ExtractCsvIntoSql class is as follows:
public class ExtractCsvIntoSql
{
private string CsvPath, Separator;
private bool HasHeader;
private List<string[]> Lines;
private List<string> Query;
/// <summary>
/// Header content of the CSV File
/// </summary>
public string[] Header { get; private set; }
/// <summary>
/// Template to be used in each INSERT Query statement
/// </summary>
public string LineTemplate { get; set; }
public ExtractCsvIntoSql(string csvPath, string separator, bool hasHeader = false)
{
this.CsvPath = csvPath;
this.Separator = separator;
this.HasHeader = hasHeader;
this.Lines = new List<string[]>();
// you can also set this
this.LineTemplate = "INSERT INTO [table1] SELECT ({0});";
}
/// <summary>
/// Generates the SQL Query
/// </summary>
/// <returns></returns>
public List<string> Generate()
{
if(this.CsvPath == null)
throw new ArgumentException("CSV Path can't be empty");
// extract csv into object
Extract();
// generate sql query
GenerateQuery();
return this.Query;
}
private void Extract()
{
string line;
string[] splittedLine;
int iLine = 0;
try
{
using (StreamReader sr = File.OpenText(this.CsvPath))
{
while ((line = sr.ReadLine()) != null)
{
splittedLine = line.Split(new string[] { this.Separator }, StringSplitOptions.None);
if (iLine == 0 && this.HasHeader)
// header line
this.Header = splittedLine;
else
this.Lines.Add(splittedLine);
iLine++;
}
}
}
catch (Exception ex)
{
if(ex.InnerException != null)
while (ex.InnerException != null)
ex = ex.InnerException;
throw ex;
}
// Lines will have all rows and each row, the column entry
}
private void GenerateQuery()
{
foreach (var line in this.Lines)
{
string entries = string.Concat("'", string.Join("','", line))
.TrimEnd('\'').TrimEnd(','); // remove last ",'"
this.Query.Add(string.Format(this.LineTemplate, entries));
}
}
}
and you can run it as:
class Program
{
static void Main(string[] args)
{
string file = Ask("What is the CSV file path? (full path)");
string separator = Ask("What is the current separator? (; or ,)");
var extract = new ExtractCsvIntoSql(file, separator);
var sql = extract.Generate();
Output(sql);
}
private static void Output(IEnumerable<string> sql)
{
foreach(var query in sql)
Console.WriteLine(query);
Console.WriteLine("*******************************************");
Console.Write("END ");
Console.ReadLine();
}
private static string Ask(string question)
{
Console.WriteLine("*******************************************");
Console.WriteLine(question);
Console.Write("= ");
return Console.ReadLine();
}
}
这篇关于如何获取CSV字段并写入SQL中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
