Page 1 of 2 12 LastLast
Results 1 to 25 of 27
  1. #1
    "An Englishman In New York" TJ's Avatar
    Join Date
    January 18th, 2005
    Posts
    3,282
    Hi,

    I'm trying to load a data file into MySQL that has fields enclosed by "

    When a field has an item with " in the data (e.g. Super 10" Widget) it makes the database think the field ended and it gives me all kinds of agrovation.

    Any thought on how I can get over this?
    Thanks

  2. #2
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    594
    Try replacing the single quote (") with a double quote ("").

    IamJaloppy

  3. #3
    ABW Ambassador sjangro's Avatar
    Join Date
    January 18th, 2005
    Location
    Boston
    Posts
    1,529
    Akoss,

    Are the quotes that appear in the data escaped, like this?

    Super 10\" Widget

    If not, then the best solution is to change the data feed format, not code around it.

    I assume you're working with the TD feed. I don't have access to a feed that's comma-quote delimited to check and answer my own question above.

    If the data has quotes in it and they're not escaped, they should be. I'm looking into that. However, the quickest solution may be to get the feed with different delmiters, like a tab (\t) and no quotes.

    Tab delimited text is easily imported into MySql, like this:

    <pre class="ip-ubbcode-code-pre">
    load data infile 'filename.txt'
    into table your_table
    fields terminated by '\t'
    lines terminated by '\n'
    ignore 1 lines;
    </pre>

    --scott

    --
    Scott Jangro
    sjangro@befree.com

  4. #4
    "An Englishman In New York" TJ's Avatar
    Join Date
    January 18th, 2005
    Posts
    3,282
    You mean in the datafile? I will be getting fresh data every night and was hoping not to have to manipulate the data before loading it...

    Perhaps I'm gonna need a script to load the data? I've heard others do this, but I have no clue where to start this [img]/infopop/emoticons/icon_frown.gif[/img]

  5. #5
    "An Englishman In New York" TJ's Avatar
    Join Date
    January 18th, 2005
    Posts
    3,282
    Hey Scott, we posted at the same time...

    I just sent you an email about this...

    [Message edited because I don't expect people to broadcast their email address here [img]/infopop/emoticons/icon_smile.gif[/img]]

    [This message was edited by ^akoss^ on October 23, 2002 at 10:00 AM.]

  6. #6
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    594
    <BLOCKQUOTE class="ip-ubbcode-quote"><font size="-1">quote:</font><HR>Originally posted by ^akoss^:
    You mean in the datafile? I will be getting fresh data every night and was hoping not to have to manipulate the data before loading it...

    Perhaps I'm gonna need a script to load the data? I've heard others do this, but I have no clue where to start this [img]/infopop/emoticons/icon_frown.gif[/img]<HR></BLOCKQUOTE>

    Sorry, I was assuming you were using a script. Without it, it would be cumbersum.

    IamJaloppy

  7. #7
    ABW Ambassador sjangro's Avatar
    Join Date
    January 18th, 2005
    Location
    Boston
    Posts
    1,529
    Akoss,

    I just looked at a comma-quote format TD data feed and the quotes in the data are doubled, like this:

    ..."abcabc","10"" Widget","12123123",...

    The following MySql load syntax should handle this, as Mysql detects whether an enclosed-by parameter is doubled and treats it as data.

    <pre class="ip-ubbcode-code-pre">
    LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
    FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    LINES TERMINATED BY '\n';
    </pre>

    If you want to continue this by email, that's fine, I just thought I'd follow up on the subject here for academic purposes...

    --scott

    --
    Scott Jangro
    sjangro@befree.com

  8. #8
    "An Englishman In New York" TJ's Avatar
    Join Date
    January 18th, 2005
    Posts
    3,282
    ok, my bad.... I'm still having an issue though... I'll have to delve deeper and get back with you [img]/infopop/emoticons/icon_smile.gif[/img]

    Thanks for your help scott [img]/infopop/emoticons/icon_biggrin.gif[/img]

  9. #9
    "An Englishman In New York" TJ's Avatar
    Join Date
    January 18th, 2005
    Posts
    3,282
    I have to take a break right now, but it seems to have a problem with the \n ....I have all 50 fields created in my db, and it wraps the last line:

    ttp://www.tigerdirect.com/applications/category_slc.asp?id=040203"","Zerus Hardware

    this is the result of the query, the three quotes ARE in the datafile

    hmmmmm.....

  10. #10
    ABW Ambassador sjangro's Avatar
    Join Date
    January 18th, 2005
    Location
    Boston
    Posts
    1,529
    So I've been helping you with a KB datafeed?

    Hmmph.

    --sucker

    --
    Sucker Jangro
    sjangro@befree.com

  11. #11
    "An Englishman In New York" TJ's Avatar
    Join Date
    January 18th, 2005
    Posts
    3,282
    <BLOCKQUOTE class="ip-ubbcode-quote"><font size="-1">quote:</font><HR>So I've been helping you with a KB datafeed?<HR></BLOCKQUOTE>

    NOPE! this is yours.... the last field in the database is item_category_url which is in the format of my post :-)

  12. #12
    ABW Ambassador sjangro's Avatar
    Join Date
    January 18th, 2005
    Location
    Boston
    Posts
    1,529
    Good! (I mistook that for a KB link, but anyway I was kidding. [img]/infopop/emoticons/icon_smile.gif[/img] )

    That's strange about the '\n' not working.

    What OS are you using? Try '\r\n'.

    --scott

    --
    Scott Jangro
    sjangro@befree.com

  13. #13
    "An Englishman In New York" TJ's Avatar
    Join Date
    January 18th, 2005
    Posts
    3,282
    <BLOCKQUOTE class="ip-ubbcode-quote"><font size="-1">quote:</font><HR>Try '\r\n'<HR></BLOCKQUOTE>

    Perfecto thanks.... now dyou wanna stop over and look at the warnings hehehehe

    Have a great night Scott... try to not be too paranoid about KB ;D

  14. #14
    ABW Ambassador sjangro's Avatar
    Join Date
    January 18th, 2005
    Location
    Boston
    Posts
    1,529
    Just warnings? You're home free.

    Probably field length and data-type issues. As long as you have the right number of fields and the URLs are ok, you can probably ignore them as you won't use most of the fields.

    Haven't been to Rochester in...well...ever but I'll have to pass.
    [img]/infopop/emoticons/icon_wink.gif[/img]

    --scott

    --
    Scott Jangro
    sjangro@befree.com

  15. #15
    "An Englishman In New York" TJ's Avatar
    Join Date
    January 18th, 2005
    Posts
    3,282
    ok, I'm down to 2 warnings now [img]/infopop/emoticons/icon_smile.gif[/img]

    I noticed the ID encoded url has no %20 per our earlier discussions. Also I was hoping to see the small version of the images.... is this your area, or should I bounce it over to Andy?

  16. #16
    ABW Ambassador sjangro's Avatar
    Join Date
    January 18th, 2005
    Location
    Boston
    Posts
    1,529
    Akoss,

    Sorry for the delay, I've been out of town for three days.

    We're working on the %20 thing in our tag generator. However, if you're using a datafeed are you also using a scripting language to develop the web pages? If so, it would be easy to fix the %20 thing youself as you display the URL in the HTML.

    You could even do it in your MySQL select statement:

    <pre class="ip-ubbcode-code-pre">
    SELECT
    product_name,
    REPLACE(product_url, ' ', '%20')
    from your_table;
    </pre>

    As far as smaller images go, that'd be up to Andy.

    --scott

    --
    Scott Jangro
    sjangro@befree.com

  17. #17
    ABW Ambassador sjangro's Avatar
    Join Date
    January 18th, 2005
    Location
    Boston
    Posts
    1,529
    Actually, an easier solution would be to run this update SQL statement on your product table after you load it with a new datafeed.

    <pre class="ip-ubbcode-code-pre">
    update your_table
    set product_url = replace(product_url, ' ', '%20')
    </pre>
    That will accomplish the same thing once and for all. Obviously you'll need to do that after you load the new data each time.

    Of course there are probably many other ways to do it as well. Or as the Perl developers say, TMTOWTDI.

    --scott

    --
    Scott Jangro
    sjangro@befree.com

  18. #18
    "An Englishman In New York" TJ's Avatar
    Join Date
    January 18th, 2005
    Posts
    3,282
    Thanks for all your help scott, I can live with the images being large in my application, and the %20 is not an issue for me either, I encode the url in my script.

    You have given some really useful hints for all us dummies out here [img]/infopop/emoticons/icon_smile.gif[/img]

  19. #19
    Newbie
    Join Date
    January 18th, 2005
    Posts
    21
    ^akoss^

    FYI

    I use the LOAD DATA Query also, and my hosting company just upgraded to the new version of MySQL.

    apparently the LOAD DATA LOCAL command has been disabled, so in case you use the LOCAL subcommand, and your hosting company upgrades keep in mind you can easily remove the LOCAL, it just means the query runs a tad slower

    partners@bigvaluezone.com
    ------------------------------
    Let us show you where the best online deals are!
    --------------------------------
    http://www.bigvaluezone.com

  20. #20
    "An Englishman In New York" TJ's Avatar
    Join Date
    January 18th, 2005
    Posts
    3,282
    Haiko or whoever.... can this thread be moved into the Datafeed forum?

  21. #21
    Affiliate Marketing Consultant Andy Rodriguez's Avatar
    Join Date
    January 18th, 2005
    Location
    Sunny Miami
    Posts
    8,384
    <BLOCKQUOTE class="ip-ubbcode-quote"><font size="-1">quote:</font><HR>Originally posted by ^akoss^:
    Haiko or whoever.... can this thread be moved into the Datafeed forum?<HR></BLOCKQUOTE>

    Moved.... [img]/infopop/emoticons/icon_wink.gif[/img]

    Andy Rodriguez,
    Online Advertising / Affiliate Marketing Manager

    TigerDirect.com
    P: (305) 415-2313
    E: andy.rodriguez@tigerdirect.com
    ICQ: 175010
    AIM: miamitigercub

  22. #22
    ABW Ambassador CrazyGuy's Avatar
    Join Date
    January 18th, 2005
    Posts
    1,463
    <BLOCKQUOTE class="ip-ubbcode-quote"><font size="-1">quote:</font><HR>Originally posted by Scott - Be Free:
    Or as the Perl developers say, TMTOWTDI.
    <HR></BLOCKQUOTE>

    Or as this Perl developer says, TMTOWTFIU [img]/infopop/emoticons/icon_biggrin.gif[/img]

    Are you Crazy?

  23. #23
    ABW Ambassador cditty's Avatar
    Join Date
    January 18th, 2005
    Location
    Memphis TN
    Posts
    1,434
    I think this is the actual mantra from any programmer. I know I think this ALOT. [img]/infopop/emoticons/icon_smile.gif[/img]

    Chris

    ----------------------------
    Scriptsforyoursite.com
    Datafeed scripts and more.....
    Coming soon

  24. #24
    Member
    Join Date
    January 18th, 2005
    Posts
    52
    if you datafeed file has special characters and after you load into mysql, won't the special characters be screwed up?

    for example, my feed has "PSYCâ„¢ CD", and obviously mysql cannot store the string right. Is there any solution? Thanks.

  25. #25
    Member
    Join Date
    January 18th, 2005
    Posts
    52
    hi anyone? Thanks!

+ Reply to Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. MySQL LOAD DATA INFILE question
    By PatrickAllmond in forum Programming / Datafeeds / Tools
    Replies: 12
    Last Post: March 4th, 2006, 01:47 PM
  2. MYSQL LOAD DATA - access denied
    By vivekar in forum Programming / Datafeeds / Tools
    Replies: 6
    Last Post: October 4th, 2004, 04:58 PM
  3. Three Level Hierarcy from One Datafile?
    By 1CNS in forum WebMerge (Fourthworld.com)
    Replies: 1
    Last Post: August 31st, 2003, 08:47 PM
  4. Datafile is not user friendly
    By Heyder in forum Commission Junction - CJ
    Replies: 91
    Last Post: December 30th, 2002, 09:17 PM
  5. no datafile in last few days?
    By addall in forum Commission Junction - CJ
    Replies: 18
    Last Post: December 20th, 2002, 10:54 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
  •