copy-paste large data set, or import? perhaps

Applications Grid / Reports
#1

hi guys/Albert

I’ve a need to repeatedly add data to one application to update a database

planning to make it a form or an editable grid

now this data is coming to me on excel sheet, about 15 columns and around 25 rows each time…

in vb.net, c sharp, we can use the data sheet to copy-paste the data directly by selecting the columns and the rows accordingly

in sc, php, anyway we can do this? if not copy-paste the data, an import from the excel sheet may work as well

my target is to add batch data files in columns/rows to the database, in any application type of SC that doable

any advice or idea is highly appreciated
thanks in advanced

#2

Hi,
assuming the the data are added to the db (MySQL) as is there is no need to copy/paste the data into an application.
The easiest way to go would be to save the Excel sheet as csv and load it into the db.

Have a look at: http://dev.mysql.com/doc/refman/5.0/en/load-data.html

In case you need to filter out or change some data based on some criteria you can load the csv file into an array using the fgetcsv() function from php.
Manipulate the data in the array and update your db from there. No need of copy and paste which, but that’s just my opinion, is the wrong way anyway.

Hope this helps.

jsb

#3

Create a control application and put a file upload field on it. Then you are able to upload a csv file. Add a button ‘import’ to it. In the button event you process the uploaded file. I.e…


<?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);
}
?>
#4

thanks again Albert, you seem my hero in sc forums :slight_smile:

thanks also jsbinca, your way is correct, but not practice for end-user daily use (ursing the db directly, or through phpmyadmin) the one Albert stated using sc control app looks more reasonable, it needs a try though

yet wondering if i can modify the columns and import from that csv only required column, e.g. i have a large sheet with 20 olumns and 25-50 rows, i want to import function to get only data from column 1,3, 5 and 10, 11… ok will try this ASAP and keep you posted guys

i really thought there a copy-paste way such as sub-form or data-sheet of Microsoft access db or asp.net dotnet csharp stuff, but doesn’t seem apply to be valid for an http web page though

thanks again dudes, really appreciated :slight_smile:

Mike

#5

You will have all fields in an array $data. $data[0] contains your first column, $data[1] your second etc. So you can extract the fields you need and react accordingly. This function will process record by record until end of file.