1

I have a script that downloads and uploads a csv file (for inventory) for my website. When I manually download the file and upload it, everything is fine. When the script runs to download and upload the file, extra empty rows are added after every row in the csv file.

I found this out by directly downloading the file from the original source and comparing it to the file that gets uploaded by my script.

From there, the file is imported to a mysql database.

Anyone know how I can prevent these empty lines from being added to the csv file? It is causing problems when uploading the rows to my database and not adding anything new to my tables.

EDIT: Code to send csv info to mysql db:

$file = "./file.csv";

$handle = fopen($file, r);

$firstentry = 0;

while($csv = fgetcsv($handle))
{
if($firstentry == 0)
{
    $firstentry++;
}
else
{
    $csv[7] = strtotime($csv[7]);

    $csv[7] = date("Y-m-d H:i:s",$csv[7]);

    if($csv[18] == 217 || $csv[18] == 38)
    {
            if(!mysql_query("INSERT IGNORE INTO TABLE_NAME 
                (Col1, Col2, Col3, Col4, Col5,
                     Col6, Col7, Col8, Col9, Col10,                
                Col11, Col12, Col13, Col14, Col15,
                     Col16, Col17, Col18, Col19, Col20,
                Col21, Col22, Col23) VALUES
                ('".md5($csv[0].$csv[2])."','".addslashes($csv[0])."','".addslashes($csv[1])."','".addslashes($csv[2])."','".addslashes($csv[3])."','".addslashes($csv[4])."',
                    '".addslashes($csv[5])."','".addslashes($csv[6])."','".addslashes($csv[7])."','".addslashes($csv[8])."','".addslashes($csv[9])."',
                '".addslashes($csv[10])."','".addslashes($csv[11])."','".addslashes($csv[12])."','".addslashes($csv[13])."','".addslashes($csv[14])."',
                    '".addslashes($csv[15])."','".addslashes($csv[16])."','".addslashes($csv[17])."','".addslashes($csv[18])."','".addslashes($csv[19])."',
                '".addslashes($csv[20])."','".addslashes($csv[21])."')"))
            {                    
                exit("<br>" . mysql_error());
            }
            else
            {
                print_r($csv);
                echo " insert complete<br><br>";
            } 


    }        
}
}

Code to Download / Upload CSV File:

<?php
set_time_limit(0);
ini_set('memory_limit','512M');
ini_set('upload_max_filesize', '64M');
include('Net/SFTP.php');

//Original Location (SFTP)
$sftp2 = new Net_SFTP('example.com');
if (!$sftp2->login('username', 'password')) {
exit('Login Failed');
}

//echo $sftp2->pwd() . "\r\n";
$sftp2->get('file.csv', 'file.csv');
//print_r($sftp2->nlist());

echo "Success!";
?>

I am using Source Forge ( http://phpseclib.sourceforge.net/ ) for this.

NotJay
  • 3,919
  • 5
  • 38
  • 62
  • 3
    My crystal ball says "Show us some code" – Andy Jun 19 '12 at 15:54
  • in any case, search SO for `LOAD DATA INFILE` – Gordon Jun 19 '12 at 15:57
  • 1
    My crystal ball says a bad line-ending translator. – Marc B Jun 19 '12 at 15:57
  • specifically... the code to download/upload or the code to send the csv info to the db? – NotJay Jun 19 '12 at 15:57
  • I apologize for not adding code before. So Sorry – NotJay Jun 19 '12 at 16:05
  • The code above is for putting data into MySQL. You said the code also downloads data and uploads data. Guess the issue could be in the downloading part? – somnath Jun 19 '12 at 16:08
  • And as far as uploading the file... I had help here too: http://stackoverflow.com/questions/10981127/is-it-possible-to-download-a-file-from-one-server-sftp-and-upload-it-to-my-ser & http://bpaste.net/show/D2YC9hBfsLBdWKqB4Wn4/ – NotJay Jun 19 '12 at 16:08
  • My primary goal would be to eliminate the empty rows from appearing, otherwise, I guess my only other option would be to "ignore" the empty rows. – NotJay Jun 19 '12 at 16:27
  • You need to show code that is creating the file! – somnath Jun 19 '12 at 16:28
  • Your script is definitely doing some processing line by line and after each line is processed then saving it to the file and possibly adding a `\r\n` again to the end. This is causing an additional line in the file which you are not getting when you are downloading directly from the server. E.g. Pseudo code: > GET Line from CSV to a VAR. > > Do Some Processing with the columns > > Save the ROW again using VAR but accidentally add `\r\n` to the end of > the VAR. Since the last operation of adding the `\r\n` may not be required as it is already there. However, if you show the code here then the – somnath Jun 19 '12 at 15:57

1 Answers1

1

You could try this on the file contents after you download it:

$contents=preg_replace('/(\r\n|\r|\n)/s',"\n",$contents);
neubert
  • 15,947
  • 24
  • 120
  • 212