Announcement

Collapse
No announcement yet.

copy-paste large data set, or import? perhaps

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • copy-paste large data set, or import? perhaps

    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

    Comment


    • #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..

      Code:
      <?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>\n";
              $row++;
              for ($c=0; $c < $num; $c++) {
                  echo $data[$c] . "<br />\n";
              }
          }
          fclose($handle);
      }
      ?>
      Albert Drent
      aducom software netherlands
      scriptcase partner, reseller, support and (turn-key) development
      www.scriptcase.eu / www.scriptcase.nl

      Comment


      • #4
        thanks again Albert, you seem my hero in sc forums

        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

        Mike

        Comment


        • #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.
          Albert Drent
          aducom software netherlands
          scriptcase partner, reseller, support and (turn-key) development
          www.scriptcase.eu / www.scriptcase.nl

          Comment

          Working...
          X