Middlebury

Difference between revisions of "Excel To SQL"

Line 5: Line 5:
 
# Replace tab-delimited with SQL VALUES formatted lines:
 
# Replace tab-delimited with SQL VALUES formatted lines:
 
#* Find: <code>(.*)\t(.*)\t(.*)\t(.*)\t?(.*)?\t?(.*)?\t?(.*)?</code>
 
#* Find: <code>(.*)\t(.*)\t(.*)\t(.*)\t?(.*)?\t?(.*)?\t?(.*)?</code>
#* Replace: ('masters', '\1', '\2', '\3', '\4', '\5', '\6', '\7')<code>
+
#* Replace: <code>('masters', '\1', '\2', '\3', '\4', '\5', '\6', '\7')<code>
# Add INSERT statement to top of file:
+
# Add INSERT statement to top of file: <pre>INSERT INTO theses  
#* <code>INSERT INTO theses <br/>( `group` , `department` , `last_name` , `first_name` , `title` , `year` , `notes` , `alternate_formats`) </code>
+
( `group` , `department` , `last_name` , `first_name` , `title` , `year` , `notes` , `alternate_formats`) </pre>

Revision as of 12:09, 5 October 2007

  1. Copy from Excel, pasted into text editor
  2. Escape single quotes:
    • Find: '
    • Replace: \\'
  3. Replace tab-delimited with SQL VALUES formatted lines:
    • Find: (.*)\t(.*)\t(.*)\t(.*)\t?(.*)?\t?(.*)?\t?(.*)?
    • Replace: ('masters', '\1', '\2', '\3', '\4', '\5', '\6', '\7')
  4. Add INSERT statement to top of file:
    INSERT INTO theses

( `group` , `department` , `last_name` , `first_name` , `title` , `year` , `notes` , `alternate_formats`)