Uploading excel spreadsheet

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Uploading excel spreadsheet

Post by mohson »

Guys,
what would be the best way to upload and excel spreadsheet to my mysql database and any example of how to do it.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

export excel as cvs, and then load into mysql.

websearch, or this forum has load of examples
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Post by mohson »

everywhere I look there is information on mysql to excel but not the other way round apart from shareware websites selling converters to help you do it.

Maybe im just not getting your answer but when u say use csv the only thing that comes to my head is "ok so I open the file and then save it with the extention csv"

A lot of other posts mention CSV but thats all they mention nothing else - maybe this is enough information but I dont get it??

Anyway I fully understand if you have given up on me by this stage but I would appreciate the help.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Do you want to store just the information from the spreadsheet in the table, the actual spreadsheet itself (as a BLOB), or do you want to upload the spreadsheet to the server, and store it's location in the database?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Post by mohson »

I just want to update the date that is held in my excel spreadsheet to the mysql table in my mysql database the data sets match up exactly because my mysql tables were designed based completely on the excel spreadsheet
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Well, then you're going to have to somehow open up the spreadsheet and write the date into it. PEAR has a class that allows one to create, and I'd imagine modify, Excel spreadsheest.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

CSV means Comma Separated Values. If you save an excel file as a csv and then open it in a text editor you will see that it is what it's called. When the data is in this format it is very easy for php to grab it and do things (like insert it into mysql) with it.
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Post by mohson »

Thank you, I have saved it as .csv. when I now open the file in notepad I am presented with all the data that was in the particular sheet I am trying to upload.

The only difference is that each item of data is seperated by commas.

Will it now be possible to upload this sheet into my mysql table - Using the the LOAD INFILE command or something similar to that??
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Post by mohson »

I used the

Code: Select all

LOAD DATA LOCAL INFILE 'people.txt'' INTO TABLE people
LINES TERMINTED BY  '\r\n':
and the query was ok but then when I had a look at my tables the LOAD statement had created all the person_is but all other data was missing - so it created 600 odd records with only person_id' s and nothing else.

people.txt contains all the data sepereated by commas

does anyone know why this happened??
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you didn't specify how fields were terminated.
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Post by mohson »

I looked at the mysql manual and it never said anything about fields - how would I go about doing this?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

LOAD DATA INFILE Syntax wrote:

Code: Select all

LOAD DATA їLOW_PRIORITY | CONCURRENT] їLOCAL] INFILE 'file_name.txt'
    їREPLACE | IGNORE]
    INTO TABLE tbl_name
    їFIELDS
        їTERMINATED BY '\t']
        їїOPTIONALLY] ENCLOSED BY '']
        їESCAPED BY '\'' ]
    ]
    їLINES 
        їSTARTING BY '']    
        їTERMINATED BY '\n']
    ]
    їIGNORE number LINES]
    ї(col_name,...)]
sure looks like it's there... :roll:
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Post by mohson »

thank you for that but I keep getting

Code: Select all

ERROR 1064 you have an error in your sql syntax near 'їLOW PRIORITY ¦ CONCURRENT] їLOCAL] INFILE
'people.txt'
їREPLACE ¦ IGNORE] INTO' at line 1
I cannot work out what is wrong its really frustrating

any help would be greatly appreciated
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you may want to read how MySQL documentation lays out their syntax information.
mohson
Forum Contributor
Posts: 372
Joined: Thu Dec 02, 2004 6:58 am
Location: London

Post by mohson »

Maybe this is really stupid but am I using the wrong vertical bar... the only way to implement the key with signifies the full vertical bar is by holding down ctl alt and using that particular key which is also used for (`) and (¬) .

could it be that my sql isnt working for this reason if so how do I implement the full (real) vertical bar (pipe)

This is soo trivial but very confusing
Post Reply