Code only grabs first record from JSON c#

bbcompent1 66

Greetings folks, I have an application that takes a JSON resource file and is supposed to write the JSON out with all records into a CSV. However, what's happening is it writes the first record and quits. I'm not quite sure what's wrong TBH. I'm going to include the code and a sample file to use for testing so you can replicate my issue. Just for clarification, ensure to name response.txt to response.json. Thanks in advance.246013-exporter-code.txt 246041-response.txt

Viorel 111.8K Reputation points

2022-09-29T10:27:09.773+00:00

The created CSV file seems to contain all of the three expected rows.
bbcompent1 66 Reputation points

2022-09-29T11:56:47.693+00:00

Response.txt is the JSON source file which gets translated to output.csv for the source of data that is pulled in and then output to a format where the data shows like

email,course,completed_score,completed_date
trainee1@Company portal .com,android,88.94,08-22-2022
bbcompent1 66 Reputation points

2022-09-29T11:57:55.82+00:00

I think you missed the part in the initial post where I said " Just for clarification, ensure to name response.txt to response.json"

bbcompent1 66

Ok, after stepping through the code, I think I located where the problem is. This code here I think needs a row delegation before stepping through each column.

            List<Info> List = new List<Info>();  
            foreach (DataRow item in table2.Rows)  
            {  
                List.Add(new Info { Email = table15.Rows[0][0].ToString(), Score = item[0].ToString(), completeddate = item[1].ToString().Trim(), Course = cols[1].Replace("_course_score", " ") });  
            }  
            foreach (DataRow item in table3.Rows)  
            {  
                List.Add(new Info { Email = table15.Rows[0][0].ToString(), Score = item[0].ToString(), completeddate = item[1].ToString().Trim(), Course = cols[3].Replace("_course_score", " ") });  
            }

That would explain why I only see one employee. On each line of output.csv it contains the full list of their associated class records. The way this works is each class is on one line. The fields for each line are
email,classname,date,score. I think what's happening is the code is not being told to load each row from the output.csv file which is why I only see one employee instead of more.

bbcompent1 66 Reputation points

2022-09-30T13:49:09.867+00:00

@Jack J Jun Can you help me out please? Thanks in advance!
Mayank Pujara 1 Reputation point

2022-10-03T14:16:33.23+00:00
Hi @bbcompent1

in the past I have used Cinchoo ETL as it makes it very easy to convert JSON TO CSV... refer following code block for this.... and attached sample CSV which came based on your json...247101-sample.txt

using (var r = new ChoJSONReader("<<your file path>>\response.json"))
{
using (var w = new ChoCSVWriter("<<your file path>>\sample.csv").WithFirstLineHeader())
{
w.Write(r);
}
}

for more information on this package refer https://github.com/Cinchoo/ChoETL

Nuget package:

.NET Framework:

Install-Package ChoETL.JSON

.NET Core:

Install-Package ChoETL.JSON.NETStandard
bbcompent1 66 Reputation points

2022-10-03T14:20:23.653+00:00

Ok, I'll play around with that and get back to you. Obviously, the less steps I can do in the initial record pull the better. Thank you!

2 answers

bbcompent1 66 Reputation points

2022-10-03T14:33:12.317+00:00

Ok, that worked very well and easy @Mayank Pujara . Now, another question. Is there some method to strip out undesired fields in this CSV output? There are columns I really do not care about, particularly uid, role, confirmed_at, last_sign_in_at,sign_in_count,last_active_at_activated for example. Can I simply call out those column names to remove them from the output of ChoCSV writing?
Please sign in to rate this answer.
bbcompent1 66 Reputation points

2022-10-03T14:47:11.62+00:00

Nevermind, I figured it out. Was overthinking it LOL
Sign in to comment
Mayank Pujara 1 Reputation point

2022-10-03T15:03:41.637+00:00

Great to hear it worked .. @bbcompent1
Please sign in to rate this answer.
bbcompent1 66 Reputation points

2022-10-03T19:20:46.26+00:00

Hi @Mayank Pujara , ran into a bit of a snag. I'm trying to use foreach but I only want the first row, not all rows in the set. Any idea how to make this work?

foreach (DataRow item in table2.Rows) { List.Add(new Info { Email = table1.Rows[i][0].ToString(), Score = table2.Rows[i][0].ToString(), completeddate = table2.Rows[i][1].ToString().Trim(), Course = table2.Columns[2].ToString().Replace(".completed_percentage", "") }); }

Why it is doing that I have no idea, but it takes additional records as it steps through the DataRow Item list. What I want to do is only grab the first row and continue on.

Jack J Jun 24,281 Reputation points Microsoft Vendor

2022-10-05T05:51:31.99+00:00

@bbcompent1 , I have answered your new question in another question Grab only the first record in a datatable.
Sign in to comment