A friend was having trouble with a CSV file that she needs to process via PHP before saving to database. The CSV files uploaded to the server have inconsistent date formats such as:
|
1 2 3 4 5 |
November 1, 2016 Dec.6,2016 nov-10-2016 12122016 12/10/2016 |
So I prepared a PHP script that lists possible date formats in an array $possible_date_formats and then iterates through the dates read from the CSV file, matching each against the date formats in the array.
Assuming you have a .csv file with tab-delimited data such as this:
|
1 2 3 4 5 6 |
id date name email 111 "November 1, 2016" "Hanna" [email protected] 112 "Dec.6,2016" "Lorem" [email protected] 113 "nov-10-2016" "Isum" [email protected] 114 "12122016" "Dolor" [email protected] 115 "12/10/2016" "Sit" [email protected] |
My PHP file will output this when it’s run:
|
1 2 3 4 5 |
November 1, 2016 -> 2016-11-01 Dec.6,2016 -> 2016-12-06 nov-10-2016 -> 2016-11-10 12122016 -> 2016-12-12 12/10/2016 -> 2016-12-10 |
And below is the PHP file I’m talking about.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
<?php date_default_timezone_set("Asia/Manila"); $delimiter = "\t"; $date_index = 1; // expected index of date in imported CSV data $csv_file_path = 'csv/sample.csv'; $possible_date_formats = array( "F j, Y", // November 1, 2016 "M.j,Y", // Dec.6,2016 "M-d-Y", // nov-10-2016 "mdY", // 12122016 "m/d/Y" // 12/10/2016 ); $arr_data = array(); $fp = fopen($csv_file_path, 'r'); $data_row_ctr = 1; // for checking row number later while ( !feof($fp) ) { $line = fgets($fp, 2048); $data = str_getcsv($line, $delimiter); if($data_row_ctr > 1) { // process only line above 1 because 1 is headers doSomethingWithData($data); } $data_row_ctr++; } fclose($fp); function doSomethingWithData($data){ global $date_index; $arr_data['csv_date'] = $data[$date_index]; $fix_date_result = fix_date($arr_data['csv_date']); if($fix_date_result === false){ echo "\n".$arr_data['csv_date']." -> FALSE\n"; } else { echo "\n".$arr_data['csv_date']." -> ".$fix_date_result->format("Y-m-d")."\n"; } } function fix_date($date_string){ global $possible_date_formats; foreach ($possible_date_formats as $key => $value) { $result = DateTime::createFromFormat($value, $date_string); if (is_a($result, 'DateTime')) { return $result; } } return false; } |
Nice coding!