Click here to Skip to main content
15,029,010 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,


How to convert value and header retrieve from CSV to JSON format? Currently, I'm appending data from header and combine with row content and join with another column.

Below is what I do:-

C#
Imports LumenWorks.Framework.IO.Csv

Private Async Function ReadFromCSV() As Task
    Try
        Using csv As New CsvReader(New StreamReader("E:\DATA\Data001.csv"), True)
            csv.DefaultParseErrorAction = ParseErrorAction.RaiseEvent
            AddHandler csv.ParseError, AddressOf csv_ParseError
            Dim fieldCount As Integer = csv.FieldCount
            Dim headers() As String = csv.GetFieldHeaders()
            Do While csv.ReadNextRecord()
                For i As Integer = 0 To fieldCount - 1
                    Console.Write(String.Format("{0}:{1};", headers(i), csv(i)))
                Next i
                Console.WriteLine()
            Loop
        End Using
        Await Task.Delay(5000)
    Catch ex As Exception
        Console.WriteLine("{0} > Sending message failed. Ex - {1}", DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss:fff"), ex.Message)
    End Try
End Function

Beside, I want it to be able to send to Iot Hub. So I'm imagining:-

VB.NET
Dim telemetryDataPoint = New With {Key .csv_header(i) = csv_value(i)}


After I dynamically add to JSON, how to dynamically loop into New With {Key}? Because currently I'm using manual write. In future, there will be some more CSV file that having dynamic header count. E.g. File001.csv have 7 headers, File002.csv have 16 headers. So is there a possible 1 function to translate it to dynamic object creation?

What I have tried:

Using csv As New CsvReader(New StreamReader("E:\DATA\Data001.csv"), True)
csv.DefaultParseErrorAction = ParseErrorAction.RaiseEvent
AddHandler csv.ParseError, AddressOf csv_ParseError
Dim fieldCount As Integer = csv.FieldCount
Dim headers() As String = csv.GetFieldHeaders()
Do While csv.ReadNextRecord()
For i As Integer = 0 To fieldCount - 1
Console.Write(String.Format("{0}:{1};", headers(i), csv(i)))
Next i
Console.WriteLine()
Loop
End Using
Posted
Updated 24-Aug-16 3:07am

1 solution

If you haven't done so already, use NuGet to add a reference to Json.NET[^]:
Install-Package Newtonsoft.Json

Import the LINQ to JSON namespace:
Imports Newtonsoft.Json.Linq

Then, use JArray[^], JObject[^] and JProperty[^] to build your objects:
VB.NET
Dim fieldCount As Integer = csv.FieldCount
Dim headers() As String = csv.GetFieldHeaders()
Dim jsonData As New JArray()

Do While csv.ReadNextRecord()
    Dim record As New JObject()
    
    For i As Integer = 0 To fieldCount - 1
        record.Add(New JProperty(headers(i), csv(i)))
    Next
    
    jsonData.Add(record)
Loop

Console.WriteLine(jsonData);

Creating JSON[^]
   
Comments
Luiey Ichigo 24-Aug-16 20:41pm
   
Hi Richard,

Thank you for your guide. It's work and perfect meet!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900