How do I extract data from a text string in Excel?/ Extract email addresses from Excel column

Hello Friends, In this blog tutorial, I am going to explain how to extract email addresses from bulk data in the CSV column.

let’s assume you have some data in the excel column the same as shown in the below image.

excelreader4

It is easy to get all the email id from these CSV files if would only 4-5 records but in the case of bulk records, it will be quite difficult to extract email address and use them for sending the email campaign.

Here, in this blog post, we are going to cover how do I extract email addresses from a text string? How do I extract email addresses from the text in Excel? How do I extract data from a text string in Excel?

We will also see Extract email addresses from a text in Excel, Extract all emails from the text, Extract multiple emails, addresses from Excel, Extract email addresses from the Excel column.

so what is the procedure to extract email addresses from the CSV column where we have multiple data in the form of a string?

This is a quite simple process to extract email address from the raw CSV data, you just need to follow the below-given procedure.


1. Install all the applications to develop this functionality.

Software required to develop this functionality are :

NetBeans IDE
Xampp Server


2. Create a new project name as ‘excel reader’ though you can name as per your wish.

Now create a few files and folders as given in the below image.

excelreader1


3. Now copy and paste the below code in the respective files as shown in the file hierarchy.

index.php

[code php]
<?PHP
session_start();
?>
<html>
<head>
<link href=”style.css” rel=”stylesheet” type=”text/css”/>
<meta http-equiv=”Content-Type” content=”text/html; charset=windows-1252″>
<link href=”css/style.css” rel=”stylesheet” type=”text/css”/>
<link href=”bootstrap/css/bootstrap.css” rel=”stylesheet” type=”text/css”/>
<link href=”bootstrap/css/bootstrap-theme.min.css” rel=”stylesheet” type=”text/css”/>
<title>select all email form a CSV file</title>
</head>
<body>
<div class=”container”>
<div class=”row”>
<h1 class=”main_heading”>Select all email from a CSV file</h1>
<div id=”main”>
<div id=”login”>
<h2>Fetch Emails form the bulk data in CSV file</h2>
<hr>
<div id=”right”>
<div id =”form1″ >
<form action=”<?php echo $_SERVER[“PHP_SELF”]; ?>” method=”post” enctype=”multipart/form-data”>
<label><b>Upload CSV :</b></label>
<label><input type=”file” name=”file” id=”file” /></label>
<input type=”submit” name=”csubmit” id=”csubmit” value=”submit”/>
</form>
</div>
</div>
</div>
</div>
<?php
try {
if (isset($_POST[‘csubmit’])) {
if (isset($_FILES[“file”])) {
//if there was an error uploading the file
if ($_FILES[“file”] [“error”] > 0) {
echo “<script type=’text/javascript’>alert(‘Please choose File for upload’)</script>”;
} else {
$storagename = $_FILES[“file”][“name”];
$_SESSION[‘file_name’] = $storagename;
$id = $_SESSION[‘file_name’];
$row = 1;
/***saving file to upload folder you can check whether your file has been upoaded or not. Clean
this folder if you find necessary after certain time when a bulk of file stored in this folder****/
move_uploaded_file($_FILES[“file”][“tmp_name”], “upload/” . $storagename);
$file = new SplFileObject(“upload/” . $storagename);
$file->setFlags(SplFileObject::READ_CSV);
$file->setCsvControl(‘,’, ‘”‘, ‘\\’);
foreach ($file as $value) {
/**extracting value from file and printing on page**/
foreach ($value as $row) {
$arcsv[] = $row;
}
}
$arcsv = array_unique($arcsv);

$_SESSION[‘csv_value’] = $arcsv;
if (empty($arcsv)) {
$arcsv = $_SESSION[‘csv_value’];

}
function extract_emails_from($string){
preg_match_all(“/[\._a-zA-Z0-9-][email protected][\._a-zA-Z0-9-]+/i”, $string, $matches);
return $matches[0];
}
foreach($arcsv as $key)
{
$text = $key;

$emails[]= extract_emails_from($text);

}
foreach($emails as $row)
{
foreach($row as $key)
{
echo $key.”</br>”;

}
}

$lists1 = json_encode($emails);

}
}
if (!empty($file)) {
/**form having ‘Export CSV’ button’ also takes the value to be downloaded**/
echo”<form action=’csvdown.php’ method=’post’ >
<input type=’hidden’ name=’lists1′ value=’$lists1′ />
<button type=’submit’ value=’Export CSV’ id =’csvsubmit’><u>Export CSV<u></button>
</form>”;
}
}
} catch (MyException $e) {
echo “Some thing went wrong”;
}
?>

