[SOLVED] Modifying first line of uploaded file

[NerdConcepts]

I had a topic kind of based on this, but I think the title kept people away.

 

Here is what my script does, you select a .csv or file that follows those guildlines. Script then loads it into mySQL database using the first line as a reference to which field each row of the .csv (spreadsheet) goes into. One problem: some of the first line references have spaces in them. Since MySQL doesn't support spaces I need to take that first line and change them all: example

 

WORK ORDER,CUSTOMER NAME,CUSTOMER ADDRESS needs to become WORK_ORDER,CUSTOMER_NAME,CUSTOMER_ADDRESS

 

....is there a ways to do this? I know I'll probably have to upload the file first, before I can modify that first line. I cannot replace that first line with a specific text, since the order of the fields change sometimes. Thanks a bunch and I'm sorry for kind of posting again. But my last problem with getting the CSV to actually import right, well I figured that one out.

[chigley]

<?php

/* I'm hoping you already have a variable containing everything in the file */

$filecontents = "WORK ORDER,CUSTOMER NAME,CUSTOMER ADDRESS
1,Charlie,England
2,Bob,USA";

/* This is what you need to use once you have that variable: */

$lines = explode("\n", $filecontents); // Split the variable into separate lines
$lines[0] = str_replace(" ", "_", $lines[0]); // The first line has had it's spaces turned to underscores now
$filecontents = join("\n", $lines); // Join all of the lines back together into the same variable

/* Test output */

echo "<pre>{$filecontents}</pre>";

/* Output: *\

WORK_ORDER,CUSTOMER_NAME,CUSTOMER_ADDRESS
1,Charlie,England
2,Bob,USA

\*         */

?>

[NerdConcepts]

Thanks, this does put me a step closer, except this doesn't seem to work fully. I am going ahead and uploading the file, it uploads the file, and it does replace all the first line spaces with "_" except it never rewrites it back to the file. When you echo the $filecontents is does show the c 1,c 2,c 3 (test stuff) is c_1,c_2,c_3. Except if you look at the file it is still c 1,c 2,c 3 ... So I can't really see if it will add it to the database, I'm sure it will once it actually writes to the file. I'm sure it's something I have done with the reading of the file, except that does appear to work right.

 

Here is the code to upload, modify and import into MySQL.

$target = "csv_storage/";
$target = $target . basename($_FILES['file_source']['name']) ;
$ok=1;
if(move_uploaded_file($_FILES['file_source']['tmp_name'], $target)) {
	echo "The file ". basename($_FILES['file_source']['name']). " has been uploaded";
} else {
	echo "Sorry, there was a problem uploading your file.";
}

$fcontents = "csv_storage/" . $_FILES['file_source']['name'];
$fh = fopen($fcontents, 'r'); // opens the read
$filecontents = fread($fh, filesize($fcontents)); // reads all data

$lines = explode("\n", $filecontents); // Split the variable into separate lines
$lines[0] = str_replace(" ", "_", $lines[0]); // The first line has had it's spaces turned to underscores now
$filecontents = join("\n", $lines); // Join all of the lines back together into the same variable
fclose($fh);

$csv = new Quick_CSV_import();

$csv->file_name = "csv_storage/" . $_FILES['file_source']['name'];
$csv->import();

echo "<br />csv_storage/" . $_FILES['file_source']['name'];
echo "<br /><pre>{$filecontents}</pre>";

 

**EDIT** btw, it does have to look for a file, after this, which I have most of the code written, I'll have the ability to grab csv files from the server that are stored in a certain folder. So the code does need to be looking at a file. The whole read from upload isn't going to work right. Hinze the reason I am uploading now...

[NerdConcepts]

Hmm, I'm dumb I know. Ok, I've it going ahead and uploading the file, reading it, modifying the first line, updating all the data, which it does the just fine. Just when I go onto adding the file to the database (which used to work) it cannot find the file.

 

original csv file:

c 1,c 3,c 2

testc1,"c3,c3",testc2

 

modified file, final upload

c_1,c_3,c_2

testc1,"c3,c3",testc2

 

so yeah the upload, read, modify, write works just fine now. Here is the new code, not much changed.

$target = "csv_storage/";
$target = $target . basename($_FILES['file_source']['name']) ;
$ok=1;
if(move_uploaded_file($_FILES['file_source']['tmp_name'], $target)) {
	echo "The file ". basename($_FILES['file_source']['name']). " has been uploaded";
} else {
	echo "Sorry, there was a problem uploading your file.";
}

// Read Uploaded File
$fcontents = "csv_storage/" . $_FILES['file_source']['name'];
$fh = fopen($fcontents, 'r');
$filecontents = fread($fh, filesize($fcontents));

// Modify Data
$lines = explode("\n", $filecontents);
$lines[0] = str_replace(" ", "_", $lines[0]);
$filecontents = join("\n", $lines);
fclose($fh);

// Write New Data
$fw = fopen($fcontents, 'w');
$stringData = $filecontents;
fwrite($fw, $stringData);
fclose($fw);

// Import CSV
$csv->file_name = "csv_storage/" . $_FILES['file_source']['name'];
$csv->import();

 

when you echo "csv_storage/" . $_FILES['file_source']['name']; it comes up with "csv_storage/test.csv" which is the correct location. Yet when it runs in the script I get this error:

 

An error occurred in script 'C:\wamp\www\csvtophp\classes\csv_upload.php' on line 46:

Query: LOAD DATA INFILE 'csv_storage/test2.csv' INTO TABLE `test` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\' IGNORE 1 LINES (`c_1`,`c_3`,`c_2`)

MySQL Error: File 'c:\wamp\mysql\data\csv_storage\test2.csv' not found (Errcode: 2)

 

No idea when it is looking in c:\wamp\mysql\data for the file...when the website is c:\wamp\www\csvtophp ... is this something to do with WAMP?

[solarisuser]

1) Put the full path to the csv file

 

2) You might need to escape the slashes  (C:\\path\\file.csv)

[NerdConcepts]

Awesome. That does work. But what happens when this is on a internet server? I've tried putting an internet path and it hangs WAMP, or gives a access denied if on a internet server.

 

example server path "/home/serverlogin/public_html/csvtophp/" that won't work. Unless I can figure out how to get PHP to auto figure out that path, that may work.

[solarisuser]

I've never tried to use a csv from a URL or whatever, but if you need to grab a csv file from another site, then look into using PHP's curl or ftp functionality to grab the file and save it to a randomized name, do your parsing, save it to a filename of your choice, and delete the temp file.

[solarisuser]

BTW - If you want an awesome PHP uploader, just use this:

 

http://labs.beffa.org/w2box/

[NerdConcepts]

Tried...get this:

MySQL Error: Can't get stat of 'http:/www.servername.com/csvtophp/csv_storage/test.csv' (Errcode: 2)

 

So, no idea what is going on. I know the importer works, so I am about to throw my monitor

 

also, I know how to write an uploader that gives a the file a random name, no need tho. All the .csv files will have unique names.

[NerdConcepts]

Ok, figured it out. using the servers absolute path I do get results. Had the access deny message because the server wasn't allowing me to run the Query. Not sure why. But it works. Thanks everyone for all the help.