transfer7zfile2db: Transfer data from .7z files or text files to the database

In stix-global/eutradeflows: Download bilateral trade flow data from Eurostat Comext

Description

transfer7zfile2db transfers data from a .7z file to the database.

transfer7zfolder2db lists all .7z files available in the local copy of the raw Comext folder and transfers them to a database table. Attention: transfer7zfolder2db erases all previous content in the database.

transfertxtcodesfolder2db transfers product and country codes to the database

Usage

transfer7zfile2db(RMariaDBcon, file7z, productcodestart, tablename)

transfer7zfolder2db(
  RMariaDBcon,
  rawdatacomextfolder,
  productcodestart,
  tablename,
  tabletemplate
)

transfertxtcodesfolder2db(RMariaDBcon, rawdatacomextfolder)

Arguments

RMariaDBcon	database connection object created by RMariaDB::dbConnect
file7z	character path to a .7z file from Comext
productcodestart	numeric vector of product codes. These products and all their sub-products will be transfered to the database. transfer7zfile2db transfers only one product code transfer7zfolder2db can transfer a vector of product codes
tablename	character name of a database table
tabletemplate	character name of a table template to be copied and renamed to tablename
rawdatacomextfoler	character path of the folder where comext files have been saved localy (usually ends with "[year][month]/data")

Details

transfer7zfile2db is a chain of several functions that:

• Decompress comext file .7z file into .dat file in a temporary directory

• filter .dat file, keep only products starting by a certain code, such as "44"

• rename columns from comext to efi column names

• write file content to a database table

• delete decompressed version of the comext file

• (delete temporary directory)

See Also

writedat2db

writetxtcodes2db

Examples

## Not run:  # Transfer data from a 7z file to the database
# Create a database connection
con <- RMariaDB::dbConnect(RMariaDB::MariaDB(), dbname = "test")
# Transfer data for product codes 44 and 94 from a 7z file to a database table
transfer7zfile2db(con, "data_raw/comext/201706/data/nc201501.7z", c(44,94), "raw_comext_monthly_201706")

# Transfer data from many .7z files to the database
# decompose
harvestrecent(con, "/tmp",c(44,94))
# in several steps.
# Download the latest comext data
comextfiles <- downloadcomextmonthlyrecent("/tmp")
comextfolder <- unique(comextfiles$folder)
# Create a table name where this data will be saved
tablename <- paste0("raw_comext_monthly_",
                   scrapcomextfoldername(format(Sys.Date(),"\\[%Y")))
# Transfer recent monthly data to the database
transfer7zfolder2db(con,
                    rawdatacomextfolder = file.path("/tmp",comextfolder),
                    productcodestart = c(44, 94),
                    tablename = tablename,
                    tabletemplate = "raw_comext_monthly_template")

# Transfer monthly archive to the database
transfer7zfolder2db(con,
                    rawdatacomextfolder = file.path("data_raw/comext/2016S1/data"),
                    productcodestart = c(44, 94),
                    tablename = "raw_comext_monthly_2016S1",
                    tabletemplate = "raw_comext_monthly_template")

# Transfer yearly archive to the database
# This time the template is raw_comext yearly
transfer7zfolder2db(con,
                    rawdatacomextfolder = file.path("data_raw/comext/2016S2/data"),
                    productcodestart = c(44, 94),
                    tablename = "raw_comext_yearly_2016S2",
                    tabletemplate = "raw_comext_yearly_template")

# Transfer product codes and country codes to the database
transfertxtcodesfolder2db(con,
                          rawdatacomextfolder = "data_raw/comext/201707/text/english/")

# Disconnect from the database
RMariaDB::dbDisconnect(con)

## End(Not run)

stix-global/eutradeflows documentation built on Nov. 13, 2020, 9:23 p.m.