Import csv file

hiramalik · Unread post by **hiramalik** » Wed Jul 15, 2020 12:37 pm

Hi all,
I am just getting started with nuBuilder. This is one of the greatest solutions I have found. However, I am stuck on a point. I want to create a button in a form to import a csv file into existing table. Is there any way I could do that? Any guidance from seniors will be highly appriciated.
Kind Regards

Unread post by **kev1n** » Wed Jul 15, 2020 1:28 pm

Hi,

Here's a ready-to-use example:

https://www.webslesson.info/2019/04/liv ... query.html

The HTML code can be added to a nuBuilder HTML object.

Let me know if you have any questions.

hiramalik · Unread post by **hiramalik** » Wed Jul 15, 2020 2:33 pm

Thanks you so much for you reply kev1n. But unfortunately, I am just getting started with nuBuilder. When I go to the object property of the button on which I want to create this event. I can see multiple tabs. On HTML editor which piece of code do I need to paste from given link? Do I need to paste only following code?

<?php

//import.php

if(isset($_POST["student_name"]))
{
$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");
$student_name = $_POST["student_name"];
$student_phone = $_POST["student_phone"];
for($count = 0; $count < count($student_name); $count++)
{
$query .= "
INSERT INTO tbl_student(student_name, student_phone)
VALUES ('".$student_name[$count]."', '".$student_phone[$count]."');

";
}
$statement = $connect->prepare($query);
$statement->execute();
}

?>

hiramalik · Unread post by **hiramalik** » Wed Jul 15, 2020 2:52 pm

I don't need to fetch the data, I just need to import it.

Unread post by **kev1n** » Wed Jul 15, 2020 3:32 pm

I can give you detailed instructions tonight.

Unread post by **kev1n** » Wed Jul 15, 2020 7:43 pm

1. Save this code in a file called fetch.php:

Code: Select all

<?php

//fetch.php

if(!empty($_FILES['csv_file']['name']))
{
 $file_data = fopen($_FILES['csv_file']['tmp_name'], 'r');
 $column = fgetcsv($file_data);
 while($row = fgetcsv($file_data))
 {
  $row_data[] = array(
   'student_name'  => $row[0],
   'student_phone'  => $row[1]
  );
 }
 $output = array(
  'column'  => $column,
  'row_data'  => $row_data
 );

 echo json_encode($output);

}

?>

2. Save this code in a file called import.php. Modify the host, dbname, user, password if they are different.

Code: Select all

<?php

//import.php

if(isset($_POST["student_name"]))
{
 $connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");
 $student_name = $_POST["student_name"];
 $student_phone = $_POST["student_phone"];
 for($count = 0; $count < count($student_name); $count++)
 {
  $query .= "
  INSERT INTO tbl_student(student_name, student_phone) 
  VALUES ('".$student_name[$count]."', '".$student_phone[$count]."');
  
  ";
 }
 $statement = $connect->prepare($query);
 $statement->execute();
}

?>

3. Place these two files in a new subdirectory of your nuBuilder installation. E.g. libs\csvupload"

4. In your form, create a new object of type HTML and paste this code in the HTML field (in the HTML tab).

Place the following code in a HTML object:

Code: Select all

 <head>
  
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
  <style>
  .box
  {
   max-width:600px;
   width:100%;
   margin: 0 auto;;
  }
  </style>
 </head>
 
 <body>
  <div class="container">
   <br />
   <h3 align="center">CSV File Editing and Importing in PHP</h3>
   <br />
   <form id="upload_csv" method="post" enctype="multipart/form-data">
    <div class="col-md-3">
     <br />
     <label>Select CSV File</label>
    </div>  
                <div class="col-md-4">  
                    <input type="file" name="csv_file" id="csv_file" accept=".csv" style="margin-top:15px;" />
                </div>  
                <div class="col-md-5">  
                    <input type="submit" name="upload" id="upload" value="Upload" style="margin-top:10px;" class="btn btn-info" />
                </div>  
                <div style="clear:both"></div>
   </form>
   <br />
   <br />
   <div id="csv_file_data"></div>
   
  </div>
 </body>


<script>

