Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2017-04-12
2
Medium Priority
?
369 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 83

Accepted Solution

by:
David Johnson, CD, MVP earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

650 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