Announcement

Collapse
No announcement yet.

upload csv and import to database

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

  • upload csv and import to database

    Hello,

    I am currently facing some trouble with .csv import using a form. I am trying to use a code after uploading .csv, but it looks like OPTIONALLY ENCLOSED BY '\"' does not work proper since I am missing 1st value of each line...import just skips the first value of each line. Since I am only allowed to use OPTIONALLY ENCLOSED BY '"' with SC, i will need a solution that will work with any .csv that has the following format:

    CSV:

    "line1-value1","line1-value2"
    "line2-value1","line2-value2"

    Code:

    Code:
    $sql = "LOAD DATA LOCAL INFILE '$my_csv'
    	INTO TABLE `data_csv`
            FIELDS TERMINATED BY ','
            OPTIONALLY ENCLOSED BY '\"'
            LINES TERMINATED BY ''
    
    (`value1`,
    `value2`)";
    
    sc_exec_sql($sql);
    Another question: Is it possible to check if there are any duplicates being imported during import (in case one dataset has already been imported) and skip those lines. I would like to count those duplicates during import as well and fetch the number of duplicates into a variable.

  • #2
    Ok...import works when i add an extra sign at the end of each line:

    CSV:

    "line1-value1","line1-value2"|
    "line2-value1","line2-value2"

    Code:
    Code:
    $sql = "LOAD DATA LOCAL INFILE '$my_csv'
    	INTO TABLE `data_csv`
            FIELDS TERMINATED BY ','
            OPTIONALLY ENCLOSED BY '\"'
            LINES TERMINATED BY '|'
    
    (`value1`,
    `value2`)";
    
    sc_exec_sql($sql);
    I would still like to solve this without 1 extra step...

    Comment


    • #3
      Hey you know I need your help.

      I need to load an Excel or CSV file and then add the information to the database and do not know how to do.

      What happens is that users have to load that file have it in excel and csv save saves them as such:

      "value1", "value2", "value3"
      "value1", "value2", "value3"
      "value1", "value2", "value3"

      and i was looking and found this query

      LOAD DATA INFILE "/ home / jaime / Desktop / ensayo.csv" INTO TABLE test FIELDS TERMINATED BY ",";
      * and I have two problems
      one load some values ​​and the other is that nose as I can do when I publish to the server address put

      please help

      if you could send me the form where you do it please
      I'll be waiting for your answer
      thanks

      Comment


      • #4
        If you need to check for duplicates then you have to work record-by-record. Importing the record will generate a database error. You can trap this error and adjust count. This is an example (without duplicates check) of a csvimport I used in an application


        $cnt=0;
        $handle = fopen('D:/Program Files (x86)/NetMake/v6/wwwroot/scriptcase/file/doc/csvupload/'.{csv}, 'r');
        if ($handle)
        {
        set_time_limit(0);

        //the top line is the field names
        $fields = fgetcsv($handle, 4096, ';');

        $empty='';
        //loop through one row at a time
        while (($data = fgetcsv($handle, 4096, ';')) !== FALSE)
        {
        $data = array_combine($fields, $data);

        if($data['Geboortedatum']=='') {$data['Geboortedatum']='01-01-1900';}
        $stm ='replace into lid (LidId,Voornaam,Voorletters,Voorvoegsels,Achternaa m,'
        . 'Straat,Huisnummer,Postcode,Woonplaats,Geboortedat um,Geslacht,'
        . 'EMail,TelefoonVast,TelefoonMobiel,LidSinds) values ('
        . '"'.$data['Lid-nr'].'",'
        . '"'.$data['Voornaam'].'",'
        . '"'.$empty.'",'
        . '"'.$empty.'",'
        . '"'.$data['Naam'].'",'

        . '"'.$data['Straat'].'",'
        . '"'.$empty.'",'
        . '"'.$data['Postcode'].'",'
        . '"'.$data['Woonplaats'].'",'
        . 'STR_TO_DATE("'.$data['Geboortedatum'].'","%e-%m-%Y"),'
        . '"'.$data['Geslacht'].'",'

        . '"'.$data['E-mail'].'",'
        . '"'.$empty.'",'
        . '"'.$data['GSM'].'",'
        . 'STR_TO_DATE("'.$data['Lid vanaf'].'","%e-%m-%Y"))';

        sc_exec_sql($stm);

        $cnt++;
        }

        fclose($handle);
        }
        {reccnt}=$cnt;
        Albert Drent
        aducom software netherlands
        scriptcase partner, reseller, support and (turn-key) development
        www.scriptcase.eu / www.scriptcase.nl

        Comment


        • #5
          I would LOAD DATA in a tmp table and then INSERT IGNORE to final table, drop the tmp table...much faster

          Comment


          • #6
            Yes, but than you cannot count the records, which was a demand?
            Albert Drent
            aducom software netherlands
            scriptcase partner, reseller, support and (turn-key) development
            www.scriptcase.eu / www.scriptcase.nl

            Comment


            • #7
              @ jaimeflores: could you please write in a way so we can understand?

              Count Record before importing...

              $server_path = 'path_to_csv_file';
              $csv_filename = 'xyz.csv';
              $csv_path = $server_path . '/' . $csv_filename;
              $fields_terminated = ','; // MYSQL STATEMENT
              $fields_enclosed = '"'; // MYSQL STATEMENT
              $lines_terminated = '\n'; // MYSQL STATEMENT

              if(file_exists($csv_path)){
              $CSVLineCount = count(file($csv_path));
              echo 'Number of records in CSV: ' . $CSVLineCount;
              }

              $sqlLoadData_CSV2TMP = "LOAD DATA LOCAL INFILE '" . $csv_path . "'
              INTO TABLE `tmp`
              FIELDS TERMINATED BY '" . $fields_terminated . "'
              ENCLOSED BY '" . $fields_enclosed . "'
              LINES TERMINATED BY '" . $lines_terminated . "'
              IGNORE 1 LINES
              (`a`,
              `b`,
              `c`)
              SET
              b = 'Hello World';
              sc_exec_sql($sqlLoadData_CSV2TMP);
              Last edited by poltergeist; 12-13-2012, 02:10 AM.

              Comment


              • #8
                The questions was to count duplicates:
                I would like to count those duplicates during import as well and fetch the number of duplicates into a variable.
                Albert Drent
                aducom software netherlands
                scriptcase partner, reseller, support and (turn-key) development
                www.scriptcase.eu / www.scriptcase.nl

                Comment


                • #9
                  well,
                  - import to tmp
                  - count records
                  - INSERT IGNORE to final table (one col needs to be Unique in final table)
                  - count again
                  Last edited by poltergeist; 12-13-2012, 01:21 PM.

                  Comment


                  • #10
                    you win ;-)
                    Albert Drent
                    aducom software netherlands
                    scriptcase partner, reseller, support and (turn-key) development
                    www.scriptcase.eu / www.scriptcase.nl

                    Comment


                    • #11
                      actually it took me 2 days to figure out the best way to delete and count duplicates, since a JOIN, with my amount of data, on the same table would take long even indexed Thx...next time it's your turn
                      Last edited by poltergeist; 12-13-2012, 01:19 PM.

                      Comment


                      • #12
                        Originally posted by aducom View Post
                        If you need to check for duplicates then you have to work record-by-record. Importing the record will generate a database error. You can trap this error and adjust count. This is an example (without duplicates check) of a csvimport I used in an application


                        $cnt=0;
                        $handle = fopen('D:/Program Files (x86)/NetMake/v6/wwwroot/scriptcase/file/doc/csvupload/'.{csv}, 'r');
                        if ($handle)
                        {
                        set_time_limit(0);

                        //the top line is the field names
                        $fields = fgetcsv($handle, 4096, ';');

                        $empty='';
                        //loop through one row at a time
                        while (($data = fgetcsv($handle, 4096, ';')) !== FALSE)
                        {
                        $data = array_combine($fields, $data);

                        if($data['Geboortedatum']=='') {$data['Geboortedatum']='01-01-1900';}
                        $stm ='replace into lid (LidId,Voornaam,Voorletters,Voorvoegsels,Achternaa m,'
                        . 'Straat,Huisnummer,Postcode,Woonplaats,Geboortedat um,Geslacht,'
                        . 'EMail,TelefoonVast,TelefoonMobiel,LidSinds) values ('
                        . '"'.$data['Lid-nr'].'",'
                        . '"'.$data['Voornaam'].'",'
                        . '"'.$empty.'",'
                        . '"'.$empty.'",'
                        . '"'.$data['Naam'].'",'

                        . '"'.$data['Straat'].'",'
                        . '"'.$empty.'",'
                        . '"'.$data['Postcode'].'",'
                        . '"'.$data['Woonplaats'].'",'
                        . 'STR_TO_DATE("'.$data['Geboortedatum'].'","%e-%m-%Y"),'
                        . '"'.$data['Geslacht'].'",'

                        . '"'.$data['E-mail'].'",'
                        . '"'.$empty.'",'
                        . '"'.$data['GSM'].'",'
                        . 'STR_TO_DATE("'.$data['Lid vanaf'].'","%e-%m-%Y"))';

                        sc_exec_sql($stm);

                        $cnt++;
                        }

                        fclose($handle);
                        }
                        {reccnt}=$cnt;
                        Albert can I ask please if you used a control or blank app for this? reason being is I am wanting to add csv import to my app and this is only example I can find.
                        Also what would the file path be if not local? as you do have a local program files path stated...?

                        Thanks in advance for your help! You been a real star so far!

                        Comment


                        • #13
                          You need a database connection and thus a control application. This file is local on the server and you can upload it to a certain directory. Other solutions are to make your own upload routine. You can find some instruction on the next link:

                          http://www.w3schools.com/php/php_file_upload.asp
                          Albert Drent
                          aducom software netherlands
                          scriptcase partner, reseller, support and (turn-key) development
                          www.scriptcase.eu / www.scriptcase.nl

                          Comment


                          • #14
                            Originally posted by aducom View Post
                            You need a database connection and thus a control application. This file is local on the server and you can upload it to a certain directory. Other solutions are to make your own upload routine. You can find some instruction on the next link:

                            http://www.w3schools.com/php/php_file_upload.asp
                            yup upload is not a problem thats the easy part so control app thx!

                            Comment


                            • #15
                              Look at php function fgetcsv. Examples: http://www.w3schools.com/php/func_fi...em_fgetcsv.asp and http://www.bin-co.com/php/scripts/csv_import_export.
                              Last edited by RHS; 06-17-2013, 10:15 AM.
                              Best regards: - Reinhard -

                              I use ScriptCase 8 Enterprise Edition, Version 8.(latest)

                              Comment

                              Working...
                              X