Middlebury

Difference between revisions of "Excel To SQL"

m
 
(4 intermediate revisions by one other user not shown)
Line 4: Line 4:
 
#* Replace: <code>\\'</code>
 
#* Replace: <code>\\'</code>
 
# 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\r]*)\t([^\t\r]*)\t([^\t\r]*)\t([^\t\r]*)\t?([^\t\r]*)?\t?([^\t\r]*)?\t?([^\t\r]*)?</code>
#* Replace: <code>('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: <pre>INSERT INTO theses  
+
# Remove the last comma from the end of the file.
( `group` , `department` , `last_name` , `first_name` , `title` , `year` , `notes` , `alternate_formats`) </pre>
+
# Add INSERT statement to top of file:  
 +
<pre>INSERT INTO theses
 +
( `group` , `department` , `last_name` , `first_name` , `title` , `year` , `notes` , `alternate_formats`)  
 +
VALUES</pre>
 +
 
 +
[[Category:TDXNO]]

Latest revision as of 15:32, 14 November 2022

  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\r]*)\t([^\t\r]*)\t([^\t\r]*)\t([^\t\r]*)\t?([^\t\r]*)?\t?([^\t\r]*)?\t?([^\t\r]*)?
    • Replace: ('masters', '\1', '\2', '\3', '\4', '\5', '\6', '\7'),
  4. Remove the last comma from the end of the file.
  5. Add INSERT statement to top of file:
INSERT INTO theses
( `group` , `department` , `last_name` , `first_name` , `title` , `year` , `notes` , `alternate_formats`) 
VALUES
Powered by MediaWiki