is this possible?

[coolphpdude]

hi there,

 

what i need to know is it is possibe to come up with a system where you can upload a spreadsheet for eample and you can extract the information from the cells to put the data in the relevant fields in an SQL table?

[coolphpdude]

for example i have an SQL table that requires the following fields:-

 

Fist Name                  Second Name              date of birth

 

 

but i have 600 users to add to this database. If i had a spreadsheet of 600 rows containing, first name, surname and DOB, could i come up with something that will automatically extract the information?

[efficacious]

heres what you do...

 

copy & paste the names out of the spread sheet onto seperate lines in a txt file..

 

FirstName LastName Birth

FIrstName LastName Birth ect....

 

then add stoppers so you know in your code where each section stops...

 

FirstName | LastName | Birth

 

with a decent txt editor this is a breeze

[coolphpdude]

then how do you go about adding it into an existing table automatically?

[efficacious]

from that point is simply setting up a loop to go through all the lines and pull out each string and put into database..

 

$file = fopen("test.txt","r");

echo fgets($file);

 

$filez = explode('|',$file,3);

 

$QUERY="INSERT INTO datatable (FirstName, LastName, Birth) VALUES ('".$filez[0]."', '".$filez[1]."', '".$filez[2]."');"

 

if($QUERY)

{

//SUCCESS

}

else

{

//FAIL

}

 

fclose($file);

[efficacious]

something like that ne ways.. you might have to play with it alil

[efficacious]

sry here it is within a loop

 

 

$file = fopen("test.txt","r");

while(! feof($file))
{
 echo fgets($file);

 $filez = explode('|',$file,3);

$QUERY="INSERT INTO datatable (FirstName, LastName, Birth) VALUES ('".$filez[0]."', '".$filez[1]."', '".$filez[2]."');"

if($QUERY)
{
 //SUCCESS
}
else
{
 //FAIL
}
}
fclose($file);

[coolphpdude]

so do a simple upload then if upload is successful get the file from the server (i.e /users/user.txt), run that script, if its successful delete user.txt from the server and respond completed otherwise respond fail?? Just to make sure i understand it properly...

 

 

As I already have a spreadsheet with users, is it possible for sumthing similar to be created with a spreadsheet?

[efficacious]

yes but I don't know how the spread sheet looks in a text editor and thats how php will read it.

 

(as far as i kno)

 

it will take nothing to put the names into a txt file.. easy as COPY&PASTE

 

then read through the txt file with the info line by line...

 

then explode the line into an array of 3 parts "First,Last,Birth"

 

then plug those variables into an insert statement and run the statement..

 

thus entering the values into the database..

 

then repeat.. (the loop)

[Mchl]

Save the spreadsheet as CSV file.

(both Excel and OO Calc can do this).

 

 

[edit]

 

Oh. And in mySQL you can use LOAD statement to load CSV directly into table (available as 'Import' through phpMyAdmin)

[JonnoTheDev]

What the posts above are stating is correct but there is a hell of a lot more needed (if I were making this) to consider.

The first is a proper error routine. If an error is detected do you stop at that point or continue inserting the data?

Each field must be validated to ensure that the correct data types are inserted into the correct mysql fields. So if the database is expecting an email address to go into an email field then the xls value must be an email address and not say a numeric value or non-email address value. As in coolphpdude's post you may be better using pipe delimiters rather than csv values as text often contains commas and can cause your data to be improperly separated.

[coolphpdude]

yeah i get what you mean. So validate the fields within the spreadsheet the same as i have validated the structure when creating the initial table? Then save as .csv. i have just taken a look in myphpadmin and isee what you mean... CSV using LOAD DATA. one question, with it being a spreadsheet does it automatically but the next column into the next field??

 

Cheers for all your help you lot, its appreciated.