2GB SQLite file - need to open, convert it to a CSV, so I can use the data to feed an excel query

[SNA400]

Hi All,

I have downloaded the UK Trade Tariff SQLite file so I can run multiple queries over the data to populate an upload spreadsheet giving us notice if there is Excise duty to pay, specific National codes, if the goods have any restrictions etc.

I have tried unsuccessfully to understand SQL and get it to a state I can use. I do not have an SQL Server or an account with the online services as this is me developing something for the company I work for to help us with customs imports.

Can someone teach an old git how to work with this data or at least open the SQLite file so i can view it?

'Frustrated in the UK'

Simon

 

[alansidman]

Can you provide a link to the file and we can then look and see if there is a workable solution for you. As I understand, you want to bring the data from the file to excel and then save it as a .csv.

 

[nguyendang]

Hi All,

I have downloaded the UK Trade Tariff SQLite file so I can run multiple queries over the data to populate an upload spreadsheet giving us notice if there is Excise duty to pay, specific National codes, if the goods have any restrictions etc.

I have tried unsuccessfully to understand SQL and get it to a state I can use. I do not have an SQL Server or an account with the online services as this is me developing something for the company I work for to help us with customs imports.

Can someone teach an old git how to work with this data or at least open the SQLite file so i can view it?

'Frustrated in the UK'

Simon

I guess your SQLite file (v3.0.151 - Full tariff) is located on this website.
This file contains a table named "common_version_groups" with 10,012,159 rows.
How about a VBA macro solution? The following macro will query that SQLite file (uk-tariff-2021-01-01--v3.0.151.sqlite), and create a csv file from the table "common_version_groups" (You need to download and install this SQLite ODBC Driver before running this macro).
You may need to modify the code to suit your needs.

Option Explicit

Private Sub BringDataFromSQLiteToExcel()
    Dim strConnectionString As String
    Dim strSQLiteFile As String
    strSQLiteFile = "E:\uk-tariff-2021-01-01--v3.0.151.sqlite" 'Name of SQLite file
    strConnectionString = "DRIVER=SQLite3 ODBC Driver;Database=" & strSQLiteFile & ";LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;"
    Dim objCnn As Object
    Set objCnn = CreateObject("ADODB.Connection")
    With objCnn
        .ConnectionString = strConnectionString
        On Error Resume Next
        .Open
        If Err.Number <> 0 Then
            MsgBox Err.Description, vbExclamation, "Error"
            Exit Sub
        End If
        On Error GoTo 0
        Dim objRs As Object
        Set objRs = .Execute("SELECT * FROM common_version_groups")
        If Not (objRs.BOF And objRs.EOF) Then
            Dim i As Variant
            Dim arrValues As Variant
            arrValues = objRs.GetRows
            objRs.Close
            objCnn.Close
            Const strCsvFile As String = "E:\uk-tariff-2021-01-01--v3.0.151.csv" 'Name of CSV file to be exported
            Dim objFSO As Object
            Set objFSO = CreateObject("Scripting.FileSystemObject")
            Dim objTextStream As Object
            Set objTextStream = objFSO.CreateTextFile(strCsvFile)
            For i = 0 To UBound(arrValues, 2)
                objTextStream.Write arrValues(0, i) & "," & arrValues(1, i) & "," & arrValues(2, i) & "," & arrValues(3, i)
                objTextStream.WriteLine
            Next
            objTextStream.Close
        End If
        If objCnn.State = 1 Then objCnn.Close
    End With
End Sub

Private Function Quote(ByVal Text As String) As String
    Quote = Chr(34) & Text & Chr(34)
End Function

Because an individual worksheet in Excel is unable to handle more than 1,048,576 rows, you should preview/open the exported csv file with an app like Modern CSV (Free).