Solved

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

Posted on 2017-04-12
2
122 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 81

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
ID: 42098724
OK thanks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

695 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