Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Feb 2004
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Alter a csv to sql script - in php

    I found a script on codewalkers, but needed to modify it. Its a csv2 sql converter. Below is my partly modified script.
    PHP Code:
    <H2>CSV to SQL convertor</H2>

    <!-- Input form begin -->

    <FORM NAME="csv2sql" METHOD=POST ACTION="<? echo $PHP_SELF?>">
        <INPUT TYPE="HIDDEN" NAME="ref" VALUE="csv2sql">
        Insertion table:
        <BR>
        <INPUT CLASS="DEFAULT" TYPE="TEXT" NAME="table_name" VALUE="" SIZE=50>
        <BR><BR>
        CSV data: 
        <BR>
        <TEXTAREA CLASS="DEFAULT" NAME="csv_data" ROWS=30 COLS=100></TEXTAREA>
        <BR><BR>
        <INPUT CLASS="DEFAULT" TYPE=SUBMIT VALUE="     Convert to SQL query     ">
    </FORM>

    <!-- Input form end -->

    <?php

    // Parse incoming information if above form was posted
    if($_POST[ref] == "csv2sql") {

        echo 
    "<h2>SQL Query Output:</h2>";

        
    // Get information from form & prepare it for parsing
        
    $table_name $_POST[table_name];
        
    $csv_data   $_POST[csv_data];
        
    $csv_array    explode("\n",$csv_data);
        
    $column_names explode(";",$csv_array[0]);

        
    // Generate base query
        
    $base_query "INSERT INTO $table_name";

        
    // Loop through all CSV data rows and generate separate
        // INSERT queries based on base_query + the row information
        
    $last_data_row count($csv_array) - 1;
        for(
    $counter 1$counter $last_data_row$counter++)
        {
            
    $value_query " VALUES (";
            
    $first true;
            
    $data_row explode(";",$csv_array[$counter]);
            
    $value_counter 0;
            foreach(
    $data_row as $data_value)    
            {
                if(!
    $first)
                    
    $value_query .= ", ";    
                
    $data_value trim($data_value);
                
    $value_query .= "'$data_value'";
                
    $first false;
            }
            
    $value_query .= ")";

            
    // Combine generated queries to generate final query
            
    $query $base_query .$value_query .";";
            echo 
    "$query <BR>";
        }
    }

    ?>
    It worked fine, but not with my implentation.
    I have it "working" at http://www.sqitroma.com/sql/csv2sql.php

    Current output is:INSERT INTO deputy VALUES ('3,\'Hughes W H\',\'Labor\'');
    But I want to strip out the backslashes, and have single inverted commas around each value. The values in my csv were single inverted around the last 2. I also want to strip out any other characters.
    My csv looks like:
    1,'Deakin A','Protectionist'

    I want the sql to look like:
    INSERT INTO deputy VALUES (1,'Deakin A','Protectionist');

    I'm almost there, but need some help to modify the last bits.

    Cheers

  • #2
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    Have you tried to use the stripslashes() to remove the backslashes?
    But aren't the backslashes in your code simply escaping the single quotes?
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •