# This one can be used for large-ish files (100MB to 1GB)
# But use dask-large-files.py for the really large files (1GB+)

# Import packages
import pandas as pd
import duckdb as duckdb
import time

start_time = time.time()

file_path = 'path_to_file.sas7bdat'

# Specify the chunk size (adjust based on available memory)
chunk_size = 800000

# Initialize an empty DataFrame to store the concatenated results
result_df = pd.DataFrame()

# Read the file in chunks
for chunk in pd.read_sas(file_path, chunksize=chunk_size):

	# Concatenate the chunk to the result DataFrame
	result_df = pd.concat([result_df, chunk], ignore_index=True)

print(result_df)

# Connect to DuckDB
con = duckdb.connect(database='name_of_database.db', read_only=False)

# Write dataframe to DuckDB
con.execute("CREATE TABLE name_of_table AS SELECT * FROM result_df")
  
# Close the connection
con.close()

# Time the script
end_time = time.time()

runtime = end_time - start_time

print(f"Script execution time: {runtime:.2f} seconds")

# %%
# Import packages
import pyreadstat
import dask.dataframe as dd
from dask.delayed import delayed
import duckdb
import os
import time
from datetime import datetime

# %%
start_time = time.time()

# %%
# Set working directory
new_dir = 'path_to_WD'
os.chdir(new_dir)

# %%
# Create chunks for reading the SAS file
def dask_sas_reader(filepath, chunksize):

	# Read metadata only of the SAS file in order to find out the number of rows
	_, meta = pyreadstat.read_sas7bdat(filepath, disable_datetime_conversion=True, metadataonly=True)

	# Helper function which reads a chunk of the SAS file
	def read_sas_chunk(offset):
		df, _ = pyreadstat.read_sas7bdat(filepath, disable_datetime_conversion=True, row_offset=offset, row_limit=chunksize)
		return df

	# Parallelize reading of chunks using delayed() and combine these in a dask dataframe
	dfs = [delayed(read_sas_chunk)(x) for x in range(0, meta.number_rows, chunksize)]
	return dd.from_delayed(dfs)

# %%
# Read using dask
dd_df = dask_sas_reader('path_to_file', chunksize=800000)

# %%
# Connect to DuckDB
con = duckdb.connect(database='database_name', read_only=False)

# %%
# Convert to pandas dataframe and write to DuckDB in chunks
chunk_size = 800000

# Get the list of delayed objects
delayed_dfs = dd_df.to_delayed()

	for i, delayed_df in enumerate(delayed_dfs):

	# Convert the delayed object to a Pandas DataFrame
	pandas_df = delayed_df.compute()

	# Write the DataFrame to DuckDB
	if i == 0:
		# If this is the first chunk, create a new table
		con.execute("CREATE TABLE name_of_table AS SELECT * FROM pandas_df")
	else:
		# If this is not the first chunk, append to the existing table
		con.execute("INSERT INTO name_of_table * FROM pandas_df")

	# Discard the Pandas DataFrame to free up memory
	del pandas_df

# %%
# Show the database tables
tables = con.execute("SHOW TABLES").fetchall()
print(tables)

# %%
# Close the connection
con.close()

# %%
end_time = time.time()

runtime = end_time - start_time

current_time = datetime.now().strftime("%H:%M:%S")

print(f'The script took {runtime:.2f} seconds to run and finished on {current_time}')

import duckdb as duckdb
import os

# Set working directory
new_dir = 'path_to_WD'
os.chdir(new_dir)

# Connect to DuckDB
con = duckdb.connect(database='database_name.db', read_only=False)

# Export database to CSV
con.execute("EXPORT DATABASE 'name_of_folder'")