Exporting/Importing tables with different date format

Question

Im having this problem, that when i export my table as csv file and open it on the excel: the date seems to have different format (in the database : yyyy-mm-dd, while on the excel dd/mm/yyyy)

Is there a way of solving these things to have the same format either when exporting or importing

This is my code when importing

  elseif ($_POST["program"]=="dengue"){
        $filename = $_FILES['file']['tmp_name']; 
        $file = fopen($filename, "r");
        while (($getData = fgetcsv($file, 10000, ",")) !== FALSE)
         {



           $sql = "INSERT into lcho_dengue_activities (district_id,barangay_id,month,year,dengue_ind1,dengue_ind2,dengue_ind3,dengue_ind4,dengue_ind5,dengue_ind6,dengue_ind7,dengue_ind8,dengue_ind9,dengue_ind10,dengue_ind11,date) 
               values ('".$getData[0]."','".$getData[1]."','".$getData[2]."','".$getData[3]."','".$getData[4]."','".$getData[5]."','".$getData[6]."','".$getData[7]."','".$getData[8]."','".$getData[9]."','".$getData[10]."','".$getData[11]."','".$getData[12]."','".$getData[13]."','".$getData[14]."','".$getData[14]."')";


            if(!mysqli_query($con, $sql))
            {
            echo ("Error description: " . mysqli_error($con));  
            }
            else {
                  echo "<script type=\"text/javascript\">
                    alert(\"CSV File has been successfully Imported.\");
                    window.location = \"imports.php\"
                </script>";
            }
        }

While this is on exporting

            if($_POST["program"]=="dengue"){
      // get records from database
    $query = $db->query("SELECT * FROM lcho_dengue_activities ");

     if($query->num_rows > 0){
$delimiter = ",";
$filename = "dengueactivities" . date('Y-m-d') . ".csv";

//create a file pointer
$f = fopen('php://memory', 'w');


//output each row of the data, format =line as csv and write to file pointer
while($row = $query->fetch_assoc()){

    $lineData = array( $row['district_id'], $row['barangay_id'], $row['month'], $row['year'],$row['dengue_ind1'],$row['dengue_ind2'],$row['dengue_ind3'],$row['dengue_ind4'],$row['dengue_ind5'],$row['dengue_ind6'],$row['dengue_ind7'],$row['dengue_ind8'],$row['dengue_ind9'],$row['dengue_ind10'],$row['dengue_ind11'],$row['date']);

    fputcsv($f, $lineData, $delimiter);
}

//move back to beginning of file
fseek($f, 0);

//set headers to download file rather than displayed
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="' . $filename . '";');

//output all remaining data on a file pointer
fpassthru($f);

} exit;

Need to have same format

**Warning:** You are wide open to [SQL Injections](http://php.net/manual/en/security.database.sql-injection.php) and should really use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](http://php.net/manual/en/pdo.prepared-statements.php) or by [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Never trust any kind of input, especially that which comes from the client side. Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). — Dharman, Jan 18 '19 at 00:03
Why do you seem to be using arbitrary constants in your code? e.g. `dengue` & `program` — Dharman, Jan 18 '19 at 00:05
I've edited it. Thanks. those are part of my codes, i just wanted to know if there is a way of importing date from .csv file to sql having the format yyyy-mm-dd because .csv file have the format of dd/mm/yyyy or exporting from sql where yyyy-mm-dd is the format and output it with the same format on .csv — Carlo E. Barrogo, Jan 18 '19 at 00:14
Why not just format it in the way you want? Use [DateTime class](https://stackoverflow.com/questions/54245787/how-to-use-the-datetime-class-dealing-with-conversions-formatting-diffs-an) — Dharman, Jan 18 '19 at 00:17

Bullschmidt · Answer 1 · 2019-01-18T06:28:43.593

You wrote in short:

"Im having this problem, that when i export my table as csv file and open it on the excel: the date seems to have different format (in the database : yyyy-mm-dd, while on the excel dd/mm/yyyy)"

Seems like that's just the nature of the beast. Remember that a .csv file is not an Excel file (which may store exact instructions on how to format everything). So when Excel opens a .csv file it tries to make some guesses about how to format columns of data and sometimes that may not be exactly what you intend.

A similar thing is trying to show something like 2 decimal places where Excel shows whatever it considers significant such as 0.12, 0.13, 0.1, 0. Another similar weird thing I've seen is if the first 10 or 20 rows or something in the .csv file are blank and then Excel often seems to have no clue on how to properly format the column.

To REALLY see exactly what is in a .csv file you can open it up in some kind of word processor such as a coding editor.

Exporting/Importing tables with different date format

1 Answers1