Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2007
    Posts
    5

    Unanswered: Personal Project Help

    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?

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    save your data in excel as a CSV file type and import it to mysql.

  3. #3
    Join Date
    Sep 2007
    Posts
    5
    Sounds too simple. I will try it and see what happens.
    Thanks!

  4. #4
    Join Date
    Sep 2007
    Posts
    5
    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

  5. #5
    Join Date
    Mar 2004
    Posts
    480
    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.

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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 '"'

  7. #7
    Join Date
    Sep 2007
    Posts
    5
    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.

  8. #8
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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.

  9. #9
    Join Date
    Sep 2007
    Posts
    5
    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

  10. #10
    Join Date
    Mar 2004
    Posts
    480
    so tell us exactly what you are doing to create the CSV file and exactly how you are using PHPMYADMIN to import it.

  11. #11
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •