SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Mar 2005
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Excel or csv to mysql via PhpMyAdmin

    Hi I am new to Site point, certainly looks a busy site

    Anyway I have a mysql database that I want to dump data into.

    When using MS Access I,m fine but I cant get the data to transfer using phpmyadmin.

    The tables are all set up in mysql,

    1. I have my excel doc and I save as csv file type.

    2. Go to phpmyadmin, select the table I want to add data to and click sql

    3.then click "Insert data from a textfile into table"

    I leave all the defaults as they are

    Replace table data with file: yes
    Fields terminated by ; The terminator of the fields.
    Fields enclosed by "
    Fields escaped by \
    Lines terminated by \r\n
    Linefeed: \n

    Upload it, and get an error each time

    PHP Code:
    SQL-query :  

    LOAD DATA LOCAL INFILE '/home/vdeck/tmp/phpvY4zfL' INTO TABLE `default_en_listingsdbFIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' 

    MySQL said


    The used command is not allowed with this MySQL version 
    I have tried saving the excel file as formatted text (space delimited) and get similar errors.

    I am on shared hosting but have privelages to create update db's etc

    What am I doing wrong

  2. #2
    SitePoint Zealot newspire's Avatar
    Join Date
    Mar 2005
    Location
    Houston, TX
    Posts
    118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    http://dev.mysql.com/doc/mysql/en/load-data-local.html

    You might check your version of MySQL. They have made some changes to this command. Also, according to the link below you get this error if the feature has been disabled.

    http://www.ispirer.com/doc/sqlways38...ays-1-329.html

  3. #3
    SitePoint Member
    Join Date
    Mar 2005
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Looks like that's the problem, any workarounds or ideas. I can't change the php set up as I am on a hosted server

  4. #4
    SitePoint Zealot newspire's Avatar
    Join Date
    Mar 2005
    Location
    Houston, TX
    Posts
    118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't think it is a PHP issue. It is that your host has disabled this feature (the ability to load a csv file from a client) on MySQL. If they will not enable this feature then I guess you could have something convert your csv file to sql queries and run them as a batch.


Bookmarks

Posting Permissions

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