$(document).ready(function(){
 $('#upload_csv').on('submit', function(event){
  event.preventDefault();
  $.ajax({
   url:"libs/csvupload/fetch.php",
   method:"POST",
   data:new FormData(this),
   dataType:'json',
   contentType:false,
   cache:false,
   processData:false,
   success:function(data)
   {
    var html = '<table class="table table-striped table-bordered">';
    if(data.column)
    {
     html += '<tr>';
     for(var count = 0; count < data.column.length; count++)
     {
      html += '<th>'+data.column[count]+'</th>';
     }
     html += '</tr>';
    }

    if(data.row_data)
    {
     for(var count = 0; count < data.row_data.length; count++)
     {
      html += '<tr>';
      html += '<td class="student_name" contenteditable>'+data.row_data[count].student_name+'</td>';
      html += '<td class="student_phone" contenteditable>'+data.row_data[count].student_phone+'</td></tr>';
     }
    }
    html += '<table>';
    html += '<div align="center"><button type="button" id="import_data" class="btn btn-success">Import</button></div>';

    $('#csv_file_data').html(html);
    $('#upload_csv')[0].reset();
   }
  })
 });

 $(document).on('click', '#import_data', function(){
  var student_name = [];
  var student_phone = [];
  $('.student_name').each(function(){
   student_name.push($(this).text());
  });
  $('.student_phone').each(function(){
   student_phone.push($(this).text());
  });
  $.ajax({  
   url:"libs/csvupload/import.php",
   method:"post",
   data:{student_name:student_name, student_phone:student_phone},
   success:function(data)
   {
    $('#csv_file_data').html('<div class="alert alert-success">Data Imported Successfully</div>');
   }
  })
 });
});

</script>

6. For this example, create a table tbl_student. Run this SQL in phpMyAdmin:

Code: Select all

--
-- Table structure for table `tbl_student`
--

