使用CSVHelper将JSON字符串转换为CSV

Question

我正在我的ASP.NET Web API项目中使用JSON/CSV文件，并尝试使用 csvhelper 和 servicestack.text.text 库生成CSV，但无法正常工作。

包含数组的JSON文件是动态的，并且可能具有任意数量的字段。

我使用StreamReader读取文件，然后需要将其转换为CSV文件，以使其可供最终用户下载。

示例文件文本

[{"COLUMN1":"a","COLUMN2":"b","COLUMN3":"c","COLUMN4":"d","COLUMN5":"e"},
 {"COLUMN1":"a","COLUMN2":"b","COLUMN3":"c","COLUMN4":"d","COLUMN5":"e"}]

json to csv

public static string jsonStringToCSV(string content)
{
    var jsonContent = (JArray)JsonConvert.DeserializeObject(content);
    
    var csv = ServiceStack.Text.CsvSerializer.SerializeToCsv(jsonContent);
    return csv;
}

这不会导致我csv data

那么，某些文件是带有逗号或tab的界定类型，并且and I want to utilize CSVHelper to convert CSV string to an IEnumerable dynamically.

public static IEnumerable StringToList(string data, string delimiter, bool HasHeader)
{
    using (var csv = new CsvReader(new StringReader(data)))
    {
         csv.Configuration.SkipEmptyRecords = true;
         csv.Configuration.HasHeaderRecord = HasHeader;
         csv.Configuration.Delimiter = delimiter;

         var records = csv.GetRecords();
         return records;
     }
}

Answer 1

我能够通过使用 DeserializeObject 来解决它来解决它。

用

public static DataTable jsonStringToTable(string jsonContent)
{
    DataTable dt = JsonConvert.DeserializeObject<DataTable>(jsonContent);
    return dt;
}

制作CSV字符串。

public static string jsonToCSV(string jsonContent, string delimiter)
{
    StringWriter csvString = new StringWriter();
    using (var csv = new CsvWriter(csvString))
    {
        csv.Configuration.SkipEmptyRecords = true;
        csv.Configuration.WillThrowOnMissingField = false;
        csv.Configuration.Delimiter = delimiter;

        using (var dt = jsonStringToTable(jsonContent))
        {
            foreach (DataColumn column in dt.Columns)
            {
                csv.WriteField(column.ColumnName);
            }
            csv.NextRecord();

            foreach (DataRow row in dt.Rows)
            {
                for (var i = 0; i < dt.Columns.Count; i++)
                {
                    csv.WriteField(row[i]);
                }
                csv.NextRecord();
            }
        }
    }
    return csvString.ToString();
}

用 WebAPI的最终用法

string csv = jsonToCSV(content, ",");

HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
result.Content = new StringContent(csv);
result.Content.Headers.ContentType = new MediaTypeHeaderValue("text/csv");
result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") { FileName = "export.csv" };
return result;

Answer 2

以防万一您想探索一个开源库来完成这项工作，这是我写的。

cinchoo etl使使用几行代码可将JSON转换为CSV，

using (var r = new ChoJSONReader("sample.json"))
{
    using (var w = new ChoCSVWriter("sample.csv").WithFirstLineHeader())
    {
        w.Write(r);
    }
}

以获取更多信息/来源，转到 https： //github.com/cinchoo/choetl

nuget软件包：

.net框架：

Install-Package ChoETL.JSON

.net core：

Install-Package ChoETL.JSON.NETStandard

样本小提琴： https://dotnetfiddle.net/t3u4w2

全面披露：我是该库的作者。

Answer 3

最近有同样的问题，我相信使用 system.dynamic.expandOobject 和 csvhelper 。它是较少的代码，希望与数据表相比性能相似或更好。

    public static string JsonToCsv(string jsonContent, string delimiter)
    {
        var expandos = JsonConvert.DeserializeObject<ExpandoObject[]>(jsonContent);

        using (var writer = new StringWriter())
        {
            using (var csv = new CsvWriter(writer))
            {
                csv.Configuration.Delimiter = delimiter;

                csv.WriteRecords(expandos as IEnumerable<dynamic>);
            }

            return writer.ToString();
        }
    }

