Jump to content

Add Column and Value existing CSV


eugenevz

Recommended Posts

Hi

 

I have a script which picks up CSV files and inserts into a table (mysql).

 

I am a beginner and finding it difficult to get the script to add an additional column to existing CSV, and add value to column, for each row...

 

Here is the script.... 

How can I manipulate $csv and add a column (including separator) and value ($filedate) for each row?  Help much appreciated.

 

for example:

 

CSV contains:

Number, Name, LastName

1, John, Thomas

2, Jeffrey, James

 

I need to add:

Number, Name, LastName, Date

1, John, Thomas, 2014-10-31

2, Jeffrey, James, 2014-10-31



<?php


#! /usr/bin/env php
$databasehost = "localhost"; 
$databasename = "****"; 
$databasetable = "****"; 
$databaseusername="****"; 
$databasepassword = "****"; 
$fieldseparator = ","; 
$lineseparator = "\n";
$files = glob("*ETF_*.csv");
$filedate = "2014-10-31";


if (empty($files)) {
    die("No records were loaded.");
}
else 
foreach($files as $csv)
$etf_array = array(
            PDO::MYSQL_ATTR_LOCAL_INFILE => true,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
{
try {
    $pdo = new PDO("mysql:host=$databasehost;dbname=$databasename", 
        $databaseusername, $databasepassword, $etf_array   
    );

catch (PDOException $e) {
    die("database connection failed: ".$e->getMessage());
}
$affectedRows = $pdo->exec("
      LOAD DATA LOCAL INFILE ".$pdo->quote($csv)." INTO TABLE `$databasetable`
      FIELDS TERMINATED BY ".$pdo->quote($fieldseparator)."
      LINES TERMINATED BY ".$pdo->quote($lineseparator)."
      IGNORE 1 LINES");


echo "Daily - Loaded a total of $affectedRows records from $csv.\n"; 
}
?>

Edited by Ch0cu3r
Link to comment
Share on other sites

I assume you only want to add todays date to the database and not the csv file aswell?

 

Add the following to the end of your query

 SET date_column = CURRENT_DATE()

change date_column to the actual name of your column you want to add the date to

 

NOTE When posting code please wrap it within


tags (or click the <> button in the editor)

Edited by Ch0cu3r
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.