[SOLVED] text file

[jakebur01]

How can I...

 

- count the columns of a tab and/or comma delimeted file based on file type.

 

- create a mysql table named "smith_price" and add the columns to match the text file.

 

Example... 1 Count the columns in text file ... 2 text file has 5 columns ... 3 create mysql table "smith_price" with 5 columns : col1, col2, col3, col4, col5

 

_____________

 

Thank you,

 

Jake

[Brian W]

<?php
$content = file_get_contents($filename);
//Parse the columns by a specified deliminator
$del = ","; //could be "	" or ";" or anything else you want
//$content = "Hi, Hello, Dog, Cat"; //for testing
$cols = explode($del, $content);
$sql = "ALTER TABLE `".$table_name."` ADD (";
foreach($cols as $col){
$sql .= trim($col)." varchar(256),";
}
$sql  = rtrim($sql, ",");//Get rid of that last "," in the statment
$sql .= ");";
//Go ahead and execute however you want. Proble "mysql_query($sql) or die(mysql_error()."<br>".$sql);"
?>

Does that help?

May I ask what this is for?

[jakebur01]

Hi Brian,

 

Thank you for helping me with this. This is to create a price file that can be imported into an inventory program that runs on a unix server. We receive our txt file (price file) from our vendor that contains several columns (which are different every time). We only need a few columns off of this file in order to build our new importable file.

 

We usually build our importable file in excel and it takes 2 to 3 hours. You have to open the vendor file and make several calculations in the new file before it can be imported (ex. dealer cost = cost x .75). Then, we have to ftp it onto our unix box and uploaded it into the inventory program from there.

 

Here are the steps I am wanting to take with php. Which will take only a few clicks as opposed to a few hours in excel.

 

1. page one ------ select file

 

2. page two -------- upload file into "upload" directory  ...... rename file  ......  store file name in session ..... look at file to see how many columns it contains ..... create mysql table with correct number of columns with columns named like col1, col2, and on up ..........  then do a "load data infile" .........  now display onto the page a table that contains the columns and the first row in the table.......    display a drop down menu (selecting column) {one for cost, retail, etc.}  this will tell us the columns that we need and identify them (ex cost == col4, retail==col2, and so on)

 

3. page three --------- post retail and cost (that contains the correct columns) ....... make calculations for the new importable file ...... create file on unix box with the file name we stored in the session earlier...... then open up the file on the unix box (fopen("ftp://user:[email protected]/yourfile.txt", "w"); ) and start writing our new updated file

 

display file name

 

then delete txt file in upload directory and delete the mysql table

[jakebur01]

page 1

<form enctype="multipart/form-data" action="upload2.php" method="POST">
Please choose a file: <input name="uploaded" type="file" /><br />
<input type="submit" value="Upload" />
</form>

 

page 2

session_start();
//This function separates the extension from the rest of the file name and returns it 
if ($uploaded_type =="text/xls")
{
echo "No excel files<br>";
$ok=0;
}
elseif ($uploaded_type =="text/doc")
{
echo "No word files<br>";
$ok=0;
}
if ($ok==0) 
{ 
Echo "Sorry your file was not uploaded"; 
} 

//If everything is ok we try to upload it 
else 
{ 

//This applies the function to our file 
$ext = findexts ($_FILES['uploaded']['name']) ; 
$ext="txt";

//This line assigns a random number to a variable. You could also use a timestamp here if you prefer. 
$ran = rand () ;
$ran1 = MTD.".".$ran;

//This takes the random number (or timestamp) you generated and adds a . on the end, so it is ready of the file extension to be appended.
$ran2 = $ran1.".";

//This assigns the subdirectory you want to save into... make sure it exists!
$target = "upload/";
$filename=$ran2.$ext;
$_SESSION[filename]=$ran2.$ext;


//This combines the directory, the random file name, and the extension
$target = $target . $ran2.$ext;


if(move_uploaded_file($_FILES['uploaded']['tmp_name'], $target)) 
{
echo "The file has been uploaded as ".$ran2.$ext;

////////// count columns and create table here








} 
else
{
echo "Sorry, there was a problem uploading your file.";
}

 

[Brian W]

So what I gave you is kinda useless unless you just need to build a table.

<?php
//...........
echo "The file has been uploaded as ".$ran2.$ext;
//You could make the table name what you want
$table_name = "temptable".rand(100,999).rand(100,999); //something like "temptable182765"
////////// count columns and create table here
$content = file_get_contents($filename);
//Parse the columns by a specified deliminator
$del = ","; //could be "   " or ";" or anything else you want
//$content = "Hi, Hello, Dog, Cat"; //for testing
$line = explode("\n", $content);
$cols = explode($del, $line[0]);
$sql = "CREATE TABLE `".$table_name."` ADD (";
$i=1;
foreach($cols as $col){
   $sql .= "col".$i++." varchar(256),";
   }
$sql  = rtrim($sql, ",");//Get rid of that last "," in the statment
$sql .= ");";
//Go ahead and execute however you want. Proble "mysql_query($sql) or die(mysql_error()."<br>".$sql);"
$_SESSION['table_name'] = $table_name; //Setting the table name into a session variable
?>

That should create a table (after you specify $table_name) I almost sure.

Now you have a table with x number of columns named col1, col2, col3 and so on.

 

Buts that's all I got for you now. If you need more help, write code till something is broken and your own trouble shooting skill do not fix it then post back on phpfreaks. Or, you could hire a programmer on the freelance board (or even PM me and I could get you a quote).

[jakebur01]

THank you soo much....

 

This will really get me started.