• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 596
  • Last Modified:

MYSQL pipe email csv into MYSQL

Hello,

I have a email with sales data that will be sent daily to me and I need the email to be piped to, maybe a php script, that will take the attached csv (with the sames data in it) and load it into a MySQL database.

No issues with setting up email piping but issue with getting data from attachement.

I have had a bit of a search but have not had much luck when it comes to the attachement side of things, I am able to receive as a text document if required but again as an attachement to the email.



Any thoughts?
0
AUCKLANDIT
Asked:
AUCKLANDIT
  • 4
2 Solutions
 
Ray PaseurCommented:
Got any test data?
0
 
AUCKLANDITAuthor Commented:
Site Code,Site Name,Sales Date,Product Code,Product Name,Amount,100579,BLA BLA ,10/05/2011 0:00,31003,V,10865.69
0
 
Ray PaseurCommented:
Ok, great.  Please send me a test message with the EXACT email and attachment that you expect to send to your pipe script.  You can use my public email address, Ray.Paseur at Gmail (you know the rest ;-)
0
 
Ray PaseurCommented:
http://www.laprbass.com/RAY_temp_callum.php
Outputs:
string(330) "Site Code,Site Name,Sales Date,Product Code,Product Name,Volume
185164, STRATFORD,13/05/2011 0:00,32002,91,12521.74
183972, PALM BEACH,13/05/2011 0:00,32002,91,16022.15
108046, CURLETTS ROAD,13/05/2011 0:00,32002,91,15738.38
105148, MIRAMAR,13/05/2011 0:00,32002,91,11121.09
100232, RIVERHEAD,13/05/2011 0:00,32002,91,4796.76"

You may have to make some modifications to this code, so I will try to explain.  This simulates an email PIPE script that reads from STDIN.  Here is what I did.  

Line 5: Since this is an email pipe script, it will not have browser output.  But if you capture the script output in the buffers, you can grab the buffers and email them to yourself or write them to a log file.  Sounds hokey, but it works to get the debugging information you need with an asynchronous script.

Line 8 - 135: This is what the PIPE will retrieve from STDIN.

Line 138-141: This enables us to tell that the email is legit.  You can use whatever code and data makes sense for your app.

Line 152-166: Detect the mime boundary string.

Line 169-179: Use the mime boundary string to detect the edges of the attachment.

Line 182-192: Get the base64-encoded string out of the attachment.  $decoded is the CSV file.  It had a lot of empty lines that looked like ,,,,,

Line 195-203: Get rid of the lines with nothing but commas.

Hope that helps, ~Ray
<?php // RAY_temp_callum.php
error_reporting(E_ALL);

// CAPTURE THE OUTPUT BUFFERS
ob_start();
echo "<pre>";

// SIMULATED CONTENTS OF THE STDIN FILE
$stdin = <<<STDIN

Delivered-To: ray.paseur@gmail.com
Received: by 10.216.162.1 with SMTP id x1cs53961wek;
        Fri, 13 May 2011 18:16:27 -0700 (PDT)
Received: by 10.213.33.193 with SMTP id i1mr38627ebd.142.1305335787264;
        Fri, 13 May 2011 18:16:27 -0700 (PDT)
Return-Path: <callum.henderson@caljo.com.au>
Received: from mail-ey0-f172.google.com (mail-ey0-f172.google.com [209.85.215.172])
        by mx.google.com with ESMTPS id 58si6495056eef.13.2011.05.13.18.16.26
        (version=TLSv1/SSLv3 cipher=OTHER);
        Fri, 13 May 2011 18:16:26 -0700 (PDT)
Received-SPF: neutral (google.com: 209.85.215.172 is neither permitted nor denied by best guess record for domain of callum.henderson@caljo.com.au) client-ip=209.85.215.172;
Authentication-Results: mx.google.com; spf=neutral (google.com: 209.85.215.172 is neither permitted nor denied by best guess record for domain of callum.henderson@caljo.com.au) smtp.mail=callum.henderson@caljo.com.au
Received: by mail-ey0-f172.google.com with SMTP id 13so1319448eye.17
        for <Ray.Paseur@gmail.com>; Fri, 13 May 2011 18:16:26 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.14.11.5 with SMTP id 5mr842171eew.92.1305335785870; Fri, 13
 May 2011 18:16:25 -0700 (PDT)
Received: by 10.14.119.5 with HTTP; Fri, 13 May 2011 18:16:25 -0700 (PDT)
In-Reply-To: <A221FF9A8C054B4B820B5214389AABC214A521@SWLGWEXGP01.gel.local>
References: <A221FF9A8C054B4B820B5214389AABC214A521@SWLGWEXGP01.gel.local>
Date: Sat, 14 May 2011 13:16:25 +1200
Message-ID: <BANLkTi=C2aMFeRsQxT6sCQRWZDoMie=itA@mail.gmail.com>
Subject: Daily Pump Sales csv.CSV
From: Callum Henderson <callum.henderson@caljo.com.au>
To: Ray.Paseur@gmail.com
Content-Type: multipart/mixed; boundary=0016364d1d69d4562804a3322b3c

--0016364d1d69d4562804a3322b3c
Content-Type: multipart/alternative; boundary=0016364d1d69d4562104a3322b3a

--0016364d1d69d4562104a3322b3a
Content-Type: text/plain; charset=ISO-8859-1



--0016364d1d69d4562104a3322b3a
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<p>=A0</p>

