How do I extract data from a text string in Excel?
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.
It is easy to get all the email IDs from these CSV files if there are only 4-5 records but in the case of bulk records, it will be quite difficult to extract email addresses and use them for sending the email campaign.
Here, in this blog post, we are going to cover how 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, and addresses from Excel, and 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 addresses from the raw CSV data, you just need to follow the 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 it as per your wish.
Now create a few files and folders as given in the below image.
3. Now copy and paste the below code into 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 emails from 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 from 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;
/*** By saving the file to the upload folder you can check whether your file has been uploaded or not. Clean
this folder if you find it necessary after a certain time when a bulk of files 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-]+@[\._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 "Something 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 "Something 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: light blue;
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 complete all the above procedures and run the code then you will get the option to select a CSV file 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 extracted email address.
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 text in Excel? How do I extract data from a text string in Excel?
We have also seen deeply Extracting email addresses from a text in Excel, Extracting all emails from the text, Extracting multiple email addresses from Excel, and Extracting email addresses from the Excel column.
In case of any queries, you can write to us at a5theorys@gmail.com 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 feedback in the comment section below.
Have a great time! Sayonara!
