Import csv file

Questions related to customising nuBuilder Forte with Javascript or PHP.

Import csv file

Postby hiramalik » Wed Jul 15, 2020 8:07 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
hiramalik
 
Posts: 15
Joined: Wed Jul 15, 2020 8:04 pm

Re: Import csv file

Postby kev1n » Wed Jul 15, 2020 8:58 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.
kev1n
 
Posts: 1038
Joined: Mon Oct 15, 2018 2:13 am

Re: Import csv file

Postby hiramalik » Wed Jul 15, 2020 10:03 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
 
Posts: 15
Joined: Wed Jul 15, 2020 8:04 pm

Re: Import csv file

Postby hiramalik » Wed Jul 15, 2020 10:22 pm

I don't need to fetch the data, I just need to import it.
hiramalik
 
Posts: 15
Joined: Wed Jul 15, 2020 8:04 pm

Re: Import csv file

Postby kev1n » Wed Jul 15, 2020 11:02 pm

I can give you detailed instructions tonight.
kev1n
 
Posts: 1038
Joined: Mon Oct 15, 2018 2:13 am

Re: Import csv file

Postby kev1n » Thu Jul 16, 2020 3:13 am

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.
kev1n
 
Posts: 1038
Joined: Mon Oct 15, 2018 2:13 am

Re: Import csv file

Postby hiramalik » Thu Jul 16, 2020 10:08 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.
hiramalik
 
Posts: 15
Joined: Wed Jul 15, 2020 8:04 pm

Re: Import csv file

Postby nc07 » Thu Jul 16, 2020 11:13 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", "");
nc07
 
Posts: 24
Joined: Tue Jun 04, 2019 11:35 am

Re: Import csv file

Postby hiramalik » Thu Jul 16, 2020 1:14 pm

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 :D 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
 
Posts: 15
Joined: Wed Jul 15, 2020 8:04 pm

Re: Import csv file

Postby hiramalik » Thu Jul 16, 2020 6:38 pm

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.
hiramalik
 
Posts: 15
Joined: Wed Jul 15, 2020 8:04 pm

Next

Return to Custom Code