SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    May 2011
    Posts
    191
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Loading data from Excel to MySQL

    Good day,

    I have just created a new MySQL datyabase and its tables. I have data in Excel files, and I want to load it to the database.


    Is there a way to do this?

    Thanks a lot!!!

  2. #2
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Save your excel spreadsheet as a tab delimited text file - it is an option under Save as... (NOT the commonly recommended csv file)

    then use this sort of command

    load data local infile "C:/Documents and Settings/dr john/My Documents/logpile/FlightDataExport_2011-09-02.txt" into table myflights;

    Adjust the above query as required, table by table.

  3. #3
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dr. John, why do you recommend Tab over Comma separated? I generally use comma separated and have never had a problem with it.

  4. #4
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I use tab delimited because lots of data can have a comma in it.
    eg 25, My Street, mySuburb
    Or
    caravan parking, glider rental, and trailer parking fees.
    OR
    sent one email, two letters, and even phoned him, and still not payed

    so these would mess up the insert. But the data that I insert regularly, which often has commas in the actual data, never has a tab, and I think that most other people are very unlikely to encounter a tab in their data.

  5. #5
    SitePoint Zealot
    Join Date
    May 2011
    Posts
    191
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi Dr. John,

    Thanks a lot for your help!!

    Does the Excel file have column names or just the data? (I guess in the same order as the table)
    Should I include also the autoincremental field (table Primary Key)

    Thanks!!!!

  6. #6
    SitePoint Zealot
    Join Date
    May 2011
    Posts
    191
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    And one last extra silly question:

    From where should I execute load data local infile instruction?

    Thanks!!!

  7. #7
    SitePoint Zealot
    Join Date
    May 2011
    Posts
    191
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi again,

    I was ablt to load the data as you suggested.
    The only probles I had is some information includes special characters like , and these names were cut.

    How can I fix this?

    Thanks a lot!!!!

  8. #8
    SitePoint Zealot
    Join Date
    May 2011
    Posts
    191
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ALL SOLVED!!!

    Had to save .txt file with UTF-8 format!!

    Thanks a lot!!!


Bookmarks

Posting Permissions

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