Export SQL file into Excel

Question

Hi guys,

I have a sql file which has a lot of insert statements (1000+),

e.g.

insert into `pubs_for_client` (`ID`, `num`, `pub_name`, `pub_address`, `pub_tele`, `pub_fax`, `pub_email`, `publ_website`, `publ_vat`, `pub_last_year`, `titles_on_backlist`, `Personnel`) values('2475','2473','xxx xxx xxx','xxx xxx, xxxx, xxxx, xxxx, ','000000 ','0000 ',NULL,NULL,NULL,NULL,NULL,NULL);

My client wants these in an excel file.

Is it possible to extract the information from this sql file and import it into an excel spreadsheet? I'm using excel 2007.

Answer 1

If you have a mysql installation somewhere and phpMyAdmin, just import the .sql file and export the table as .xls file directly from within phpMyAdmin.

Answer 2

You mean export the values?

Assuming you're using the SQL server + tools the simplest method would be to

1. actually insert those values into a table (creating a temp table if necessary)
2. select * from pubs_for_client
3. copy + paste the results table into Excel.

You'll have to set the column widths yourself, and if you have number-as-a-string columns you want to keep as strings (e.g. to preserve leading zeroes) then you'll need to set the receiving column type first.

If not, and if you can't use similar tools for your database, you can use regexps or just use your text editor to convert your SQL file into a CSV which Excel will read. You'd also have to edit out quotes from values and nulls and doubled-to-quote apostrophes. Unfortunately you can't then specify column types in advance and you would e.g. lose leading zeros on numbers.

Answer 3

edit: If you want to extract the data from the Insert statements without actually running them, you could:

• Save the SQL file as a CSV file
• Open it in Excel and it will automatically split the statements up into bits using the commas
• Most of the columns to the left of the data wont be worth keeping, so delete them
• The right-most columns should contain the data

Answer 4

If you are not keen on installing any tools you could try this...its simple but needs some regex matching with trial and error for strings.

1)copy over the column names to excel

2)Open the file with the insert statements and hit replace all

Insert into pubs_for_client (ID, num, pub_name, pub_address, pub_tele, pub_fax, pub_email, publ_website, publ_vat, pub_last_year, titles_on_backlist, Personnel) values(

with nothing.

3) Find )\n with wild card and remove all of them.

4)now this text file is ready to be imported to excel...you can copy it over to clipboard and then to excel...or open with excel and use.

As a side note...you could also try asking your client to send it in excel henceforth...its usually available in most data providers.