writedat2db: Write Comext data to the database

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

Description

writedat2db reads data from a dat file, and writes it into a database table. The connection to the database is oppened with a username and password provided in the "tradeflows" group of the ~/.my.cnf file by default. This function was updated to consider zeros 0 and empty strings "" as missing values, NULL in the database. The updated used an SQL query to insert the data, instead of RMariaDB::dbWriteTable() There was an "permission denied" issue when loading files a solution is provided here: https://stackoverflow.com/questions/3471474/mysql-load-data-infile-cant-get-stat-of-file-errcode-2 Specify LOCAL in LOAD DATA infile, so that it becomes LOAD DATA LOCAL infile.

writetxtcodes2db reads country codes from REPORTER.txt and PARTNER.txt files and writes them to the database. It doesn't erases previous codes you have to run eutradeflows::createdbstructure first to get a fresh database with empty tables.

Usage

writedat2db(RMariaDBcon, datfile, tablename, sep = ",")

writetxtcodes2db(RMariaDBcon, tablename, codesfile, columnnames)

Arguments

RMariaDBcon	database connection object created by RMariaDB::dbConnect
datfile	character path to the .dat file
tablename	character name of the database table where the data will be inserted
sep	field separator character in the input file (see RMariaDB::dbWriteTable)
productcodestart	numeric begining of a comext product code
file.types	character vector of named SQL field types (see RMariaDB::dbWriteTable)

See Also

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

Examples

## Not run:  # Load data from a .dat file into the database
# Create a database connection
con <- RMariaDB::dbConnect(RMariaDB::MariaDB(), dbname = "test")
# Create a temporary dat file to be written to the database
datfile <- tempfile(fileext = ".dat")
write(c("col1,col2\n1,2"),datfile)
readLines(datfile)
# Write the temporary dat file to the database
writedat2db(datfile = datfile, tablename = "temporary", RMariaDBcon = con)
# Disconnect from the database
RMariaDB::dbDisconnect(con)

## End(Not run)
## Not run:  # Load product codes and country codes into the database
con <- RMariaDB::dbConnect(RMariaDB::MariaDB(), dbname = "test")
# Write product codes to the database
writetxtcodes2db(con, "raw_comext_product", "~/R/tradeharvester/data_raw/comext/201707/text/english/CN.txt",
              columnnames =  c("productcode", "datestart", "dateend",
                               "productdescription", "datestart2", "dateend2"))
# Write reporter codes to the database
writetxtcodes2db(con, "raw_comext_reporter", "~/R/tradeharvester/data_raw/comext/201707/text/english/REPORTERS.txt",
              columnnames = c("reportercode", "datestart", "dateend",
                              "reporter", "datestart2", "dateend2"))
# Write partner codes to the database
writetxtcodes2db(con, "raw_comext_partner", "~/R/tradeharvester/data_raw/comext/201707/text/english/PARTNERS.txt",
              columnnames = c("partnercode", "datestart", "dateend",
                              "partner", "datestart2", "dateend2"))
# Write units to the database

# Write unit descriptions to the database


RMariaDB::dbDisconnect(con)

## End(Not run)

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