ssquare
1 year ago

Catch error if arise on a loop but continue ro process other data from the loop.

Posted 1 year ago by ssquare

Scenario:

I am trying to dump data from csv file to MsSQL database. My code is as:

        /*here skip is just 0 or 1 if skip is 0 later that mapped row will be ignored. Map data contains index array which will later used to map csv column to database column*/

        $limit = $this->service->getSettingsNameValue('chunk_limit');
            $skip = $request->skip;
            $map_data = $request->row;

    /*Just reading csv file. Have used csv league*/
        $db_header_obj = new Sos_contribution();
            $db_header = $db_header_obj->getTableColumns();
            $csv_file_path = storage_path('app/files/sos_contribution/').$request->filename;
            $csv = Reader::createFromPath($csv_file_path, 'r');
            $csv->setOutputBOM(Reader::BOM_UTF8);
            $csv->addStreamFilter('convert.iconv.ISO-8859-15/UTF-8');
            $csv->setHeaderOffset(0); //set the CSV header offset
            $csv_header = $csv->getHeader();    

            $rec_arr = array();
            $records = array();
            $records_arr = array();

            $stmt = (new Statement())
            ->offset($offset)
            ->limit($limit)
            ;

            $records = $stmt->process($csv);

            foreach ($records as $record) 
            {
                $rec_arr[] = array_values($record);
            }

            $records_arr = $this->service->trimArray($rec_arr);

            if(count($records_arr)>0)
            {
                foreach($records_arr as $ck => $cv){

                    $sos_contribution_arr = array();
                    foreach ($map_data as $mk => $mv) {
                        if(!isset($skip[$mk])){

                                $data_type = $this->service->getDatabaseColumnType($this->table,$mv);
                                if($data_type == 'date' || $data_type == 'datetime' || $data_type == 'timestamp'){
                /*just little piece of formatting of datetime*/
                                    $datetime =  (array)$cv[$mk];
                                    $dt = array_shift($datetime);
                                    $dt = date('Y-m-d h:i:s', strtotime($dt));
                                    $sos_contribution_arr[$mv] = $dt;
                                }else{
                                    $sos_contribution_arr[$mv] = $cv[$mk];
                                }   

                        }
                    }


        /*CATCH ERROR HERE*/
                    Sos_contribution::where('voter_state_id',$sos_contribution_arr['voter_state_id'])
                        ->update($sos_contribution_arr);

                   
              }
        }

Problem:

As I have huge csv file, there might be some typo errors in some column for example here example having some string character in integer column.

So, for example if there are 10 rows and there is an sql error for row 2. this means there is an string character mapped to int column. In that case, it just throws error and halt the process.

But, what I want is not to halt the process, instead record the row number and continuing other row. In this case, store 2 in a variable and continue processing 3-10 and later return that row number.

Is that possible?

What might be the best way to deal with this as later I want to queue this job and later send email to the owner after the completion of job with failed row number.

Example error: SQLSTATE[22018]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Conversion failed when converting the nvarchar value 'abx' to data type tinyint.

Please sign in or create an account to participate in this conversation.