Solved

Visual Basic Json to CSV Converter - Converting large json files to csv

Posted on 2017-04-12
2
55 Views
Last Modified: 2017-04-19
I have large json files(50+mbs) that I need to convert to .csv. I also have a json to csv converter coded in Visual Basic but because the number of rows in Excel is limited to 1,048,576 rows I'm unable to convert everything successfully.

Can I add some code to the converter to add extra .csv files when it gets to a certain limit? This is the code for the converter

Imports System.IO

Public Class Form1

    Private marketDictionary As New Dictionary(Of String, String)
    Private runnerDictionary As New Dictionary(Of Integer, String)

    Public Sub Print(ByVal Message As String)
        TextBox1.SelectionStart = TextBox1.Text.Length
        TextBox1.SelectedText = vbCrLf & Message
    End Sub

    Private Sub OpenToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles OpenToolStripMenuItem.Click
        With OpenFileDialog1
            .Title = "Open File ..."
            .InitialDirectory = "C:\Users\Anto\Desktop\Application Files\Betfair_1_0_0_1\"
            .FileName = "*.json"
            .ShowDialog()
        End With

    End Sub

    Private Sub ExitToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ExitToolStripMenuItem.Click

        Me.Close()

    End Sub

    Private Sub ProcessToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ProcessToolStripMenuItem.Click

        Print("Processing JSON file")

        ProcessJSON(OpenFileDialog1.FileName.ToString())

        Print("Processing complete")

    End Sub

    Private Sub OpenFileDialog1_FileOk(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk

        Dim jsonFilename As String

        jsonFilename = OpenFileDialog1.FileName.ToString()

        Dim dateString As String = jsonFilename.Replace("C:\Users\Anto\Desktop\Application Files\Betfair_1_0_0_1\", "").Replace(".json", "")

        Dim marketKeys As String = "C:\Users\Anto\Desktop\Application Files\Betfair_1_0_0_1\marketKeys-" & dateString & ".csv"
        Dim runnerKeys As String = "C:\Users\Anto\Desktop\Application Files\Betfair_1_0_0_1\runnerKeys-" & dateString & ".csv"


        Print(jsonFilename)
        Print(marketKeys)
        Print(runnerKeys)
        Print("")
        Print("Loading market and runner keys")

        LoadKeys(marketKeys, runnerKeys)

        Print("Keys loaded - System ready for processing")
        Print("")

    End Sub

    Private Sub LoadKeys(ByVal marketKeysFilename As String, ByVal runnerKeysFilename As String)

        Dim line As String

        Using reader As StreamReader = New StreamReader(marketKeysFilename)

            line = reader.ReadLine

            Do While (Not line Is Nothing)

                Dim parts As String() = Strings.Split(line, ",")
                Try
                    marketDictionary.Add(parts(0), parts(1))
                Catch ex As Exception
                    Print("Ignoring duplicate market key")
                End Try

                line = reader.ReadLine

            Loop

        End Using

        Using reader As StreamReader = New StreamReader(runnerKeysFilename)

            line = reader.ReadLine

            Do While (Not line Is Nothing)

                Dim parts As String() = Strings.Split(line, ",")

                Try
                    runnerDictionary.Add(parts(0), parts(1))
                Catch ex As Exception
                    Print("Ignoring duplicate runner key")
                End Try

                line = reader.ReadLine

            Loop

        End Using

    End Sub

    Private Sub ProcessJSON(ByVal jsonFilename As String)

        Dim outputFilename As String = jsonFilename.Replace("json", "csv")

        Dim line As String

        Using reader As StreamReader = New StreamReader(jsonFilename)

            line = reader.ReadLine

            Do While (Not line Is Nothing)

                Dim parts As String() = Strings.Split(line, "*")
                Dim book() As MarketBookResponse = DeserializeRawBook(parts(1))
                For bookCount As Integer = 0 To book(0).result.Count - 1
                    For runnerCount As Integer = 0 To book(0).result(bookCount).runners.Count - 1

                        With book(0).result(bookCount).runners(runnerCount)

                            Using writer As StreamWriter = File.AppendText(outputFilename)

                                writer.WriteLine(parts(0) & "," & marketDictionary.Item(book(0).result(bookCount).marketId) & "," & runnerDictionary.Item(.selectionId) & "," & .lastPriceTraded)

                            End Using

                        End With

                    Next

                Next

                line = reader.ReadLine 'read in the next line.

            Loop

        End Using

    End Sub

End Class
0
Comment
Question by:William Joyce
2 Comments
 
LVL 80

Accepted Solution

by:
David Johnson, CD, MVP earned 500 total points
ID: 42091029
Imports System.IO

Public Class Form1

    Private marketDictionary As New Dictionary(Of String, String)
    Private runnerDictionary As New Dictionary(Of Integer, String)

    Public Sub Print(ByVal Message As String)
        TextBox1.SelectionStart = TextBox1.Text.Length
        TextBox1.SelectedText = vbCrLf & Message
    End Sub

    Private Sub OpenToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles OpenToolStripMenuItem.Click
        With OpenFileDialog1
            .Title = "Open File ..."
            .InitialDirectory = "C:\Users\Anto\Desktop\Application Files\Betfair_1_0_0_1\"
            .FileName = "*.json"
            .ShowDialog()
        End With

    End Sub

    Private Sub ExitToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ExitToolStripMenuItem.Click

        Me.Close()

    End Sub

    Private Sub ProcessToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ProcessToolStripMenuItem.Click

        Print("Processing JSON file")

        ProcessJSON(OpenFileDialog1.FileName.ToString())

        Print("Processing complete")

    End Sub

    Private Sub OpenFileDialog1_FileOk(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk

        Dim jsonFilename As String

        jsonFilename = OpenFileDialog1.FileName.ToString()

        Dim dateString As String = jsonFilename.Replace("C:\Users\Anto\Desktop\Application Files\Betfair_1_0_0_1\", "").Replace(".json", "")

        Dim marketKeys As String = "C:\Users\Anto\Desktop\Application Files\Betfair_1_0_0_1\marketKeys-" & dateString & ".csv"
        Dim runnerKeys As String = "C:\Users\Anto\Desktop\Application Files\Betfair_1_0_0_1\runnerKeys-" & dateString & ".csv"


        Print(jsonFilename)
        Print(marketKeys)
        Print(runnerKeys)
        Print("")
        Print("Loading market and runner keys")

        LoadKeys(marketKeys, runnerKeys)

        Print("Keys loaded - System ready for processing")
        Print("")

    End Sub

    Private Sub LoadKeys(ByVal marketKeysFilename As String, ByVal runnerKeysFilename As String)

        Dim line As String

        Using reader As StreamReader = New StreamReader(marketKeysFilename)

            line = reader.ReadLine

            Do While (Not line Is Nothing)

                Dim parts As String() = Strings.Split(line, ",")
                Try
                    marketDictionary.Add(parts(0), parts(1))
                Catch ex As Exception
                    Print("Ignoring duplicate market key")
                End Try

                line = reader.ReadLine

            Loop

        End Using

        Using reader As StreamReader = New StreamReader(runnerKeysFilename)

            line = reader.ReadLine

            Do While (Not line Is Nothing)

                Dim parts As String() = Strings.Split(line, ",")

                Try
                    runnerDictionary.Add(parts(0), parts(1))
                Catch ex As Exception
                    Print("Ignoring duplicate runner key")
                End Try

                line = reader.ReadLine

            Loop

        End Using

    End Sub

    Private Sub ProcessJSON(ByVal jsonFilename As String)

        Dim outputFilename As String = jsonFilename.Replace("json", "csv")

        Dim line As String

        Using reader As StreamReader = New StreamReader(jsonFilename)

            line = reader.ReadLine

            Do While (Not line Is Nothing)

                Dim parts As String() = Strings.Split(line, "*")
                Dim book() As MarketBookResponse = DeserializeRawBook(parts(1))
                For bookCount As Integer = 0 To book(0).result.Count - 1
                    For runnerCount As Integer = 0 To book(0).result(bookCount).runners.Count - 1

                        With book(0).result(bookCount).runners(runnerCount)

                            Using writer As StreamWriter = File.AppendText(outputFilename)

                                writer.WriteLine(parts(0) & "," & marketDictionary.Item(book(0).result(bookCount).marketId) & "," & runnerDictionary.Item(.selectionId) & "," & .lastPriceTraded)

                            End Using

                        End With

                    Next

                Next

                line = reader.ReadLine 'read in the next line.

            Loop

        End Using

    End Sub

End Class 

Open in new window

put code into code block
0
 

Author Comment

by:William Joyce
OK thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Join & Write a Comment

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question