Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2012
    Posts
    7

    Unanswered: how to read sql file in excel format ?

    Hello all,

    I have an sql file, but I want to read it in the form of an excel sheet. Do you have any suggestions, how can i convert sql into excel or any such readable format ? Or are there any free tools which does the same ?

    Thanks in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what does "read it in the form of an excel sheet" mean?

    can we see the first few lines of the sql file please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I think the easiest way of reading in some type of tabular form would be to apply the SQL file into a database and then using "mysql --html" run a select statement against this. The result of this is your data in HTML table format which can be viewed with a browser. You can then even from the browser copy the table and apply it into an Excel spreadsheet.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Jan 2012
    Posts
    7
    I thank you both for your reply..

    I am totally new to databases.
    I actually have a dump sql file (filename.sql) with me, which I am trying to import it in mysql database. I have found the following command for it.

    mysql < filename.sql

    The goal is, after importing, i would be able to see the tables and I somehow want it all in the form of excel sheet or csv format. And so, as Ronan suggested, I would then try using "mysql --html" and run the select statements (i still need to try and figure out after that, how it works though).

    But for now, the above command to import the dump file shows me an error all the time. It says I need to check the syntax with every version of the mysql. I am using 5.5.19.0, and refering to its manual as well and the above command is as per that. I still dont understand why do I get errors. Or sometimes it says, "database file not found".
    Or am i missing something?

    I apologize if this seems to be a very small and silly query? But i am just stuck with it.

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    What happens if you type in mysql by itself? Do you still get the error message? If yes can you copy and paste in here so we can take a look?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  6. #6
    Join Date
    Jan 2012
    Posts
    7
    If I type only "mysql", it still gives me the same message, with the following error:

    ERROR 1064 (42000): 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 'mysql' at line 1

  7. #7
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Are you sure that your mysql server is running? If it is then it sounds like you have some SQL that is automatically executed when you run the mysql command. This is usually configured in your local copy of my.cnf.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  8. #8
    Join Date
    Jan 2012
    Posts
    7
    It is running, because I am able to run other normal commands like SHOW, CREATE, SELECT, etc. I also just checked with another laptop, with the same version of mySQL. But it shows me the same error when I type "mysql" or "mysql < filename.sql"

    Could you may be tell me how can I solve or configure this ? I would try it out then.

  9. #9
    Join Date
    May 2008
    Posts
    17
    looks like your at the mysql prompt already so try

    source filename.sql

  10. #10
    Join Date
    Jan 2012
    Posts
    7
    It works with this finally.. I thank you so much.. It was just a very long file.

    And now, just last thing i would again bother you with..
    I want to get it in a file in the form of tables or the excal sheet as I said (xls or csv), so that it gets easy to read.
    I guess i need to write select statements for each table and put one by one in a file ? And If yes, could you only tell me the command to put them all in a file.

  11. #11
    Join Date
    May 2008
    Posts
    17
    SELECT
    field1
    ,field2
    ,field3
    FROM
    table_name
    INTO OUTFILE 'out.csv'
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'


    Replacing field1, field2 etc., with the field names in your table and table_name with the name of your table.

  12. #12
    Join Date
    Jan 2012
    Posts
    7
    Thanks a lot again.. i get it now..

  13. #13
    Join Date
    Jan 2012
    Posts
    7
    I thought I would figure out this thing by myself and also tried different commands for it. But again I think it would be best to ask you, when I couldn't find.

    The command which you gave surely works for every individual table. But I have a list of around 90 tables in my database. Is there a way to extract all of them at once in the csv file ?

Posting Permissions

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