Dataframe encoding

 using Pkg
    Pkg.add("CSV")
    Pkg.add("DataFrames")
    Pkg.add("SQLite")

    using CSV
    using DataFrames
    using SQLite

    path = "/Users/Desktop/Data"

    ref = CSV.File(path, "filename.csv"))
    println(size(ref))
    first(ref, 10)

    Ref = SQLite.DB() 
    SQLite.load!(filename, path) 

    DataFrame(SQLite.Query(ref,"""select * from tablename
          where date > '1999-01-01'
          """))       

my guess - β€œOrderdate” type is a BLOB - and it is strange …

$ sqlite3 acme.sqlite 
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> PRAGMA table_info(orders);
0|OrderID|INT|0||0
1|CustomersID|INT|0||0
2|EmployeeID|INT|0||0
3|Orderdate|BLOB|0||0
4|ShipperID|INT|0||0
sqlite> 

created with (Julia 1.0.1; SQLite v0.7.0 )

using CSV, DataFrames, SQLite
orders = CSV.read("orders.csv")
acme = SQLite.DB("acme.sqlite") 
SQLite.load!(orders, acme, "orders") 
$ cat orders.csv
OrderID,CustomersID,EmployeeID,Orderdate,ShipperID
10248,90,5,1996-07-04,3
10249,81,6,1996-07-05,1

maybe this is related: https://github.com/JuliaDatabases/SQLite.jl/issues/160

How do I call string on the date values? Should I do it when reading the CSV file to Julia

Tried this, didn’t work

    mydateformat = Dates.DateFormat("yyyy-mm-dd")
    Dates.Date.(["orders[:,4]",mydateformat])

How do I call string on the date values?

simple workaround: try on the CSV reading part - reading as a String

  • orders = CSV.read( IOBuffer(csv) ; types=[Int64,Int64,Int64,String,Int64] )

My log ( Julia 1.1 )

julia> using CSV, DataFrames, SQLite
julia> csv = """OrderID,CustomersID,EmployeeID,Orderdate,ShipperID
       10248,90,5,1990-07-04,3
       10249,81,6,1996-07-05,1
       """
"OrderID,CustomersID,EmployeeID,Orderdate,ShipperID\n10248,90,5,1990-07-04,3\n10249,81,6,1996-07-05,1\n"
julia> orders = CSV.read( IOBuffer(csv) ; types=[Int64,Int64,Int64,String,Int64] )
2Γ—5 DataFrame
β”‚ Row β”‚ OrderID β”‚ CustomersID β”‚ EmployeeID β”‚ Orderdate  β”‚ ShipperID β”‚
β”‚     β”‚ Int64   β”‚ Int64       β”‚ Int64      β”‚ String     β”‚ Int64     β”‚
β”œβ”€β”€β”€β”€β”€β”Όβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”Όβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”Όβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”Όβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”Όβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€
β”‚ 1   β”‚ 10248   β”‚ 90          β”‚ 5          β”‚ 1990-07-04 β”‚ 3         β”‚
β”‚ 2   β”‚ 10249   β”‚ 81          β”‚ 6          β”‚ 1996-07-05 β”‚ 1         β”‚
julia> acme = SQLite.DB()
SQLite.DB(in-memory)
julia> SQLite.load!(orders, acme, "orders")
"orders"
julia> DataFrame(SQLite.Query(acme,"""select * from orders
                 where orderdate = '1996-07-05'
                 """))
1Γ—5 DataFrame
β”‚ Row β”‚ OrderID β”‚ CustomersID β”‚ EmployeeID β”‚ Orderdate  β”‚ ShipperID β”‚
β”‚     β”‚ Int64⍰  β”‚ Int64⍰      β”‚ Int64⍰     β”‚ String⍰    β”‚ Int64⍰    β”‚
β”œβ”€β”€β”€β”€β”€β”Όβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”Όβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”Όβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”Όβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”Όβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€
β”‚ 1   β”‚ 10249   β”‚ 81          β”‚ 6          β”‚ 1996-07-05 β”‚ 1         β”‚

code:

using CSV, DataFrames, SQLite
csv = """OrderID,CustomersID,EmployeeID,Orderdate,ShipperID
10248,90,5,1990-07-04,3
10249,81,6,1996-07-05,1
"""
orders = CSV.read( IOBuffer(csv) ; types=[Int64,Int64,Int64,String,Int64] )
acme = SQLite.DB() 
SQLite.load!(orders, acme, "orders") 
DataFrame(SQLite.Query(acme,"""select * from orders
          where orderdate = '1996-07-05'
          """))  
1 Like

ImreSamu - You the man, thanks

1 Like