CODEWITHSUNDEEP
sqlimportexportexcel-2007
109221793
109221793

Reputation: 16897

Export SQL file into Excel

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.

Upvotes: 7

Views: 68073

Answers (4)

Whimsical
Whimsical

Reputation: 6355

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.

Side Note : you could also try asking your client to send it in excel henceforth its usually available in most data providers.

Upvotes: 3

codeulike
codeulike

Reputation: 23064

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

Upvotes: 5

Rup
Rup

Reputation: 34418

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.

Upvotes: 2

cweiske
cweiske

Reputation: 31098

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.

Upvotes: 12

Related Questions