Results 1 to 5 of 5
  1. #1
    Full Member
    Join Date
    January 18th, 2005
    Posts
    270
    I have a tab delimited file. I am loading it into a mysql table. When I load it with the script below, I end up with one line of fields, then one line with all fields blank (when I browse the table in phpmyadmin). This essentially is doubling the size of the table and really slows it down when I am updating some other tables from it.

    I have narrowed the problem down to how the "line feed" is delimited. When I import it thru phpmyadmin I can manually tell it how to delimit the lines, and it works.

    So here is my import part of my php script, what can I add to get this to stop:

    $handle = fopen('InvStatFile.txt', 'r');
    while ($data = fgetcsv ($handle, 9999, "\t"))
    {
    foreach ($data as $key => $value) $data[$key] = addslashes($data[$key]);
    $query = "INSERT INTO products_temp(`sku`, `stock`, `order`, `ph`, `ph1`, `ph2`, `ph3`)
    VALUES('$data[0]', '$data[1]', '$data[2]', '$data[3]', '$data[4]', '$data[5]', '$data[6]')";
    $result = mysql_query($query);
    }
    fclose ($handle);

    I know there must be some what to tell it how to delimit the line feeds, but I cannot figure it out!

    Burke

  2. #2
    ABW Ambassador FFoc's Avatar
    Join Date
    January 18th, 2005
    Posts
    1,015
    why not just use something like the unix commands 'flip', 'tr', 'sed' or 'dd' to twiddle the line end characters for you?
    “An adventure is an inconvenience rightly considered. An inconvenience is an adventure wrongly considered.” - G.K. Chesterton

  3. #3
    ABW Ambassador FFoc's Avatar
    Join Date
    January 18th, 2005
    Posts
    1,015
    Ah, there you go -- when you fopen your file, use this instead:

    <pre class="ip-ubbcode-code-pre">
    $handle = fopen('InvStatFile.txt', 'rb');
    </pre>
    “An adventure is an inconvenience rightly considered. An inconvenience is an adventure wrongly considered.” - G.K. Chesterton

  4. #4
    Full Member c4's Avatar
    Join Date
    January 18th, 2005
    Posts
    488
    Here's another way to go:

    $data=array();
    $lines=file("InvStatFile.txt");

    foreach ($lines as $thisline) {
    $thisline=chop($thisline);
    $data=explode("\t",$thisline);
    $query = "INSERT INTO products_temp(`sku`, `stock`, `order`, `ph`, `ph1`, `ph2`, `ph3`)
    VALUES('$data[0]', '$data[1]', '$data[2]', '$data[3]', '$data[4]', '$data[5]', '$data[6]')";
    $result = mysql_query($query);
    unset($data);
    }

    Regards
    [URL=http://www.hesk.com]Help desk software[/URL]

  5. #5
    Full Member
    Join Date
    January 18th, 2005
    Posts
    88
    Just a thought.

    Why not replace tabs into semicolons and Use the PHPMyAdmin load file feature?
    Or you can keep you tabs as delim and specify that 'tab' is the delim in PHPMyAdmin

    Loading csv file using foreach thrashes MySQL.
    It is not recommended.
    LoadFile feature is the best method to load Megabytes of records in a flash.

    --
    I think the real problem is not in the script.
    The problem is in CSV file. Your CSV file may contain special chars like \n \r \t etc.
    You should clean the file and load it.

    Have you tried loading the file with \n\r as the row terminator in phpmyadmin?

  6. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Coupon API blank lines
    By knight01 in forum ShareASale - SAS
    Replies: 0
    Last Post: December 2nd, 2009, 04:36 PM
  2. Replies: 4
    Last Post: June 23rd, 2009, 07:39 PM
  3. zip file datafeed file transfer into database
    By rahulcbr in forum Programming / Datafeeds / Tools
    Replies: 13
    Last Post: August 28th, 2007, 04:59 AM
  4. Record selection: generating unwanted blank lines
    By spaces in forum WebMerge (Fourthworld.com)
    Replies: 1
    Last Post: August 16th, 2006, 12:15 PM
  5. Blank Lines after Filter
    By rufus41 in forum WebMerge (Fourthworld.com)
    Replies: 1
    Last Post: January 15th, 2005, 10:52 AM

Posting Permissions

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