How to fetch the filename from CSV and insert it into one of columns of a table

Question

Suppose mt table name is Table_1

Structure of the table is something like:

EMP_id, Emp_Name, File_Name_Input_Date (columns)

I am loading Table_1 from a CSV file. The name of this CSV file is daily_01122014.csv

I am using vertical copy command to load this table:

ex copy Table_1 from '/location/daily_01122014.csv'delimiter ',';

It will load the table, but column name File_Name_Input_Date will be left blank, and I have to hard code it.

What I want is to write a shell script which will dynamically read file name and load it into column File_Name, and also extract date from file name.

i.e: If file name is daily_01122014.csv, then while loading Table_1 column File_Name should be daily_01122014.csv and Input_Date should be 2014-12-01. Since I have 50-60 files, I can not do it manually.

Answer 1

What you can do is adding this string to the file before you load the data to Vertica See here an example:

[dbadmin@DCXLG001 tmp]$ cat file001.csv
1,Adrian
[dbadmin@DCXLG001 tmp]$ ls -1 file*.csv|xargs -I% sed -i 's/$/,%/' %
[dbadmin@DCXLG001 tmp]$ cat file001.csv
1,Adrian,file001.csv

- where i have a file called file*.csv and i add the name of the file to the end of each line in the actual file. You can do this to any more then one file. I would use the operation system resources to do this transformation then trying to create a filler using Vetica's copy command

Answer 2

You can just use CURRENT_LOAD_SOURCE() inside your COPY:

COPY t (c1, c2, c3 AS CURRENT_LOAD_SOURCE()) 
FROM '/home/load_file_1' ON exampledb_node02, '/home/load_file_2' ON exampledb_node03 
DELIMITER ',';

Alternatively, you can use a shell script:

#!/bin/bash
for INPUT_FILE in /path/to/file*.dat; do
vsql -c "copy test_file_name_insert
(
input_file_name as '$INPUT_FILE',
id,
name
)
from '/home/test_input_file.txt'
delimiter '|' ;"
done