Mysql query for insert records from csv file to mysql Db

Question

I am trying to insert data from csv file to mysql database. Data is inserted to DB. But not in the way I expected. In my case there are several text files which includes employee id, date, in time and out time. Also, I want to validate this if there is one record for the same employee number with same date it should not allow to insert the any data.

My Code

$fdate = array_filter(explode(" ", $y));

$csv->emp_id = $epf;
$a_date = date("Y-m-d", strtotime($fdate[9]));
$csv->date= $a_date;

$csv->save();

$check = "SELECT * FROM daily_attendances WHERE emp_id = $epf AND date = '$a_date'";

$sql = DB::select(DB::raw($check));
$count = count($sql);

if($count > 0){
  if ($fdate[11] == "AM") {
        $update_query = "UPDATE daily_attendances SET in_time = '$fdate[10]'  WHERE 
       emp_id = $epf AND date = '$a_date'";
       $sql = DB::select(DB::raw($update_query));

                            } elseif ($fdate[11] == "PM") {
                                $o_time = date("H:i:s", strtotime($fdate[10] . $fdate[11]));
                                $update_query = "UPDATE daily_attendances SET out_time = '$o_time'  WHERE emp_id = $epf AND date = '$a_date'";
                                $sql = DB::select(DB::raw($update_query));


                            }

current output

expected output

Removal of duplicates should probably be done in the database, after you have loaded the CSV data. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 10, 2017 at 4:25
I don't understand your expected output. Whence is the timestamp 18:18:21? It appears in neither of the two original rows. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 10, 2017 at 4:26

Tim Biegeleisen · Accepted Answer · 2017-07-10 04:48:29Z

1

I recommend loading the CSV data into a temporary table first using MySQL's LOAD DATA. Once you have done that, you can insert into your target daily_attendances table using a query along the lines of the following:

INSERT INTO daily_attendances (emp_id, date, in_time, out_time)
SELECT emp_id, date, MAX(in_time), MAX(out_time)
FROM temp
GROUP BY emp_id, date

This answer assumes that the only sort of duplication or missing data is what you showed us in the question. In general, bringing in lots of new data from CSV will be fastest with LOAD DATA. Also, the kind of logic you require to scrub your data should be handled in the database.

answered Jul 10, 2017 at 4:48

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sagar Gautam · Accepted Answer · 2017-07-10 04:42:09Z

0

It's better to check by using SQL query.

INSERT INTO TABLE_2 (id, name) SELECT t1.id,t1.name
FROM TABLE_1 t1
WHERE NOT EXISTS(SELECT id FROM TABLE_2 t2 WHERE t2.id = t1.id)

edited Jul 10, 2017 at 4:42

Sagar Gautam

9,4099 gold badges62 silver badges95 bronze badges

answered Jul 10, 2017 at 4:36

Manjunath Mg

881 silver badge10 bronze badges

Collectives™ on Stack Overflow

Mysql query for insert records from csv file to mysql Db

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related