How to convert CSV string to json (or to c# object's list) without loop? using .NET Framework or .NET Core
This can be done using LINQ and Json.Net (but will still do loops internally) :
using Newtonsoft.Json;
using System.Linq;
// -----------
var data = "\"Product\",\"Date\",\"Expiry\",\"Type\",\"Price\":\"ABC\",\"20-Jul-2019\",\"20-Jul-2022\",\"Supplement\",\"1300\":\"XYZ\",\"20-Jul-2019\",\"20-Jul-2022\",\"Supplement\",\"100\":\"AAA\",\"20-Jul-2019\",\"20-Jul-2022\",\"Supplement\",\"200\":\"XXX\",\"20-Jul-2019\",\"20-Jul-2022\",\"Supplement\",\"500\"";
var datas = data.Split(':'); // string[] containing each line of the CSV
var MemberNames = datas[0].Split(','); // the first line, that contains the member names
var MYObj = datas.Skip(1) // don't take the first line (member names)
.Select((x) => x.Split(',') // split columns
/*
* create an anonymous collection
* with object having 2 properties Key and Value
* (and removes the unneeded ")
*/
.Select((y, i) => new { Key = MemberNames[i].Trim('"'),
Value = y.Trim('"') })
// convert it to a Dictionary
.ToDictionary(d => d.Key, d => d.Value));
// MYObject is IEnumerable<Dictionary<string, string>>
// serialize (remove indented if needed)
var Json = JsonConvert.SerializeObject(MYObj, Formatting.Indented);
Debug.WriteLine(Json);
This outputs :
[
{
"Product": "ABC",
"Date": "20-Jul-2019",
"Expiry": "20-Jul-2022",
"Type": "Supplement",
"Price": "1300"
},
{
"Product": "XYZ",
"Date": "20-Jul-2019",
"Expiry": "20-Jul-2022",
"Type": "Supplement",
"Price": "100"
},
{
"Product": "AAA",
"Date": "20-Jul-2019",
"Expiry": "20-Jul-2022",
"Type": "Supplement",
"Price": "200"
},
{
"Product": "XXX",
"Date": "20-Jul-2019",
"Expiry": "20-Jul-2022",
"Type": "Supplement",
"Price": "500"
}
]
Convert csv file to json using C# with no header
With Cinchoo ETL, you can do it as follows
Define class structures as below
public class Headers
{
public string TransactionFrom { get; set; }
public string TransactionTo { get; set; }
public List<Transaction1> Transactions { get; set; }
}
public class Transaction
{
[ChoFieldPosition(4)]
public string logisticCode { get; set; }
[ChoFieldPosition(5)]
public string siteId { get; set; }
[ChoFieldPosition(6)]
public string userId { get; set; }
[ChoFieldPosition(2)]
public string dateOfTransaction { get; set; }
[ChoFieldPosition(1)]
public string price { get; set; }
}
Parse the CSV, generate JSON as below
string csv = @"2019-12-01T00:00:00.000Z;2019-12-10T23:59:59.999Z
50;false;2019-12-03T15:00:12.077Z;005033971003;48;141;2019-12-03T00:00:00.000Z;2019-12-03T23:59:59.999Z
100;false;2019-12-02T12:38:05.989Z;005740784001;80;311;2019-12-02T00:00:00.000Z;2019-12-02T23:59:59.999Z";
string csvSeparator = ";";
using (var r = ChoCSVReader.LoadText(csv)
.WithDelimiter(csvSeparator)
.ThrowAndStopOnMissingField(false)
.WithCustomRecordSelector(o =>
{
string line = ((Tuple<long, string>)o).Item2;
if (line.SplitNTrim(csvSeparator).Length == 2)
return typeof(Headers);
else
return typeof(Transaction);
})
)
{
var json = ChoJSONWriter.ToTextAll(r.GroupWhile(r1 => r1.GetType() != typeof(Headers))
.Select(g =>
{
Headers master = (Headers)g.First();
master.Transactions = g.Skip(1).Cast<Transaction1>().ToList();
return master;
}));
Console.WriteLine(json);
}
JSON Output:
[
{
"TransactionFrom": "2019-12-01T00:00:00.000Z",
"TransactionTo": "2019-12-10T23:59:59.999Z",
"Transactions": [
{
"logisticCode": "005033971003",
"siteId": "48",
"userId": "141",
"dateOfTransaction": "false",
"price": "50"
}
{
"logisticCode": "005740784001",
"siteId": "80",
"userId": "311",
"dateOfTransaction": "false",
"price": "100"
}
]
}
]
Creating JSON from a CSV file in C#
You can add csv records to a List<T>
and then serialize it with Newtonsoft.Json to get your required JSON object. See the example below:
class Program
{
static void Main(string[] args)
{
string[] csv = new[] { "London,Dubai,4", "Dubai,Mumbai,8", "Dubai,Dhaka,4" };
List<model> list = new List<model>();
foreach (var item in csv)
{
string[] fields = item.Split(',');
list.Add(new model
{
From = fields[0],
To = fields[1],
Duration = fields[2]
});
}
var json = JsonConvert.SerializeObject(list);
Console.WriteLine(json);
Console.ReadLine();
}
}
public class model
{
public string From { get; set; }
public string To { get; set; }
public string Duration { get; set; }
}
Reading from CSV and converting object to JSON
If possible, get the format of the CSV file right by removing column descriptions like email =
and make sure to set a proper column separator like ;
.
In the end your file should look like this:
email@example.com;ExampleName;127.0.0.1;login;2019-06-12 13:30:11Z
email2@example.com;ExampleName2;0.0.0.1;logout;2019-06-12 13:30:11Z
Then define a new class:
public class CSVEntry
{
public string Email { get; set }
public string Name { get; set; }
public IPAddress IPAddress { get; set; }
public string Action { get; set; }
public DateTime Timestamp { get; set; }
public CSVEntry(string email, string name, IPAddress ip, string action, DateTime timestamp)
{
Email = email;
Name = name;
IPAddress = ip;
Action = action;
Timestamp = timestamp;
}
public static CSVEntry Parse(string csvLine)
{
string[] args = csvLine.Split(';');
if (args.Length >= 5)
{
string email = args[0];
string name = args[1];
IPAddress ip = IPAddress.Parse(args[2]);
string action = args[3];
DateTime timestamp = DateTime.Parse(args[4]);
return new CSVEntry(email, name, ip, action, timestamp);
}
return null;
}
}
If you are unable to change the format, you need to parse the csv line using a different method like regex
.
Next, go through the CSV file line by line, create an object using CSVEntry.Parse(line);
and add it to a List<CSVEntry>
for example:
var list = new List<CSVEntry>();
while (!reader.EndOfStream)
{
string line = reader.ReadLine();
list.Add(CSVEntry.Parse(line));
}
Then, you can serialize it using JsonConvert.SerializeObject(list);
JSON string to CSV and CSV to JSON conversion in c#
I was able to solve it by DeserializeObject to a datatable using Json.net, so want to post my own answer but will not mark it as accepted, if anyone have better way to do this.
To convert JSON string to DataTable
public static DataTable jsonStringToTable(string jsonContent)
{
DataTable dt = JsonConvert.DeserializeObject<DataTable>(jsonContent);
return dt;
}
To make CSV string
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();
}
Final Usage in Web API
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;
Related Topics
Convert Byte Array to Wav File
Cannot Access a Closed Stream of a Memorystream, How to Reopen
How to Store List of Data from Database to Arraylist or List in C#
Returning a File to View/Download in ASP.NET MVC
Best Way to Check If a Data Table Has a Null Value in It
C# - Comparing Items Between 2 Lists
C# How to Split a List in Two Using Linq
How to Asynchronously Wait for X Seconds and Execute Something Then
Launch an Application and Send It to Second Monitor
How to Convert a List to Ienumerable
Comparing Two Lists and Return Not Matching Items Results With Error
How to Use Variable in Feature File
Getting All Attributes from an Iwebelement With Selenium Webdriver
In C# What Is the Default Value of the Bytes When Creating a New Byte Array
Generating Aes 256 Bit Key Value
The Incoming Request Has Too Many Parameters. the Server Supports a Maximum of 2100 Parameters