Results 1 to 9 of 9
  1. #1
    Full Member
    Join Date
    January 18th, 2005
    Posts
    379
    I have some raw data that I was planning to insert into a flat file and then upload/insert into my mySQL DB. However the data contains commas and pipes "¦". I usually use the ¦ as my delimiter.

    Before I upload the data I wanted to remove dupes which happen to be text/blob fields. I usually do this in access.

    I thought I could get around this by using quotes as a text qualifier.
    "some name","Some category","descripton which "can" have quotes"

    It didn't seem to work. My other method I usually try is to use some combination of characters like <¦¦> as a delimeter.
    However Access only allows one character as a delimeter and phpmyadmin only allows two characters as the delimeter.

    Any ideas how to work around this?

    <hr>
    Leave the gun, take the cannolis.

  2. #2
    Resident Genius and Staunch Capitalist Leader's Avatar
    Join Date
    January 18th, 2005
    Location
    Florida
    Posts
    12,817
    Try tab...

    ~Cheapskate merchants need not apply

  3. #3
    ABW Founder Haiko de Poel, Jr.'s Avatar
    Join Date
    January 18th, 2005
    Location
    New York
    Posts
    21,609
    <BLOCKQUOTE class="ip-ubbcode-quote"><font size="-1">quote:</font><HR>Originally posted by Leader:
    Try tab...<HR></BLOCKQUOTE>

    Tab is a great tool for feeds --- froogle uses it as do many other "standardized" feeds.

    <font size="2" face="Verdana">Haiko


    The secret of success is constancy of purpose. ~ Disraeli
    </font></p>

  4. #4
    ABW Veteran Student Heyder's Avatar
    Join Date
    January 18th, 2005
    Posts
    5,482
    He can't use tab because his data already contains commas and pipes as set delimiters. The only thing I know you could do is run a find and replace on the pipes turning them into commas and hope it works.

  5. #5
    You are in, or you are out ... choose!
    Join Date
    January 18th, 2005
    Posts
    459
    Bad move to replace the Pipes with Commas, as if there are comma in the text, your fields will be all over the place. Better to replace [Comma Pipe Comma] with [Tab] as Leader suggests and then go from there.

    dWoz - serious webmaster resources.

  6. #6
    ABW Founder Haiko de Poel, Jr.'s Avatar
    Join Date
    January 18th, 2005
    Location
    New York
    Posts
    21,609
    <BLOCKQUOTE class="ip-ubbcode-quote"><font size="-1">quote:</font><HR>Originally posted by M.McNabb:
    He can't use tab because his data already contains commas and pipes as set delimiters. <HR></BLOCKQUOTE>

    Not TRUE, you can open the file in excel clean it, and then do a "save as" Text( Tab delimited) file.

    <font size="2" face="Verdana">Haiko


    The secret of success is constancy of purpose. ~ Disraeli
    </font></p>

  7. #7
    Full Member
    Join Date
    January 18th, 2005
    Posts
    379
    I haven't checked it but there is a good chance the data has tabs in it. It seems to have just about every typable character someplace.

    If I was able to use tabs what would I enter in phpmyadmin as the delimeter... \t ?


    What I may do is replace all commas in the data with html commas(& # 44) or better yet replace all ^ with html versions(^) and then use the ^ as the delimiter.


    I have abot 80,000 records which isn't a lot except each record has about a page worth of text. It's close to 600 megs of data total. I broke it up into 10-20 meg chunks so my desktop apps could handle it better. I think the most I ever stuffed into Access is 120megs. I want to get it into Access to run a query to remove dupes before I upload it.

    I am grateful I have DSL.

    &lt;hr&gt;
    Leave the gun, take the cannolis.

    [This message was edited by MrSpeed on March 10, 2004 at 10:59 PM.]

    [This message was edited by MrSpeed on March 10, 2004 at 11:00 PM.]

  8. #8
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    594
    use a csv format and put quotes around the data.

    IE:

    "field 1","field 2,additional info", "filed 3 with ||| pipes"

    IamJaloppy

  9. #9
    Full Member
    Join Date
    January 18th, 2005
    Posts
    379
    I haven't tried to upload yet but it looks like I need to double-quote existing quotes as well as wrapping each field in quotes. I ran into this isuue a few years ago when handling forms that need to insert the textarea into a DB.

    John "The Hammer" Smith, male, New York

    becomes

    "John ""The Hammer"" Smith", "male", "New York"


    To solve this I entered all kinds of crazy data into Excel and saved it as a CSV to see how it handled the quoting.

  10. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. SEO and Commas, Hyphens and Pipes in Page Titles
    By leeann in forum Search Engine Optimization
    Replies: 13
    Last Post: June 29th, 2014, 09:06 PM
  2. Yahoo Pipes for Datafeeds?
    By MINDsprinter in forum Programming / Datafeeds / Tools
    Replies: 3
    Last Post: November 30th, 2011, 02:33 PM
  3. Commas in Keywords - Yes or No
    By Afilyit in forum Newbie Affiliate FAQs & Helpful Articles
    Replies: 5
    Last Post: May 23rd, 2008, 08:19 AM
  4. Commas and URLS
    By Kevin in forum Midnight Cafe'
    Replies: 2
    Last Post: August 11th, 2005, 01:28 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
  •