</div>
</div>
</body>
</html>
[/code]


csvdown.php

[code php]
<?php
ini_set(“display_errors”, 0);
?>
<?php
try {
$array2 = json_decode($_POST[‘lists1’]);
foreach ($array2 as $key) {
foreach ($key as $key1) {
$array3[][] = $key1;
}
}
header(“Content-type: text/csv”);
header(“Content-Disposition: attachment; filename=file.csv”);
header(“Pragma: no-cache”);
header(“Expires: 0”);
$data = $array3;
$file = fopen(‘php://output’, ‘w’);
fputcsv($file, array(‘Emails ‘));
foreach ($data as $row) {
fputcsv($file, $row);
}
exit();
} catch (MyException $e) {

echo “Some thing went wrong”;
}
?>
[/code]


style.css

[code css]
@import url(http://fonts.googleapis.com/css?family=Raleway);
#main{
min-width: 350px;
margin: 44px auto;
font-family:raleway;
}
span{
color:red;
}
h1.main_heading {
text-align: center;
color: #babae2;
}
h2{
background-color: #b8bfce;
text-align:center;
border-radius: 10px 10px 0 0;
margin: -10px -40px;
padding: 15px;
}
hr{
border:0;
border-bottom:1px solid #ccc;
margin: 10px -40px;
margin-bottom: 30px;
}
#login{
border-radius: 10px;
font-family:raleway;
border: 2px solid #ccc;
padding: 10px 40px 33px;
margin-top: 23px;
background-color: #8c93a2;
}
input[type=text],input[type=email]{
width:99.5%;
padding: 10px;

border: 1px solid #ccc;
padding-left: 5px;
font-size: 16px;
font-family:raleway;
}
input[type=submit]{
width: 37%;
background-color:#FFBC00;
color: white;
border: 2px solid #FFCB00;
padding: 4px;
font-size:20px;
cursor:pointer;
border-radius: 5px;
}
#right{
overflow: hidden;
overflow-wrap: break-word;
width: 104%;
border: 1px dashed rgb(215, 215, 215);
box-shadow: 0px 5px 17px 1px #99A3AD, 0px 0px 40px #EEEEEE;
margin-left: -9%;
padding: 20px;
}
#right p{
padding: 20px;
}
form1 {
padding-top: 27px;
width: 248px;
margin: auto;
}
#right h3 {
margin-top: -8px;
text-align: center;
}
#csvmailcontent{
width:60%px;
font-family:raleway;
margin-top: -4%;
}
#mailsubmit{
background:none!important;
border:none;
padding:0!important;
font: inherit;
border-bottom:1px solid #444;
cursor: pointer;
font-size: 150%;
margin-left: 45%;
}
#csvsubmit{
background:none!important;
border:none;
padding:0!important;
font: inherit;
border-bottom:1px solid #444;
cursor: pointer;
font-size: 150%;
margin-left: 45%;
}
#uploadcsv
{
margin-bottom: 5%;
}
#text1{
margin-top: 1%;
margin-bottom: 11px;
}
input#text1 {}
#or{
margin-left: 45%;
font-size: 20px;
font-family:raleway;
margin-top: -50px;
}
#or:hover{
color: #204d74;
z-index: 8;
}
#file{
color:lightblue;
margin-top: 3px;
margin-bottom: 4px;
}
table {
font-family: ‘Raleway’, sans-serif;
color:#666;
font-size:12px;
text-shadow: 1px 1px 0px #fff;
background:#eaebec;
margin:20px;
border:#ccc 1px solid;
font-size: 16px;
-moz-border-radius:3px;
-webkit-border-radius:3px;
border-radius:3px;
-moz-box-shadow: 0 1px 2px #d1d1d1;
-webkit-box-shadow: 0 1px 2px #d1d1d1;
box-shadow: 0 1px 2px #d1d1d1;
margin: 0 auto;
}
table th {
text-align: center;
padding:21px 25px 22px 25px;
border-top:1px solid #fafafa;
border-bottom:1px solid #e0e0e0;

background: #ededed;
background: -webkit-gradient(linear, left top, left bottom, from(#ededed), to(#ebebeb));
background: -moz-linear-gradient(top, #ededed, #ebebeb);
}
table th:first-child {
text-indent: center;
padding-left:20px;
}
table tr:first-child th:first-child {
-moz-border-radius-topleft:3px;
-webkit-border-top-left-radius:3px;
border-top-left-radius:3px;
}
table tr:first-child th:last-child {
-moz-border-radius-topright:3px;
-webkit-border-top-right-radius:3px;
border-top-right-radius:3px;
}
table tr {
text-indent: center;
padding-left:20px;
}
table td:first-child {
text-align: center;
padding-left:20px;
border-left: 0;
}
table td {
padding:18px;
border-top: 1px solid #ffffff;
border-bottom:1px solid #e0e0e0;
border-left: 1px solid #e0e0e0;
background: #fafafa;
background: -webkit-gradient(linear, left top, left bottom, from(#fbfbfb), to(#fafafa));
background: -moz-linear-gradient(top, #fbfbfb, #fafafa);
}
table tr.even td {
background: #f6f6f6;
background: -webkit-gradient(linear, left top, left bottom, from(#f8f8f8), to(#f6f6f6));
background: -moz-linear-gradient(top, #f8f8f8, #f6f6f6);
}
table tr:last-child td:first-child {
-moz-border-radius-bottomleft:3px;
-webkit-border-bottom-left-radius:3px;
border-bottom-left-radius:3px;
}
table tr:last-child td:last-child {
-moz-border-radius-bottomright:3px;
-webkit-border-bottom-right-radius:3px;
border-bottom-right-radius:3px;
}
table tr:hover td {
background: #f2f2f2;
background: -webkit-gradient(linear, left top, left bottom, from(#f2f2f2), to(#f0f0f0));
background: -moz-linear-gradient(top, #f2f2f2, #f0f0f0);
}
@media only screen and (min-width:768px){
#main {
max-width: 370px;
}
}

[/code]


Note: remember to make one upload folder inside the source project file.

Once you will complete all the above procedures and will run the code then you will get the option for selecting a CSV file and then after submitting the button you will see all the emails in the output result and you will also get an option for downloading the CSV file. See the images below for the extract email address.

excelreader2

excelreader3

Download Source Code


Conclusion:

So using this blog we have learned this amazing functionality to extract the email address from a text string present in one excel column. We have also covered several sections like How do I extract email addresses from a text string? How do I extract email addresses from a text in Excel? How do I extract data from a text string in Excel?

We have also seen deeply Extract email addresses from a text in Excel, Extract all emails from the text, Extract multiple email addresses from Excel, Extract email addresses from the Excel column.

In case of any queries, you can write to us at [email protected] we will get back to you ASAP.

Hope! you would have enjoyed this post to extract the email address from the raw CSV file.

Please feel free to give your important feedbacks in the comment section below.

Have a great time! Sayonara!

Anurag

I am a blogger by passion, a software engineer by profession, a singer by consideration and rest of things that I do is for my destination.