1

I've created a script that exports a bunch of MySQL tables to csv. Script works fine except I need to remove the double quotes before each row is thrown into the CSv file.

I'm using str_replace which works when I need replace any instances of |. However it won't work on double quotes for some reason. I think I must be missing something obvious here.

require_once 'mysqliconn.php';

$mysql_tables = array("OutputApplicationsView", "OutputComponentsView", "OutputDevicesView", "OutputDisksView", "OutputInstalledSoftwareView", "OutputModulesView", "OutputOraclePTVView", "ScopeListView");

    foreach ($mysql_tables as $mysql_table) {

        echo "<br> Exporting $mysql_table to CSV file. Please wait.</br>";

        $sql = "SELECT * FROM ". $mysql_table . " LIMIT 10";

        $stmt = $myconn->prepare($sql);
        if($stmt === false) {trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $myconn->error, E_USER_ERROR);}

        $execute = $stmt->execute();
        if($execute === false) {trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $myconn->error, E_USER_ERROR); exit;}

        $results = $stmt->get_result();

        $array = $results->fetch_all(MYSQLI_ASSOC);

        $file = $mysql_table . ".txt";

        $fp = fopen($file, 'w');

            foreach ($array as $row) {

            $row = str_replace("|", ":", $row); //works fine
            $row = str_replace('"', '', $row);  //this part won't work.
            $row = preg_replace('/[\x00-\x08\x0B\x0C\x0E-\x1F\x7F-\x9F]/u', '', $row);
                        $row = preg_replace('/[\x00-\x1F\x7F-\xA0\xAD]/u', '', $row);

            fputcsv($fp, $row, "|");

                }

        fclose($fp);

    }

mysqli_close($myconn);
?>

biggambino
  • 23
  • 1
  • 4

1 Answers1

5

The call to str_replace() should be removing the double quotes. What might be happening is that fputcsv() is "helpfully" putting them back in for you. You could try adding a value for the optional enclosure parameter (which defaults to a double quote if not provided) to see if the double quotes in your output are changed to something else.

   fputcsv($fp, $row, "|", "^");

https://www.php.net/manual/en/function.fputcsv.php

If that turns out to be the case then you may have to just output the data manually, something like this would probably do the trick.

   fwrite($fp, implode("|", $row));
ArchCodeMonkey
  • 2,264
  • 1
  • 10
  • 9
  • Thanks for the reply. I replace the fputcsv line with fwrite ($fp, implode ('|', $row)); and it worked. – biggambino Aug 22 '19 at 14:49
  • @biggambino I'm glad it worked for you. Would you mind marking the question as answered so other people who have the same problem in future know what to try. – ArchCodeMonkey Aug 23 '19 at 01:08
  • @ArchCodeMonkey thank you for this it helped me. In case if any want to add each row in new line then we need to add . PHP_EOL fwrite($fp, implode("|", $row). PHP_EOL); – Shridhar Jan 02 '23 at 13:12