question

Sela-3518 avatar image
1 Vote"
Sela-3518 asked Roxstarj-7535 published

Read and process JSON file with C#

Dear Sir or Madam,

I have a JSON file available and would like to read and process this data using C# programming language. After reading the data from the JSON file, I would rewrite (change format) this data. You can think of it like a translator reading a JSON file and then translating it to another file.

An example would be:
I have a JSON file with different employee names (first name and last name), ID, location and etc. now the first thing I want to do is read the data using C# programming language. Then I want to rewrite the read data. For example in the JSON file residence is defined as City, but in the other file (like DB file) it is defined as CityOfResidence. Thus I must translate the indication from the JSON file. Finally after translation another file format will be generated, for example DB file.

Do any of you have a good suggestion on the most clever way to solve this problem?

With kind regards

dotnet-csharpsmall-basic
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Can you provide a sample for a before and after json in regards to what is expected for translating?

0 Votes 0 ·
Sela-3518 avatar image Sela-3518 karenpayneoregon ·

An example would be:
I have a JSON file with different employee names (first name and last name), ID, location and etc. now the first thing I want to do is read the data using C# programming language. Then I want to rewrite the read data. For example in the JSON file residence is defined as City, but in the other file (like DB file) it is defined as CityOfResidence. Thus I must translate the indication from the JSON file. Finally after translation another file format will be generated, for example DB file.

0 Votes 0 ·
AgaveJoe avatar image
0 Votes"
AgaveJoe answered AgaveJoe edited

.NET Core sample with StreamReader/StreamWriter.


     class Program
     {
         static void Main(string[] args)
         {
             List<Person> source = new List<Person>();
    
             using (StreamReader r = new StreamReader("data.json"))
             {
                 string json = r.ReadToEnd();
                 source = JsonSerializer.Deserialize<List<Person>>(json);
             }
    
             List<DataReadyPerson> destination = source.Select(d => new DataReadyPerson
             {
                 CityOfResidence = d.City,
                 fname = d.Firstname,
                 lname = d.Lastname,
                 DataReadPersonId = d.Id
             }).ToList();
    
    
             string jsonString = JsonSerializer.Serialize(destination, new JsonSerializerOptions() { WriteIndented = true});
             using (StreamWriter outputFile = new StreamWriter("dataReady.json"))
             {
                 outputFile.WriteLine(jsonString);
             }
         }
     }
    
     public class Person
     {
         public int Id { get; set; }
         public string Firstname { get; set; }
         public string Lastname { get; set; }
         public string City { get; set; }
     }
     public class DataReadyPerson
     {
         public int DataReadPersonId { get; set; }
         public string fname { get; set; }
         public string lname { get; set; }
         public string CityOfResidence { get; set; }
     }

JSON input

 [
   {
     "Id": 1,
     "Firstname": "Hello",
     "Lastname": "World",
     "City": "New York"
   },
   {
     "Id": 2,
     "Firstname": "Foo",
     "Lastname": "Bar",
     "City": "Phoenix"
   }
 ]

JSON Output

 [
   {
     "DataReadPersonId": 1,
     "fname": "Hello",
     "lname": "World",
     "CityOfResidence": "New York"
   },
   {
     "DataReadPersonId": 2,
     "fname": "Foo",
     "lname": "Bar",
     "CityOfResidence": "Phoenix"
   }
 ]




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AgaveJoe avatar image
0 Votes"
AgaveJoe answered AgaveJoe edited

SQL Server 2016 and above imports JSON documents directly into a table.

Import JSON documents into SQL Server

Once the data is imported, write a standard INSERT INTO whatever table you like using basic SQL.

 INSERT INTO dbo.SomeTable (CityOfResidence)
 SELECT City FROM dbo.JsonImport

If you must convert the JSON to a C# type first then create a C# type that matches the JSON. Visual Studio has a code generator for this purpose. First, copy the JSON. In Visual Studio select Edit -> Paste Special -> Paste JSON as Classes. From here you can use standard C# deserialization. Use NewtonSoft in .NET 4+ projects or the System.Text.Json namespace for .NET Core. Similar to the SQL example above table, once you have the deserialized data you can convert the C# type to whatever you like. How you go about this depends on how your project works. Do you use LINQ? A data access layer? ADO.NET? How do you currently work with a database?

There are other options like LINQ to JSON and JOSN file readers and writers. These depend on the type of project you are building.






· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you, the task is to solve the whole thing without SQL, because I know how the start file and the end file has to look like.

0 Votes 0 ·

Your initial post clearly indicates the data makes it's way to SQL. So why bother with C# when you can simply bulk load the data?

Anyway, I provided 2 links, one for .NET and one for Core, that illustrates how to deserialize a file. But, before providing an example here on the forums can you at least tell us what framework you are targeting so I can use the correct library? Also, provide a JSON sample and any source code that you have written up to this point. This will help up help you.

0 Votes 0 ·
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered Roxstarj-7535 published

So this I believe is what you are after in a mock-up

 using System;
 using System.Collections.Generic;
 using System.Linq;
 using Newtonsoft.Json;
    
 namespace CodeSampleApp1
 {
     class Program
     {
         static void Main(string[] args)
         {
             // let's say this is not empty we got data from a source
             List<Person> people = new List<Person>();
    
             List<Student> students = people.Select(person => new Student()
             {
                 Identifier = person.Id,
                 First = person.FirstName,
                 Last = person.LastName,
                 CityLocation = person.City
             }).ToList();
    
             /*
              * Now either save to json or write to a database e.g.
              * using a managed data provider or via Entity Framework.
              */
             string json = JsonConvert.SerializeObject(students, Formatting.Indented);
         }
     }
    
     public class Person
     {
         public int Id { get; set; }
         public string FirstName { get; set; }
         public string LastName { get; set; }
         public string City { get; set; }
     }
     public class Student
     {
         public int Identifier { get; set; }
         public string First { get; set; }
         public string Last { get; set; }
         public string CityLocation { get; set; }
     }
 }


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

How would I call my JSON file here?
Would it look like this:

static void Main(string[] args)
{
StreamReader sr = new StreamReader(@"file path");

Console.WriteLine(sr.ReadToEnd());
sr.Close();

Console.ReadKey();
}

0 Votes 0 ·

A one liner for json.net and the same for System.Text.Json

 List<Person> peopleFromJsonList = 
     JsonConvert.DeserializeObject<List<Person>>(File.ReadAllText("Some json file")).ToList();

Or have a class available to your projects

 public class JsonHelpers
 {

     public static TModel DeserializeObject<TModel>(string json) 
         => JsonSerializer.Deserialize<TModel>(json);
    

     public static (bool result, Exception exception) JsonToListFormatted<TModel>(List<TModel> sender, string fileName, bool format = true)
     {
    
         try
         {
    
             var options = new JsonSerializerOptions { WriteIndented = true };
             File.WriteAllText(fileName, JsonSerializer.Serialize(sender, (format ? options : null)));
    
             return (true, null);
    
         }
         catch (Exception exceptionLocal)
         {
             return (false, exceptionLocal);
         }
    
     }
 }
0 Votes 0 ·

HI Karen, this is brilliant code and just what I need to process jsons from my server but it says I have more than one entry point.... Any ideas why? It seems the same, perhaps I started in the wrong class

0 Votes 0 ·