--0016364d1d69d4562104a3322b3a--
--0016364d1d69d4562804a3322b3c
Content-Type: application/octet-stream; name="Daily Pump Sales csv.CSV"
Content-Disposition: attachment; filename="Daily Pump Sales csv.CSV"
Content-Transfer-Encoding: base64
X-Attachment-Id: 90e0fb2e3e3dbbe5_0.1

U2l0ZSBDb2RlLFNpdGUgTmFtZSxTYWxlcyBEYXRlLFByb2R1Y3QgQ29kZSxQcm9kdWN0IE5hbWUs
Vm9sdW1lDQoxODUxNjQsIFNUUkFURk9SRCwxMy8wNS8yMDExIDA6MDAsMzIwMDIsOTEsMTI1MjEu
NzQNCjE4Mzk3MiwgUEFMTSBCRUFDSCwxMy8wNS8yMDExIDA6MDAsMzIwMDIsOTEsMTYwMjIuMTUN
CjEwODA0NiwgQ1VSTEVUVFMgUk9BRCwxMy8wNS8yMDExIDA6MDAsMzIwMDIsOTEsMTU3MzguMzgN
CjEwNTE0OCwgTUlSQU1BUiwxMy8wNS8yMDExIDA6MDAsMzIwMDIsOTEsMTExMjEuMDkNCjEwMDIz
MiwgUklWRVJIRUFELDEzLzA1LzIwMTEgMDowMCwzMjAwMiw5MSw0Nzk2Ljc2DQosLCwsLA0KLCws
LCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCws
LA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCws
DQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwN
CiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0K
LCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQos
LCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiws
LCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCws
LCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCws
LA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCws
DQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwN
CiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0K
LCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQos
LCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiws
LCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCws
LCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCws
LA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCws
DQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwN
CiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0K
LCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQos
LCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiws
LCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCws
LCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCws
LA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCws
DQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwN
CiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0K
LCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQos
LCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiws
LCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCws
LCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCws
LA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCws
DQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwN
CiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0K
LCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQos
LCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiws
LCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCws
LCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCws
LA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCws
DQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwN
CiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0K
LCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQos
LCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiws
LCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCws
LCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCws
LA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCws
DQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwN
CiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0K
LCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQos
LCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiws
LCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCws
LCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCws
LA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCws
DQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwN
CiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0K
LCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQos
LCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiws
LCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCws
LCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCws
LA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCws
DQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwN
CiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0K
LCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQos
LCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiws
LCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCws
LCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCws
LA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCws
DQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwN
CiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0K
LCwsLCwNCiwsLCwsDQo=
--0016364d1d69d4562804a3322b3c--

STDIN;


// AUTHENTICATION DATA - MESSAGE MUST CONTAIN THESE FIELDS
$name = 'filename="Daily Pump Sales csv.CSV"';
$subj = 'Subject: Daily Pump Sales csv.CSV';
$from = 'From: Callum Henderson <callum.henderson@caljo.com.au>';


// AUTHENTICATION - CHECK 'SUBJECT' AND 'FROM' FOR KNOWN VALUES
if ( (!strpos($stdin, $subj)) || (!strpos($stdin, $from)) )
{
    echo PHP_EOL . "AUTH FAILED FOR $subj AND $from";
    die();
}


// DETECT THE CONTENT BOUNDARY LIKE THIS LINE
// Content-Type: multipart/mixed; boundary=0016364d1d69d4562804a3322b3c
$signal = strtoupper('Content-Type: multipart/mixed;');
$array  = explode(PHP_EOL, $stdin);
foreach ($array as $line)
{
    $line_u = trim(strtoupper($line));
    if (strpos($line_u, $signal) === 0)
    {
        $x = explode('boundary=', $line);
        $boundary = '--' . trim($x[1]);
        // echo PHP_EOL . "BOUNDARY DETECTED $boundary";
        break;
    }
}


// DETECT THE CONTENT WITH THE FILENAME
$array  = explode($boundary, $stdin);
foreach ($array as $block)
{
    if (strpos($block, $name))
    {
        $attachment = trim($block);
        // echo PHP_EOL . "BLOCK DETECTED $attachment";
        break;
    }
}


// ISOLATE THE BASE64-ENCODED DATA IN THE ATTACHMENT (AFTER BLANK LINE)
$array = explode(PHP_EOL, $attachment);
$nuevo = array();
$swtch = FALSE;
foreach ($array as $line)
{
    if ($swtch) $nuevo[] = $line;
    if (trim($line) == NULL) $swtch = TRUE;
}
$attachment = implode(PHP_EOL, $nuevo);
$decoded = base64_decode($attachment);


// REMOVE THE EMPTY LINES FROM THE CSV FILE
$array = explode(PHP_EOL, $decoded);
$nuevo = array();
foreach ($array as $line)
{
    $line = trim($line, ',');
    if (!empty($line)) $nuevo[] = $line;
}
$attachment = trim(implode(PHP_EOL, $nuevo));


// SHOW THE WORK PRODUCT
var_dump($attachment);

Open in new window

0
 
Ray PaseurCommented:
Unbelievable.  

I wrote the programming for you in the form of a demonstration script, installed it on my server, tested it to prove that it worked correctly and posted it for you here at EE.  And instead of even asking a question or posting a comment, you ignored the answer for more than a month then closed the question with a bad grade.  

Have you read this?
http://www.experts-exchange.com/help.jsp#hs=29&hi=403

Why should I or anyone help with your next question?
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now