Page 1 of 1

how to detect the csv delimiter while importing to db

Posted: Thu Feb 20, 2014 1:15 am
by barbiee
i wrote a php file to import a csv file directly to the mysql db. at the begining i wrote the code with fgetcsv and delimiter "\t" . but now there occurs a problem the user wants to import files with comma separated also(i mean he wants the pgm to support both type files). how can i detect the delimiter used in the importing file? or how can i modify my function so that it works also with comma delimiter.

Code: Select all

while (($data = fgetcsv($handle, 1000, "\t")) !== FALSE) 
                                                    {
                                                    //print_r($data);
                                                     $i = 0;
                                                     $values = array();
                                                       foreach($dbkey as $k) {
                                                           
                                                            if (!empty($data[$i])) {
                                                               
                                                         if($k!='patient_date'){
                                                              $values[$k] = $data[$i];
                                                               $i++;
                                                         }
                                                           else{
                                                              $str=$data[$i];
                                                                     $values[$k]=DateTime::createFromFormat('d/m/y H:i', $str)->format('Y-m-d');
                                                               $i++; 
                                                           }    
                                                               
                                                            }
                                                           
                                                           else{
                                                               $values[$k]='';
                                                               $i++;
                                                           }
                                                        }
                                                                                                                         
                                $ks = "`" . implode("`, `", array_keys($values)) . "`";
                                $values = "'" . implode("', '", $values) . "'";
                                $sql = "INSERT INTO ".WP_eemail_TABLE_SUB." ({$ks}) VALUES ({$values})";
                                $wpdb->get_results($sql);
                                                 }

Re: how to detect the csv delimiter while importing to db

Posted: Thu Feb 20, 2014 6:59 am
by Celauran
You could modify the function to accept the delimiter as a parameter. Detecting the delimiter may be more difficult. You could try reading in the first line of the file, checking for the existence of any of a set of delimiters, and setting the delimiter variable accordingly. This, however, runs the risk of false positives (eg. a tab-delimited file containing commas).

Re: how to detect the csv delimiter while importing to db

Posted: Thu Feb 20, 2014 1:22 pm
by requinix
If your CSV has a line of headers, that's the best place to look for a delimiter: the headers will be mostly letters, numbers, and spaces, so the presence of tabs or commas is a good indicator.
But like Celauran said that's still risky, so +1 to passing the delimiter as an argument.