CREATE TABLE `tbl_student` (
  `student_id` int(11) NOT NULL,
  `student_name` varchar(250) NOT NULL,
  `student_phone` varchar(20) NOT NULL,
  `image` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Indexes for table `tbl_student`
--
ALTER TABLE `tbl_student`
  ADD PRIMARY KEY (`student_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_student`
--
ALTER TABLE `tbl_student`
  MODIFY `student_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

6. Try uploading/importing http://demo.webslesson.info/csv-file-op ... /tesmp.csv

7. Modify the code (change column names etc.) so that your own CSV can be imported.

hiramalik · Unread post by **hiramalik** » Thu Jul 16, 2020 2:38 am

A bundle of thanks for your detailed reply. It helped me a lot. Data is showing on the webpage perfectly well. However, when I click on import data is not imported into the table. Tbl_students remains empty. If you could please look into this issue.

nc07 · Unread post by **nc07** » Thu Jul 16, 2020 3:43 am

hiramalik wrote:A bundle of thanks for your detailed reply. It helped me a lot. Data is showing on the webpage perfectly well. However, when I click on import data is not imported into the table. Tbl_students remains empty. If you could please look into this issue.

You will need to change the variables in the import.php to your server setting
$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");

hiramalik · Unread post by **hiramalik** » Thu Jul 16, 2020 5:44 am

nc07 wrote:
hiramalik wrote:A bundle of thanks for your detailed reply. It helped me a lot. Data is showing on the webpage perfectly well. However, when I click on import data is not imported into the table. Tbl_students remains empty. If you could please look into this issue.
You will need to change the variables in the import.php to your server setting
$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");

Wow.. It worked like a charm

thank you so much for this help. The way you have explained each step in detail helped me learn the process. Once again Thank you so much..

hiramalik · Unread post by **hiramalik** » Thu Jul 16, 2020 11:08 am

Unfortunately, when I tried to change table name and add more column names it is not executing. I made sure to make changes accuratly but failed. Now the file does nothing

I want to import data into the table tbl_Customers and following will be the columns
customerName
customerPhone
customerEmail
customerAddress
appointmentDate
I have updated the code in following method.

1. Fetch.php

Code: Select all

<?php

//fetch.php

if(!empty($_FILES['csv_file']['name']))
{
$file_data = fopen($_FILES['csv_file']['tmp_name'], 'r');
$column = fgetcsv($file_data);
while($row = fgetcsv($file_data))
{
  $row_data[] = array(
   'customerName'  => $row[0],
   'customerPhone'  => $row[1],
   'customerEmail'  => $row[2],
   'customerAddress'  => $row[3],
   'appointmentDate'  => $row[4]
  );
}
$output = array(
  'column'  => $column,
  'row_data'  => $row_data
);

echo json_encode($output);

}

?>

2. import.php

Code: Select all

<?php

//import.php

if(isset($_POST["customerName"]))
{
$connect = new PDO("mysql:host=localhost;dbname=nubuilder4", "root", "");
$customerName = $_POST["customerName"];
$customerPhone = $_POST["customerPhone"];
$customerEmail = $_POST["customerEmail"];
$customerAddress = $_POST["customerAddress"];
$appointmentDate = $_POST["appointmentDate"];


for($count = 0; $count < count($customerName); $count++)
{
  $query .= "
  INSERT INTO tbl_Customers(customerName, customerPhone, customerEmail, customerAddress, appointmentDate)
  VALUES ('".$customerName[$count]."', '".$customerPhone[$count]."' .$ccustomerEmail[$count]."' .$customerAddress[$count]."' .$appointmentDate[$count]."');
 
  ";
}
$statement = $connect->prepare($query);
$statement->execute();
}

?>

3. Table structure

Code: Select all

--

CREATE TABLE `tbl_Customers` (
  `customer_id` int(11) NOT NULL,
  `customerName` varchar(250) NOT NULL,
  `customerPhone` varchar(20) NOT NULL,
  `customerEmail` varchar(200) NOT NULL,
  `customerAddress` varchar(250) NOT NULL,
  `appointmentDate` varchar(50) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Indexes for table `tbl_Customers`
--
ALTER TABLE `tbl_Customers`
  ADD PRIMARY KEY (`customer_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_Customers`
--
ALTER TABLE `tbl_Customers`
  MODIFY `customer_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

4. HTML Object Code

Code: Select all

<head>
 
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
  <style>
  .box
  {
   max-width:600px;
   width:100%;
   margin: 0 auto;;
  }
  </style>
</head>

<body>
  <div class="container">
   <br />
   <h3 align="center">CSV File Editing and Importing in PHP</h3>
   <br />
   <form id="upload_csv" method="post" enctype="multipart/form-data">
    <div class="col-md-3">
     <br />
     <label>Select CSV File</label>
    </div> 
                <div class="col-md-4"> 
                    <input type="file" name="csv_file" id="csv_file" accept=".csv" style="margin-top:15px;" />
                </div> 
                <div class="col-md-5"> 
                    <input type="submit" name="upload" id="upload" value="Upload" style="margin-top:10px;" class="btn btn-info" />
                </div> 
                <div style="clear:both"></div>
   </form>
   <br />
   <br />
   <div id="csv_file_data"></div>
   
  </div>
</body>


<script>

$(document).ready(function(){
$('#upload_csv').on('submit', function(event){
  event.preventDefault();
  $.ajax({
   url:"libs/csvupload/fetch.php",
   method:"POST",
   data:new FormData(this),
   dataType:'json',
   contentType:false,
   cache:false,
   processData:false,
   success:function(data)
   {
    var html = '<table class="table table-striped table-bordered">';
    if(data.column)
    {
     html += '<tr>';
     for(var count = 0; count < data.column.length; count++)
     {
      html += '<th>'+data.column[count]+'</th>';
     }
     html += '</tr>';
    }

    if(data.row_data)
    {
     for(var count = 0; count < data.row_data.length; count++)
     {
      html += '<tr>';
      html += '<td class="customerName" contenteditable>'+data.row_data[count].customerName+'</td>';
      html += '<td class="customerPhone" contenteditable>'+data.row_data[count].customerPhone+'</td>';
      html += '<td class="customerEmail" contenteditable>'+data.row_data[count].customerEmail+'</td>';
      html += '<td class="customerAddress" contenteditable>'+data.row_data[count].customerAddress+'</td>';
      html += '<td class="appointmentDate" contenteditable>'+data.row_data[count].appointmentDate+'</td></tr>';
     }
    }
    html += '<table>';
    html += '<div align="center"><button type="button" id="import_data" class="btn btn-success">Import</button></div>';

    $('#csv_file_data').html(html);
    $('#upload_csv')[0].reset();
   }
  })
});

$(document).on('click', '#import_data', function(){
  var customerName = [];
  var customerPhone = [];
  var customerEmail = [];
  var customerAddress = [];
  var appointmentDate = [];
  $('.customerName').each(function(){
   customerName.push($(this).text());
  });
  $('.customerPhone').each(function(){
   customerPhone.push($(this).text());
  });
  $('.customerEmail').each(function(){
   customerEmail.push($(this).text());
  });
  $('.customerAddress').each(function(){
   customerAddress.push($(this).text());
  });
  $('.appointmentDate').each(function(){
   appointmentDate.push($(this).text());
  });
  $.ajax({ 
   url:"libs/csvupload/import.php",
   method:"post",
   data:{customerName:customerName, customerPhone:customerPhone, customerEmail:customerEmail, customerAddress:customerAddress, appointmentDate:appointmentDate},
   success:function(data)
   {
    $('#csv_file_data').html('<div class="alert alert-success">Data Imported Successfully</div>');
   }
  })
});
});

</script>

I am not sure where am I doing the mistake. Any guidance will be highly appriciated.

nuBuilder Forum

Import csv file

Import csv file

Re: Import csv file

Re: Import csv file

Re: Import csv file

Re: Import csv file

Re: Import csv file

Re: Import csv file

Re: Import csv file

Re: Import csv file

Re: Import csv file