[Resolved] load .txt file into mySQL

[aconway]

I am trying to create a script that ftp's a txt file from one server to another and then load it into a database. I first created a code that loads the txt file into the database and it works fine, but when I put the ftp code before it I get this error: 'Column count doesn't match value count at row 1'; by they way the ftp part works fine. Here is the code:

//connect to hosts
$fconn = ftp_connect($furl);
	if (!$fconn) { echo 'Could not connect to Remote server'; exit; }
$tconn = ftp_connect($turl);
	if (!$tconn) { echo 'Could not connect to Local server'; exit; }

//log in to hosts
@ $fresult = ftp_login($fconn, $fuser, $fpass);
	if (!$fresult) { echo 'ERROR: Could not log on as '.$fuser.' <br>'; ftp_quit($fconn); exit; }
@ $tresult = ftp_login($tconn, $tuser, $tpass);
	if (!$tresult) { echo 'ERROR: Could not log on as '.$tuser.' <br>'; ftp_quit($tconn); exit; }

//transfer file
echo 'Transfering '.$file.' database<br>';
$fp = fopen($file.'.txt', 'w');
if (!$success = ftp_fget($fconn, $fp, $ffile, FTP_ASCII)) {
	echo 'ERROR: Could not transfer '.$file.' database';
	ftp_quit($fconn);
	ftp_quit($tconn);
	exit;
	}
	fclose($fp);
	echo 'Successfully transfered '.$file.' database!<br>';

//close connectins
ftp_quit($fconn);
ftp_quit($tconn);



//insert into mySQL


db_connect();

$drop = 'delete from '.$file;
mysql_query($drop);

$fcontents = file('./$file.txt');

for($i=0; $i<sizeof($fcontents); $i++) { 
	$line = trim($fcontents[$i]);
	$arr = explode("\t", $line);
	# if your data is comma separated
	# instead of tab separated,
	# change the '\t' above to ',' 


	$sql_insert = "insert into ".$file." values ('".implode("','", $arr)."')";
	mysql_query($sql_insert);
	if(mysql_error()) {
		echo mysql_error().'<br>';
		echo 'Could not update '.$file.' database<br>';
	} else {
		echo 'Your '.$file.' database should be updated.<br>';
		echo '<a href="/form.php">Return to main page</a>';
	}
}

if anyone has an idea why I'm getting an error, PLEASE let me know

Thanks

[nemonoman]

Try fopen using the binary option.

fopen(myfile, 'rb')

[aconway]

I replaced
$fp=fopen($file.'.txt',_'w');

with

$fp=fopen($file.'.txt',_'rb');

but I get the same results.

[nemonoman]

I think you need to use binary option for all file managment:

FTP_BINARY
not
FTP_ASC

'b' option on all file elements like fopen, etc.

[aconway]

I got it.

$fp had to be read and writeable soo...

$fp = fopen('$file.txt', 'w');

and keep it FTP_ASCII,

Thanks for the guidance

[nemonoman]

excuse me, I think you want to use BINARY in all cases, not ascii.
$fp = fopen($file.'.txt', 'wb');
if (!$success = ftp_fget($fconn, $fp, $ffile, FTP_BINARY)) {

[aconway]

I tried Binary and ASCII and they both work I think that what needed to be in place was the dang 'w' instead of 'r'. What is the difference between Binary and ASCII? I know the gist of it. But, I thought that ASCII was better for text files.

any way thanks again