I’m in need of the ability to allow users of our applications to select a local csv file and upload it to an existing table in our MYSQL database. After looking, it doesn’t seem that Scriptcase has an application type that incorporates that capability - unless I’ve missed it? Is anyone doing this or have done this and can offer some guidance? The table is already in the database and we’ve been uploading manually. I want to create an application/interface using Scriptcase to allow for this on an ongoing basis via a web page. Any help would be appreciated.
This has been asked a few times and I haven’t seen someone offer a good solution.
This might be useful:
https://github.com/PHPOffice/PhpSpreadsheet
(I haven’t tried it).
It’s a very simple approach. PHP has csv functions on board. You can read a full csv into an array with one statement and reading record for record is also easy. So create a form with an uploader and a process button. then upload the file, read it and depending on the size process is as part of a php array (foreach) or record by record (while not eof). With the sql designer you can easily generate a sql template, so depending on the amount of fields you’re done in a handful of code.
[pseudocode]
$sql='replace into mytable (...)
$csva= fgetcsv(..);
foreach($csva as $rec) {
process
}
example from php.net, reading record for record
<?php
$row = 1;
if (($handle = fopen("test.csv", "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$num = count($data);
echo "<p> $num fields in line $row: <br /></p>
";
$row++;
for ($c=0; $c < $num; $c++) {
echo $data[$c] . "<br />
";
}
}
fclose($handle);
}
Here is sample I use…Hope this helps you. This a sample database in it show how it works.
Here is a sample of what I am using, Hope this helps
Code: [SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]1[/TD]
[TD=“class: content”]<?php[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]2[/TD]
[TD=“class: content”] [/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]3[/TD]
[TD=“class: content”]$connect = mysql_connect(‘localhost’,‘root’,‘12345’);[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]4[/TD]
[TD=“class: content”]if (!$connect) {[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]5[/TD]
[TD=“class: content”]die(‘Could not connect to MySQL: ’ . mysql_error());[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]6[/TD]
[TD=“class: content”]}[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]7[/TD]
[TD=“class: content”] [/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]8[/TD]
[TD=“class: content”]$cid =mysql_select_db(‘test’,$connect);[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]9[/TD]
[TD=“class: content”]// supply your database name[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]10[/TD]
[TD=“class: content”] [/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]11[/TD]
[TD=“class: content”]define(‘CSV_PATH’,‘C:/wamp/www/csvfile/’);[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]12[/TD]
[TD=“class: content”]// path where your CSV file is located[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]13[/TD]
[TD=“class: content”] [/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]14[/TD]
[TD=“class: content”]$csv_file = CSV_PATH . “infotuts.csv”; // Name of your CSV file[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]15[/TD]
[TD=“class: content”]$csvfile = fopen($csv_file, ‘r’);[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]16[/TD]
[TD=“class: content”]$theData = fgets($csvfile);[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]17[/TD]
[TD=“class: content”]$i = 0;[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]18[/TD]
[TD=“class: content”]while (!feof($csvfile)) {[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]19[/TD]
[TD=“class: content”]$csv_data[] = fgets($csvfile, 1024);[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]20[/TD]
[TD=“class: content”]$csv_array = explode(",", $csv_data[$i]);[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]21[/TD]
[TD=“class: content”]$insert_csv = array();[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]22[/TD]
[TD=“class: content”]$insert_csv[‘ID’] = $csv_array[0];[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]23[/TD]
[TD=“class: content”]$insert_csv[‘name’] = $csv_array[1];[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]24[/TD]
[TD=“class: content”]$insert_csv[‘email’] = $csv_array[2];[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]25[/TD]
[TD=“class: content”]$query = "INSERT INTO csvdata(ID,name,email)[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]26[/TD]
[TD=“class: content”]VALUES(’’,’".$insert_csv[‘name’]."’,’".$insert_csv[‘email’]."’)";[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]27[/TD]
[TD=“class: content”]$n=mysql_query($query, $connect );[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]28[/TD]
[TD=“class: content”]$i++;[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]29[/TD]
[TD=“class: content”]}[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]30[/TD]
[TD=“class: content”]fclose($csvfile);[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]31[/TD]
[TD=“class: content”] [/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]32[/TD]
[TD=“class: content”]echo “File data successfully imported to database!!”;[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]33[/TD]
[TD=“class: content”]mysql_close($connect);[/TD]
[/TR]
[/TABLE]
[/SIZE]
[SIZE=14px] [TABLE]
[TR]
[TD=“class: number”]34[/TD]
[TD=“class: content”]?>[/TD]
[/TR]
[/TABLE]
[/SIZE]
This is another example that I have used in the past, just substitute your info.
Code:
<?php //set the connection variables $hostname = “localhost”; $username = “username”; $password = “password”; $database = “library”; $filename = “books.csv”; //connect to mysql database $connection = mysqli_connect($hostname, $username, $password, $database) or die(“Error " . mysqli_error($connection)); // open the csv file $fp = fopen($filename,“r”); //parse the csv file row by row while(($row = fgetcsv($fp,“500”,”,")) != FALSE) { //insert csv data into mysql table $sql = “INSERT INTO tbl_books (name, author, isbn) VALUES(’” . implode("’,’",$row) . “’)”; if(!mysqli_query($connection, $sql)) { die('Error : ’ . mysqli_error()); } } fclose($fp); //close the db connection mysqli_close($connection); ?>