Answer 4

此代码对我来说是可以的：

3个功能（检查，解析和AUX）

    private bool IsValidJson(string strInput)
    {
        try 
        { 
            if (string.IsNullOrWhiteSpace(strInput)) { return false; }
        
            strInput = strInput.Trim();

            if ((strInput.StartsWith("{") && strInput.EndsWith("}")) || (strInput.StartsWith("[") && strInput.EndsWith("]"))) 
            {
                try
                {
                    _ = JToken.Parse(strInput);

                    return true;
                }
                catch
                {
                    return false;
                }
            }

            return false;
        }
        catch { throw; }
    }

    private string ParseJsonToCsv(string json)
    {
        try
        {
            XmlNode xml = JsonConvert.DeserializeXmlNode("{records:{record:" + json + "}}");

            XmlDocument xmldoc = new XmlDocument(); xmldoc.LoadXml(xml.InnerXml);

            DataSet dataSet = new DataSet(); dataSet.ReadXml(new XmlNodeReader(xmldoc));

            string csv = DTableToCsv(dataSet.Tables[0], ",");

            return csv;
        }
        catch { throw; }
    }

    private string DTableToCsv(DataTable table, string delimator)
    {
        try 
        { 
            var result = new StringBuilder();

            for (int i = 0; i < table.Columns.Count; i++)
            {
                result.Append(table.Columns[i].ColumnName);
                result.Append(i == table.Columns.Count - 1 ? "\n" : delimator);
            }

            foreach (DataRow row in table.Rows)
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    result.Append(row[i].ToString());
                    result.Append(i == table.Columns.Count - 1 ? "\n" : delimator);
                }

            return result.ToString().TrimEnd(new char[] { '\r', '\n' });
        }
        catch { throw; }
    }

Answer 5

以下代码成功编译了最新稳定版本的CSVHelper Nuget软件包。

public static string JsonToCsv(string jsonContent, string delimeter)
        {
            var expandos = JsonConvert.DeserializeObject<ExpandoObject[]>(jsonContent);

            using (TextWriter writer = new StringWriter())
            {
                CsvConfiguration csvConfiguration = new CsvConfiguration(System.Globalization.CultureInfo.CurrentCulture);
                csvConfiguration.Delimiter = delimeter;
                using (var csv = new CsvWriter(writer, csvConfiguration))
                {
                    csv.WriteRecords((expandos as IEnumerable<dynamic>));
                }

                return writer.ToString();
            }
        }

Answer 6

public void Convert2Json() 
        { 
            try 
            { 
                if (FileUpload1.PostedFile.FileName != string.Empty) 
                { 
                    string[] FileExt = FileUpload1.FileName.Split('.'); 
                    string FileEx = FileExt[FileExt.Length - 1]; 
                    if (FileEx.ToLower() == "csv") 
                    { 
                        string SourcePath = Server.MapPath("Resources//" + FileUpload1.FileName); 
                        FileUpload1.SaveAs(SourcePath); 
                        string Destpath = (Server.MapPath("Resources//" + FileExt[0] + ".json")); 

                        StreamWriter sw = new StreamWriter(Destpath); 
                        var csv = new List<string[]>(); 
                        var lines = System.IO.File.ReadAllLines(SourcePath); 
                        foreach (string line in lines) 
                            csv.Add(line.Split(',')); 
                        string json = new 
                            System.Web.Script.Serialization.JavaScriptSerializer().Serialize(csv); 
                        sw.Write(json); 
                        sw.Close(); 
                        TextBox1.Text = Destpath; 
                        MessageBox.Show("File is converted to json."); 
                    } 
                    else 
                    { 
                        MessageBox.Show("Invalid File"); 
                    } 

                } 
                else 
                { 
                    MessageBox.Show("File Not Found."); 
                } 
            } 
            catch (Exception ex) 
            { 
                MessageBox.Show(ex.Message); 
            } 
        }

Answer 7

using System.Globalization;

using (var csv = new CsvWriter(csvString, CultureInfo.CurrentCulture)) {
  ...
}