statuser (version 0.1.9)

convert_to_sql: Convert CSV file to SQL INSERT statements

Description

Reads a CSV file and generates SQL statements to insert all rows. Optionally can also generate a CREATE TABLE statement. The function automatically infers column types (REAL for numeric, DATE for date strings matching YYYY-MM-DD format, TEXT otherwise).

Usage

convert_to_sql(input, output, create_table = FALSE)

Value

Invisibly returns NULL. The function writes SQL statements to the specified output file.

Arguments

input

Character string. Path to the input CSV file.

output

Character string. Path to the output SQL file where the statements will be written.

create_table

Logical. If TRUE, includes a CREATE TABLE statement before the INSERT statements. Default is FALSE.

Details

The function performs the following steps:

1. Reads the CSV file using read.csv() with stringsAsFactors = FALSE

2. Infers SQL column types:

   • Numeric columns become REAL

   • Date columns (matching YYYY-MM-DD format) become DATE

   • All other columns become TEXT

3. If create_table = TRUE, generates a CREATE TABLE statement using the base filename (without extension) as the table name

4. Generates INSERT INTO statements for each row

5. Writes all SQL statements to the output file

Single quotes in text values are escaped by doubling them (SQL standard). Numeric values are inserted without quotes, while text and date values are wrapped in single quotes.

Examples

# Convert a CSV file to SQL (INSERT statements only)
tmp_csv <- tempfile(fileext = ".csv")
tmp_sql <- tempfile(fileext = ".sql")
write.csv(
  data.frame(id = 1:2, value = c("a", "b"), date = c("2024-01-01", "2024-02-02")),
  tmp_csv,
  row.names = FALSE
)
convert_to_sql(tmp_csv, tmp_sql)

# Convert a CSV file to SQL with CREATE TABLE statement
convert_to_sql(tmp_csv, tmp_sql, create_table = TRUE)