Created
July 19, 2012 12:42
-
-
Save davidrenne/3143568 to your computer and use it in GitHub Desktop.
Import Excel to Mysql using autoit.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
;copy the top header columns. save as C:\import.csv and it will build the needed SQL to import | |
While 1 | |
$nMsg = GUIGetMsg() | |
Switch $nMsg | |
Case $cTooltipClick | |
$clipBoardContents = ClipGet() | |
$array1 = StringExplode($clipBoardContents, @TAB, 0) | |
$cols = "" | |
$tableDef = "DROP TABLE IF EXISTS `development`.`import_tmp`; " & @CRLF & @CRLF & "CREATE TABLE `development`.`import_tmp` (" | |
For $i = 0 to UBound($array1)-1 | |
If $i > 0 Then | |
$comma = "," | |
If ($i = (UBound($array1)-1)) Then | |
$comma = "" | |
EndIf | |
$escCol = "`" & StringStripWS ($array1[$i],3) & "`" | |
$tableDef = $tableDef & @CRLF & @TAB & $escCol & " mediumtext NOT NULL" & $comma | |
$cols = $cols & $escCol & $comma | |
EndIf | |
Next | |
$tableDef = $tableDef & @CRLF & ") ENGINE=InnoDB DEFAULT CHARSET=latin1;" & @CRLF & @CRLF & @CRLF | |
$tableDef = $tableDef & @CRLF & "ALTER TABLE `development`.`import_tmp` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;" & @CRLF & @CRLF & @CRLF | |
$tableDef = $tableDef & "LOAD DATA LOCAL INFILE 'C:\\import.csv' INTO TABLE development.import_tmp FIELDS TERMINATED BY ',' ENCLOSED BY '" & '' & "' LINES TERMINATED BY '\r\n' (" & $cols & ");" | |
$tableDef = $tableDef & @CRLF & "DELETE FROM `development`.`import_tmp` WHERE `id` = 1;" & @CRLF & @CRLF & @CRLF | |
ClipPut($tableDef) | |
MsgBox("","Copied To Clip",$tableDef) | |
EndSwitch | |
$title = WinGetTitle("[active]") | |
if StringInStr($title,"Microsoft Excel") And _IsPressed(11) And _IsPressed(43) Then | |
Send("{CTRLDOWN}c{CTRLUP}") | |
GUISetState(@SW_SHOW) | |
TrayTip("Clear", "", 1) | |
TrayTip("Create MYSQL Table Definiton?", "Create MYSQL Table Definiton?", 5, 1) | |
EndIf | |
WEnd | |
Func StringExplode($sString, $sDelimiter, $sLimit = 0) | |
If $sLimit > 0 Then | |
;Replace delimiter with NULL character using given limit | |
$sString = StringReplace($sString,$sDelimiter,Chr(0),$sLimit) | |
;Split on NULL character, this will leave the remainder in the last element | |
$sDelimiter = Chr(0) | |
ElseIf $sLimit < 0 Then | |
;Find delimiter occurence from right-to-left | |
$iIndex = StringInStr($sString,$sDelimiter,0,$sLimit) | |
If $iIndex Then | |
;Split on left side of string only | |
$sString = StringLeft($sString,$iIndex-1) | |
EndIf | |
EndIf | |
Return StringSplit($sString,$sDelimiter,1) | |
EndFunc |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment