Personal Project Help

baldi903 · #1 (**permalink**) 09-10-07, 12:04

Hi all,

I am new to databases. I do know the terminology.
I am finding some confusion in trying to do something.

I have data in ms excel sheets and I want to create
a program using mysql and php that will automate
and randomize the data into an output in the php web
page.

As an example,

I have a table for "DaysofWeek" and a table for "Tasks"
I want to input all my data from these tables into mysql and
then from a page design be to use this data, how I want.

The later part, php page design, I'm not too worried about,
but I do need a starter or direction in how I can get my data
into MySQL ....

Do I create everything in MS Access and then use SQL Lion to import
everything to MySQL ?? How do I get the data from MS Excel or MS Access into the database?

Is there some better programs that can help with this?

guelphdad · #2 (**permalink**) 09-10-07, 14:45

save your data in excel as a CSV file type and import it to mysql.

baldi903 · #3 (**permalink**) 09-10-07, 15:49

Sounds too simple. I will try it and see what happens.
Thanks!

baldi903 · #4 (**permalink**) 09-10-07, 16:05

I get this error:

SQL query:

Meals, Measure, Protein, Carbs, Fat, CaloriesShredded Wheat, 50, 6, 40, 1, 180Egg Whites, 100, 10, 0, 0, 50Omega Cpas, 4, 0, 0, 4, 36Shredded Wheat, 50, 6, 40, 1, 180Talapia Cooked, 75, 18.75, 0, 2.2, 96Oatmeal, 60, 7.5, 45, 3, 225Chicken Breast, 150, 48, 0, 4.5, 252Oatmeal, 60, 7.5, 45, 3, 225Chicken Breast, 75, 24, 0, 2.2, 126Omega Caps, 4, 0, 0, 4, 36Oatmeal, 60, 7.5, 45, 3, 225Talapia Cooked, 150, 37.5, 0, 4.5, 192

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Meals,Measure,Protein,Carbs,Fat,Calories

Shredded Wheat,50,6,40,1,180
Egg Wh' at line 1

guelphdad · #5 (**permalink**) 09-10-07, 23:02

did you create your tables first?
did you use LOAD DATA INFILE to import the csv file?
you need to do both. sorry if I wasn't clear.

aschk · #6 (**permalink**) 09-11-07, 05:24

i think the problem there is to do with the format that the CSV file was saved in. You need to make sure you comma delimit and inverted comma (") encapsulate your fields. The problem has occured because you have Shredded Wheat as one field, but ideally it should be "Shredded Wheat" because MySQL is assuming the space to be a delimiter.
Thus, when you do your LOAD DATA INFILE you need to specify a FIELD delimiter and enclosing.

I have included the exert from MySQL.com
http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Code:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [FIELDS
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

Note that the part you are interested in is FIELDS TERMINATED BY ',' ENCLOSED BY '"'

baldi903 · #7 (**permalink**) 09-11-07, 07:20

Thanks! I'm using phpmyadmin to import the file.
It's a version that comes bundled with WAMP (latest version)
I havn't created the tables manually through phpmyadmin yet.
I'll try your suggestions.

aschk · #8 (**permalink**) 09-11-07, 10:14

As guelphdad suggested you're not going to be able to import anything unless you have the tables created

Build the tables first, then import your data.

baldi903 · #9 (**permalink**) 09-11-07, 10:55

I created the tables, but I don't know if I did them correctly.
For each table it says, there's no index defined.

When I import the .csv file I get

SQL query:

Meals, Measure, Protein, Carbs, Fat, CaloriesShredded Wheat, 50, 6, 40, 1, 180Egg Whites, 100, 10, 0, 0, 50Omega Cpas, 4, 0, 0, 4, 36

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Meals,Measure,Protein,Carbs,Fat,Calories
Shredded Wheat,50,6,40,1,180
Egg Whit' at line 1

guelphdad · #10 (**permalink**) 09-11-07, 13:01

so tell us exactly what you are doing to create the CSV file and exactly how you are using PHPMYADMIN to import it.

aschk · #11 (**permalink**) 09-12-07, 04:57

Have you specified the

Code:

[LINES
    [TERMINATED BY 'string']
]

part?

Looking at what you have provided its truncating all the rows into one row because you haven't defined your line separator, and you'll probably want to ignore the first line because it contains all your column headings, not actual information.

Also, have you followed my notes above to give the correct formatting to your CSV file when it's created?

Internet Services

Web Development

Digital Marketing

Consumer Tech