Results 1 to 15 of 15
  1. #1
    Full Member
    Join Date
    January 18th, 2005
    Posts
    270
    I've wanted to use datafeeds for nearly a year now (with PHP/MySQL) but haven't been able to because of getting stuck on the first step - getting the data into a database! My host has'LOAD DATA INFILE' disabled, which is unfortunate, because that's an easy way to get the data in.

    I have webmerge and like it, but I think I still need something more automated for bigger feeds.

    I found this script: http://www.scriptsearch.com/details/6497.html , but I'm getting some errors with it, otherwise it would be great. It gets the data in, even with the errors, but because of the nature of the errors I'm afraid I could run into some big problems with bigger feeds. The script doesn't come with any support either. It's got some great features too. I'm not certain, but I think you can even set it to just update certain fields, like the price, when the feed updates.

    I've considered paying someone to fix the script, but having someone fix another persons code is asking a lot, I think. I don't know if I can afford to have someone write something for me either.

    Right now I feel like either my head is going to explode or I'm going to completely lose my sanity as I'm struggling to get the above script to work better.

    If anyone here has had this same problem, or has a solution to it, I'd love to discuss it!

  2. #2
    ABW Ambassador
    Join Date
    January 18th, 2005
    Location
    ÄúsTrálíĺ
    Posts
    1,372
    LOAD DATA INFILE etc was disabled because in later versions of mysql due to some security hazard. (that's hardly the way to fix a problem!)

    Look up mysqlimport.

    You can run it form the command line, or within php/perl scripts.

    eg php

    system ("mysqlimport etc etc etc");

    You'll probably need something like this
    system("mysqlimport -u mysqluser -pmysqlpassword -d --local --fields-terminated-by='|' mysqldatabase $filetoimport");

    Things to note (that I've come across):
    - $filetoimport must match your table name. eg. a file named datafeed.txt must have a table named "datafeed"
    - the lack of space between -p and your password

    [edit] Here's the link to the docs at mysql.com [/edit]
    It's how I use it, anyway...

  3. #3
    Full Member
    Join Date
    January 18th, 2005
    Location
    Tamborine Mountain Australia
    Posts
    248
    Hi bubbles,

    I sympathise with you, I now how frustrating it can be just to get data into a database.

    I must admit I'm not sure what 'LOAD DATA INFILE' disabled means and how it would effect this, but does your host have phpmyadmin? - That's what I use and it's a very simple way of getting the data in to a mysql database.

    If your host does have phpmyadmin, and if you are still unable to upload using that and your host is making it hard for you to upload your data files, I would consider changing to another host.

  4. #4
    Newbie
    Join Date
    January 18th, 2005
    Posts
    4
    If all else fails short of changing hosts check this tool
    http://www.convert-in.com/xls2sql.htm

  5. #5
    Newbie
    Join Date
    January 18th, 2005
    Posts
    36
    mysqlimport with -L option shoud work

    my server also blocks load data infile but from ssh i can use mysqlimport.

    it should also work with system(mysqlimport ...) inside a script.

    Harry Hovak
    http://www.affiliatesignup.com

  6. #6
    Full Member
    Join Date
    January 18th, 2005
    Posts
    270
    tamborinegal - I've got phpmyadmin, but at least the version I have, uses 'LOAD DATA INFILE' to import the file, so I can't use that either. I found out after repeatidly(sp?) trying to do it through phpmyadmin and it not working.

    I really don't want to change hosts at this point, I have all my sites there and it would be such a pain to move everything. Any new sites I make will be hosted elsewhere though.

    ebooksafari - I'll check the link you provided.

    Pete - I remember coming across information about mysqlimport before. I don't really have command line access anymore either, at least not through telnet. I really need to do it from a script. I really don't understand anything I've ever read at mysql.com, although I've tried numerous times. I think it would be best if I check with my host to make sure that mysqlimport is enabled before I try it too!

  7. #7
    Full Member
    Join Date
    January 18th, 2005
    Posts
    270
    ebooksafari - the site at the link you gave looks pretty good! I sent a message to them asking if it uses 'load data infile' or not. Meanwhile I think I'll go ahead a try the trial version anyway.

    internetturnkeysites/Harry - I'm not sure if my host even allows ssh anymore, I know I don't have telnet access now though. If the script above doesn't work, trying mysqlimport within a script will be my next step.

    Thanks for all the ideas so far!

  8. #8
    Newbie
    Join Date
    January 18th, 2005
    Posts
    36
    Bubbles

    the ebooksafari recomendation is a windows application that will take csv text file and convert it in to mysql script then you can use myphpadmin execute the script and import the data. for small files its not a problem for largers it may get little complicated.

    as for ssh access if you use system command in your php script you sould be able to execte mysqlimport without ssh access.

    Let me know if you want to look at your hosting account and see what is the bet way to get the data in.

    I'm thinking of offering mysql hosting service where you can connect to my server and user the data there, but i'm not sure what the interest would be on that.

    Harry Hovak
    http://www.affiliatesignup.com

  9. #9
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    4,423
    If you have mysql accessible from the command line, you can also most likely do this:

    from the command prompt type:
    > mysql --local-infile=1 -u myuser -p

    This will allow to once inside to use the load data infile command.
    > use dbname;
    then issue your load command.
    > load data local infile '\dataloads\myfeed.txt' into table myrawload

    you can create a FILENAME.sql file, with the contents being the part you type once in mysql,
    The create a script file with this in it -
    mysql --local-infile=1 -u YOURMYSQLUSERNAME -pMYSQLPASSWORD < FILENAME.sql

    then all you need to do is run that script to load the file. Note no space after the -p

    Chet

  10. #10
    Full Member
    Join Date
    January 18th, 2005
    Posts
    270
    The Excel to MySQL converter program that ebooksafari mentioned seems to be working pretty well, I went ahead and bought the full version too.

    It converts and then uploads the data into your database for you as long as you give it your database, password, that kind of stuff. So far I've only used it with a feed that had 200 something rows in it, it didn't take too long, but I can see how it could with bigger feeds.

    Other than the time involved for larger files, the only other complaint I have about it is that it doesn't let you assign a data type for each of your fields, it automatically assigns then itself. (The other script I mentioned in my first post does though.) With this I will have to go open up PhpMyAdmin and set all the data types back again each time. Maybe not, now that I think about it. You may only need to do that the first time. I'll have to see.

    Here's a new question, I just thought of while writing this:

    Can I use mysqlimport within PhpMyAdmin - in the section labeled:

    "Run SQL query/queries on database X:"

    If so, what syntax do I use? Do I write it the same way as from a command line? It seems like I tried something like this a long time ago too, and couldn't get it to work. Maybe I didn't have the syntax right, that's where I tend to get confused.

    Chez Noir - I believe I tried your suggestion once a long time ago too,but it involves using load data infile, which I can't do.

  11. #11
    Newbie
    Join Date
    January 18th, 2005
    Posts
    36
    Bubbles

    There is a solution to the issue of the data types
    instead of uploading directly just create a sql dump edit it with text editor and change the data types the way you want then you can use phpmyadmin to import the data.

    all you have to do is go to the phpmyadmin to click on SQL and use the Browse button to select and upload the file

    and still this is only for not too big files up to 2-3 mb should work fine.

    Or you can do it the other way around.

    upload the data then use phpmyadmin to change the data type.

    Harry Hovak
    http://www.affiliatesignup.com

  12. #12
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    4,423
    Bubbles, which is why when you enter mysql, you have to enter the --local-infile=1

    If you don't do that, the infile will not be available, if you do, it should be avaialable. The stuff is still in the mysql code, just turned off by default, that turns it back on for that session only.

    Chet

  13. #13
    Full Member
    Join Date
    January 18th, 2005
    Location
    Tamborine Mountain Australia
    Posts
    248
    Hi Bubbles,

    if you haven't solved your problem yet, you might want to check this out. I stumbled across it today while looking for something else.

    http://px.sklar.com/code.html?id=532

  14. #14
    Full Member
    Join Date
    January 18th, 2005
    Posts
    270
    Chez Noir - are you saying it is possible to over ride something that disabled and use it anyway for one session?

    tamborinegal - thanks for the link!

    Harry - I'll have to play around with different ways of setting the data types and see what works best, is fastest, etc.

  15. #15
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    4,423
    Bubbles, that is exactly what i am saying. It is disabled in my version of mysql, but logging in with that switch enables it. Go figure.

    Chet

  16. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Data Feeds - Load and Comparision
    By Black Magic in forum Programming / Datafeeds / Tools
    Replies: 3
    Last Post: January 4th, 2007, 09:11 AM
  2. Data Feeds - Load and Comparision
    By Black Magic in forum Midnight Cafe'
    Replies: 1
    Last Post: December 29th, 2006, 02:43 PM
  3. MySQL LOAD DATA INFILE question
    By PatrickAllmond in forum Programming / Datafeeds / Tools
    Replies: 12
    Last Post: March 4th, 2006, 01:47 PM

Posting Permissions

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