How would I add a where statement to exclude rows already loaded into the table?
LOAD DATA LOCAL INFILE ‘C:\FilePath\File.csv’
INTO TABLE table
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’
(index, name);
Hi DB2,
You can specify “REPLACE” or “IGNORE” after the filename and before “INTO TABLE” to have the import either replace existing rows with new rows, or ignore new rows which already exist in the database. The matching of rows is done on the basis of the primary key or a unique index.
If you use “LOCAL” in your statement, the default action is the same as if “IGNORE” was specified i.e. the statement you listed should be doing what you want. I would take a copy of your existing table and try the import to see if it works. If it is not doing what you want, let me know.
The REPLACE statement works if I want to change all the columns, but what if I only want to change data in a single column.
When I tried to run on single column, I lost the data in the other columns.
Unfortunately if you don’t specify a value for all the columns, the non-specified columns will be set to their default values - not what you’re wanting here I think.
The only other way I think you will be able to do it is programmatically - loop through the csv file line by line and run a SQL UPDATE command for every line. You could either do this at the Excel end (if you’re happy with VBA and ADO) or in FactoryPMI (if you’re happy with Python).
Not that I’m an SQL expert, but wouldn’t it be prudent to import the csv file into a temp table first anyway? You could then run queries to copy only the columns you need, and there would be some protection against bad data in your csv file (this may or may not be a concern in your case).