Page 1 of 2

Uploading excel spreadsheet

Posted: Wed Feb 02, 2005 10:07 am
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.

Posted: Wed Feb 02, 2005 10:11 am
by timvw
export excel as cvs, and then load into mysql.

websearch, or this forum has load of examples

Posted: Wed Feb 02, 2005 11:14 am
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.

Posted: Wed Feb 02, 2005 11:27 am
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?

Posted: Wed Feb 02, 2005 2:06 pm
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

Posted: Wed Feb 02, 2005 3:01 pm
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.

Posted: Wed Feb 02, 2005 7:54 pm
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.

Posted: Fri Feb 04, 2005 4:03 am
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??

Posted: Fri Feb 04, 2005 6:58 am
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??

Posted: Fri Feb 04, 2005 8:15 am
by feyd
you didn't specify how fields were terminated.

Posted: Fri Feb 04, 2005 8:29 am
by mohson
I looked at the mysql manual and it never said anything about fields - how would I go about doing this?

Posted: Fri Feb 04, 2005 8:39 am
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:

Posted: Fri Feb 04, 2005 10:03 am
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

Posted: Fri Feb 04, 2005 10:12 am
by feyd
you may want to read how MySQL documentation lays out their syntax information.

Posted: Fri Feb 04, 2005 10:14 am
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