1

I'm developing a Java swing application with back end Database MySQL . I've a screen to upload my datas to the Database using the Query like

LOAD DATA local INFILE  'D:\\TEMP\\import\\jan_jun2013.txt' 
INTO TABLE slsdata 
FIELDS TERMINATED BY '\t'   
LINES TERMINATED BY '\n'  
IGNORE 1 LINES;  

Here the datas are like from January to June. But if i want to skip any month data or to upload only a particular month then how will i modify the above query ?

In my database table , the date column is 'SLDATE'.

The Same question i've asked in some other forum's, but i'm not able to get any suggestions there!

Any suggestions please.?

1

IMHO there is no a perfect solution for such scenario but you can utilize IGNORE clause.

For example if you were to have an auto_increment id column in your table and you already have a row in it with a known id then your query to import only rows for february might look like this

LOAD DATA local INFILE  '/path/to/your/file.txt' IGNORE
INTO TABLE slsdata 
FIELDS TERMINATED BY '\t'   
LINES TERMINATED BY '\n'  
IGNORE 1 LINES
(@sldate)
SET id = IF(MID(@sldate, 6, 2) = '02', NULL, 1), sldate = @sldate

What it does it reads values of sldate field in @slfield variable then in SET clause it checks whether it's february and if it is then assigns NULL to id column and if not then assigned predefined existing value 1 violating a UNIQUE constraint and IGNORE clause does exactly that it skips a row in such a case.


That being said I'd recommend another approach:

  1. create a temporary staging table without any constraints
  2. load whole file in it
  3. do all necessary cleansing, validating against ref tables, transformations etc. on required subset of data (e.g. for particular month) utilizing all SQL capabilities
  4. insert required subset of data from staging table into your factual table
  5. truncate or drop staging table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.