ENV["PYTHON"] = "" # to use a separate Conda environment for Julia
using Pkg
Pkg.add(["PyCall", "Conda", "Pandas"])
Pkg.build("PyCall")
using Conda
Conda.add("pandas")
Conda.add("pytables")

using Pkg
Pkg.add("TableIO")
using TableIO

] add JDF
import JDF # or using JDF

read_table

using TableIO, DataFrames

using CSV
df = DataFrame(read_table("my_data.csv"); copycols=false) # Keyword arguments can be passed to the CSV reader (CSV.jl)
df = DataFrame(read_table("my_data.zip"); copycols=false) # zipped CSV format (assuming there is only 1 file in the archive)

using Dates, JSONTables
df = read_table("my_data.json") |> DataFrame # note that |> DataFrame(; copycols=false) gives wrong column types!
df.my_date_col = Dates.(df.my_date_col) # Dates are imported as strings by default, need to be manually converted

df = read_table("my_data.zip", "my_data.json") |> DataFrame

using JDF
df = DataFrame(read_table("my_data.jdf"); copycols=false) # JDF (compressed binary format)

using Parquet
df = DataFrame(read_table("my_data.parquet"); copycols=false) # Parquet

using Arrow
df = DataFrame(read_table("my_data.arrow"); copycols=false) # Apache Arrow

import Pandas # using gives a naming conflict
df = DataFrame(read_table("my_data.hdf", "key"); copycols=false) # HDF5 (via Pandas)

using XLSX
df = DataFrame(read_table("my_data.xlsx"); copycols=false) # imports 1st sheet
df = DataFrame(read_table("my_data.xlsx", "MyAwesomeSheet"); copycols=false) # imports named sheet

using SQLite
df = DataFrame(read_table("my_data.db", "my_table"); copycols=false) # SQLite from file, table name must be given

using SQLite
sqlite_db = SQLite.DB("my_data.db")
df = DataFrame(read_table(sqlite_db, "my_table"); copycols=false) # SQLite from database connection, table name must be given

using LibPQ, CSV # CSV is required here because `write_table!` for PostgreSQL depends on CSV
postgres_conn = LibPQ.Connection("dbname=postgres user=postgres")
df = DataFrame(read_table(postgres_conn, "my_table"); copycols=false) # reading from Postgres connection

using StatFiles
df = DataFrame(read_table("my_data.dta"); copycols=false) # Stata
df = DataFrame(read_table("my_data.sav"); copycols=false) # SPSS
df = DataFrame(read_table("my_data.sas7bdat"); copycols=false) # SAS

table_names = list_tables(filename)

write_table!

using TableIO, DataFrames

using CSV
write_table!("my_data.csv", df)

write_table!("my_data.zip", df) # zipped CSV. If no "inner" file name is given, the table is always stored in csv format with the same file name as the zip archive

using JSONTables
write_table!("my_data.json", df) # dictionary of arrays
write_table!("my_data.json", df, orientation=:objecttable) # dictionary of arrays
write_table!("my_data.json", df, orientation=:arraytable) # array of dictionaries

write_table!("my_data.zip", "my_data.json", df) # need to explicitly give a file name inside zip archive, otherwise csv format is used

using JDF
write_table!("my_data.jdf", df) # JDF (compressed binary format)

using Parquet
write_table!("my_data.parquet", df) # Parquet - note that Date element type is not supported yet

using Arrow
write_table!("my_data.arrow", df) # Apache Arrow

import Pandas # using gives a naming conflict
write_table!("my_data.hdf", "key", df) # HDF5 (via Pandas)

write_table!("my_data.xlsx", "test_sheet_42", df) # creates sheet with defined name

using SQLite
write_table!("my_data.db", "my_table", df) # SQLite from file, table must not exist

sqlite_db = SQLite.DB("my_data.db")
write_table!(sqlite_db, "my_table", df) # SQLite from database connection

using LibPQ, CSV
postgres_conn = LibPQ.Connection("dbname=postgres user=postgres")
write_table!(postgres_conn, "my_table", df) # table must exist and be compatible with the input data

write_table!("my_data.jl", "my_table", df)

include("my_data.jl")
@assert DataFrame(my_table) == df

name1 = joinpath(testpath, "test.zip") # zipped CSV
name2 = joinpath(testpath, "testx.jdf") # binary
name3 = joinpath(testpath, "testx.xlsx") # Excel
name4 = joinpath(testpath, "testx.db") # SQLite

write_table!(name2, read_table(name1))
write_table!(name3, read_table(name2))
write_table!(name4, "my_table", read_table(name3))

df_recovered = DataFrame(read_table(name4, "my_table"); copycols=false)

using PlutoUI, TableIO, DataFrames
@bind f PlutoUI.FilePicker() # pick any supported file type
df = DataFrame(read_table(f); copycols=false)

┌ Warning: Package TableIO does not have CSV in its dependencies:
│ - If you have TableIO checked out for development and have
│   added CSV as a dependency but haven't updated your primary
│   environment's manifest file, try `Pkg.resolve()`.
│ - Otherwise you may need to report an issue with TableIO
└ Loading CSV into TableIO from project dependency, future warnings for TableIO are suppressed.