/** * @return \Illuminate\View\View */ public function import() { $import_path = "/var/www/vhosts/dev1/docroot/import/"; // it appears there are some unexpected commas in the data so csv is difficult to fully parse //$import_file = "bup_providers_data.csv"; // using pipe delimeted works better $import_file = "bup_providers_data-pipe.txt"; $terminator = "|"; $file = $import_path . $import_file; $import_statement = "LOAD DATA INFILE '{$file}' INTO TABLE " . HcpImport::getTable() . " FIELDS TERMINATED BY '{$terminator}' OPTIONALLY ENCLOSED BY '\"'\nLINES TERMINATED BY '\n' IGNORE 2 LINES (first_name,m_name,last_name,suffix,address_line1,address_line2,city,state,zip_code,phone)"; // import statement: // LOAD DATA INFILE '/var/www/vhosts/dev1/docroot/import/bup_providers_data-pipe.txt' INTO TABLE hcp_import FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '' LINES TERMINATED BY '\n' IGNORE 2 LINES (first_name,m_name,last_name,suffix,address_line1,address_line2,city,state,zip_code,phone) // fails from PHP due to some driver/buffering issue, haven't found a solution yet. // Field order from SAMSHA csv // First Name,Middle Name,Last Name,Suffix,Address Line 1,Address Line2,City,State,Zip Code,Phone DB::statement($import_statement); $view_data = ['file' => $file, 'sql' => $import_statement]; return view('data.import', ['data' => $view_data]); }