Results 1 to 13 of 13
  1. #1
    Believe knight01's Avatar
    Join Date
    August 14th, 2006
    Location
    Dayton, Ohio
    Posts
    1,815
    Pipe Delimited to Comma Separated
    Anyone know of a quick and easy way to convert a pipe delimited file to a comma separated file? I know this can be done in excel and other spreadsheets, I need to do it on the server without downloading and re-uploading it so that it can be run via cron.

    Perfer to use php to read the file and then re-write each line exchanging the pipes for commas, but I need to also add quotes at the beginning of each field to encapsulate the field in case commas are used inside the field.
    Someday starts today
    Military Discounts

  2. #2
    Moderator MichaelColey's Avatar
    Join Date
    January 18th, 2005
    Location
    Mansfield, TX
    Posts
    16,232
    CSV is actually a little more complicated than that. You also have to handle embedded quotes and other things. It's probably best to find a module/library (whatever PHP calls it) to handle the conversion so you don't have to worry about all the intricacies.
    Michael Coley
    Amazing-Bargains.com
     Affiliate Tips | Merchant Best Practices | Affiliate Friendly? | Couponing | CPA Networks? | ABW Tips | Activating Affiliates
    "Education is the most powerful weapon which you can use to change the world." Nelson Mandela

  3. #3
    Affiliate Manager
    Join Date
    January 18th, 2005
    Location
    Los Angeles, California
    Posts
    1,913
    Why migrate from a simpler format to a more complicated one?

    CSV is the devil. Who invented a format where the delimiter is one of the most commonly used characters likely to be found in the data? In a just world that person would be found and put into stocks in the town square with a sign around their neck stating: "I cost the programming world tens of millions of hours of lost productivity".

    Rather than a single well-defined format, it's an ad hoc collection of some half-dozen or more variants which differ in how commas are escaped, how in-data returns are handled, when quotes are used, and worse. Even among Microsoft products, CSV formats vary from product to product and sometimes between versions of the same product.

    It's common for robust CSV parsing to consume at least an order of magnitude more clock cycles than parsing any of the more consistent formats, like tab- or pipe-delimited. To handle all the possible variants you need to walk character by character through the data, keeping track of when you're inside a field value and when that field ends. Argh. With the more consistent formats you just seek to the next delimiter; simple, clean, efficient.

    If at all possible, you'll have a much better time working with pipe-delimited data when you can.
    Richard Gaskin
    Developer of WebMerge: Publish any data feed on any site
    http://www.fourthworld.com

  4. #4
    Comfortably Numb John Powell's Avatar
    Join Date
    October 17th, 2005
    Location
    Bayou Country, LA
    Posts
    3,432
    I made a PHP Class a while back that I call FeedLoader. It will take each networks preferred feed setup and load it to my database. Each merchants table is exactly the same even though some columns are empty. Like FourthWorld I hate the comma and love the pipe.


  5. #5
    .
    Join Date
    January 18th, 2005
    Posts
    2,973
    TextPipe Pro, from DataMystic, is a pretty impressive tool for homogenizing datafeeds. Not cheap, though.

  6. #6
    Believe knight01's Avatar
    Join Date
    August 14th, 2006
    Location
    Dayton, Ohio
    Posts
    1,815
    Thanks for the replies and input. Unfortunately it's not my call. The script I"m using will only import csv files. I've asked the author to support pipe, tab and xml files, but that is not something he's interested in doing.

    So, I'm stuck trying to convert pipe to csv. (shortly going to deal with XML files as well, Although I would be happy just to know how to import those into a msql db directly, but that's a different project.)


    @Michael Good point, I had forgotten about the quotes within fields and they will need to be escaped during the process.

    @Mark I've used TextPipe Pro on my desktop, never tried it on a remote server in a hosting environment. I'll read up on it.
    Someday starts today
    Military Discounts

  7. #7
    Comfortably Numb John Powell's Avatar
    Join Date
    October 17th, 2005
    Location
    Bayou Country, LA
    Posts
    3,432
    Thanks for the replies and input. Unfortunately it's not my call. The script I"m using will only import csv files. I've asked the author to support pipe, tab and xml files, but that is not something he's interested in doing
    Is it open source php that we could modify?


  8. #8
    Believe knight01's Avatar
    Join Date
    August 14th, 2006
    Location
    Dayton, Ohio
    Posts
    1,815
    Sadly it's encrypted or I would play around with it until it worked with the other formats.

    On the upside I may have a solution with a different script I own that is unencrypted, and would convert the files to csv for use with the encrypted script, just have to check if the license allows me to use it in the way I want to.
    Someday starts today
    Military Discounts

  9. #9
    ABW Ambassador bettylou's Avatar
    Join Date
    December 27th, 2005
    Location
    Indiana
    Posts
    595
    Yeah, I would definitely prefer the pipe delimited.

    I have a script that downloads the file and replaces whatever I need to be replaced before entering the data into the database table(s). Maybe you could do something like this:

    $file = "name_of_your_file.txt";
    $read_file = file_get_contents($file);
    $open_file = fopen($file, "w");
    $switch = str_replace('|', '"\,"', $read_file);
    fwrite($open_file, $switch);
    fclose($open_file);


    Note: You may have to play around with the '"\,"' in the str_replace to get the escaped characters just right.
    Last edited by bettylou; November 19th, 2009 at 04:14 PM. Reason: Added Note

  10. #10
    Believe knight01's Avatar
    Join Date
    August 14th, 2006
    Location
    Dayton, Ohio
    Posts
    1,815
    BettyLou, that is almost exactly what I've written, but was having so many issues with it I was looking for a easier solution.

    Biggest issue was putting a quote at the end of the line. I tried using a str_replace('/n|', '""/n"', $read_file); But that caused all kinds of craziness!
    Someday starts today
    Military Discounts

  11. #11
    ABW Ambassador bettylou's Avatar
    Join Date
    December 27th, 2005
    Location
    Indiana
    Posts
    595
    Just a quick thought but have you tried \r\n instead?

  12. #12
    ABW Ambassador bettylou's Avatar
    Join Date
    December 27th, 2005
    Location
    Indiana
    Posts
    595
    Just found this on another site:

    $p = ereg_replace ("/\r\n|\n\r|\r|\n/", "\"\,\"", $p);

    might be worth a try.

  13. #13
    Newbie
    Join Date
    March 30th, 2014
    Posts
    1
    the answer is:

    str_replace("|", ",", $content);


    as in

    function getTXTfile($url, $outputFile){
    $content=file_get_contents($url);
    $content = str_replace('DATE|SYMBOL|SHORT VOLUME|TOTAL VOLUME|MARKET', '',$content);
    $content = str_replace("|", ",", $content);
    $content = trim($content);
    file_put_contents($outputFile, $nqbx);
    }

    Now you can close the thread...

  14. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Pipe delimited Datafeed To MySQL
    By nick_mayhem in forum Programming / Datafeeds / Tools
    Replies: 7
    Last Post: September 18th, 2006, 09:56 AM
  2. javascript for PIPE DELIMITED (|) database
    By davon in forum ShareASale - SAS
    Replies: 0
    Last Post: December 29th, 2005, 06:33 PM
  3. Pipe delimited to tab
    By bbb in forum Programming / Datafeeds / Tools
    Replies: 10
    Last Post: August 30th, 2004, 06:03 AM
  4. Using an Affiliate Feed with Exel (Pipe Delimited)
    By jdsguam in forum Programming / Datafeeds / Tools
    Replies: 3
    Last Post: April 22nd, 2004, 09:57 AM
  5. Should keywords be separated by a space as well a a comma?
    By Radegast in forum Search Engine Optimization
    Replies: 13
    Last Post: August 29th, 2003, 04:50 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
  •