Geek Answers Handbook

Export SQL file to Excel

I have a sql file in which there are many insert (1000+) statements,

eg.

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 them in an excel file.

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

+7
sql import export excel-2007
source share
4 answers

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

+10
source share

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

  • Save SQL file as a CSV file
  • Open it in Excel and it automatically breaks the statements into bits using commas
  • Most columns to the left of the data should not be stored, so delete them.
  • A variety of columns should contain data
+5
source share

Do you want to export values?

Assuming you are using SQL Server + tools, the easiest method would be

  • really insert these values ​​into the table (creating a temporary table if necessary)
  • select * from pubs_for_client
  • copy + paste the table of results in Excel.

You will need to set the width of the columns yourself, and if you have number-as-row columns that you want to save as rows (for example, to keep leading zeros), you will need to set the type of the receive column first.

If not, and if you cannot use similar tools for your database, you can use regular expressions or just use a text editor to convert your SQL file to CSV, which Excel will read. You will also have to edit quotation marks from values ​​and zeros and apostrophes with a double number. Unfortunately, you cannot specify column types in advance, and you, for example. lose leading zeros on numbers.

+2
source share

If you are not interested in installing any tools, you can try this ... simply, but you need some regular expression matching with trial versions and string errors.

  • Copy column names in excel
  • Open the file with the insert instructions and click "Replace All." Insert values ​​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 ​​(without anything).
  • Find) \ n with a wild card and delete all of them.
  • This text file is now ready for import into excel. You can copy it to the clipboard and then succeed or open with excel and use.

Side note: you can also ask your client to send it to Excel from now on, as a rule, it is available to most data providers.

+2
source share

More articles:


All Articles