| 修訂 | defa92d3cbb61ea4b2d7145f0043b21ad6bc1cc3 (tree) |
|---|---|
| 時間 | 2016-08-09 13:38:24 |
| 作者 |  Dreas Nielsen <dreas.nielsen@gmai...> |
| Commiter | Dreas Nielsen |
Completed IMPORT routine for Excel.
doc/execsql.htm (diff)
execsql/execsql.py (diff)| @@ -6,7 +6,7 @@ | ||
| 6 | 6 | <meta name="description" content="Documentation for execsql.py, a scripting client for PostgreSQL, SQL Server, MS-Access, SQLite, MariaDB, MySQL, and Firebird databases." /> |
| 7 | 7 | <meta name="author" content="R. Dreas Nielsen" /> |
| 8 | 8 | <meta name="created" content="2008-04-27" /> |
| 9 | -<meta name="revised" content="2016-08-05" /> | |
| 9 | +<meta name="revised" content="2016-08-08" /> | |
| 10 | 10 | <meta name="copyright" content="Copyright (c) 2015, 2016, R. Dreas Nielsen" /> |
| 11 | 11 | <style type="text/css"> |
| 12 | 12 | html, body { |
| @@ -585,6 +585,7 @@ | ||
| 585 | 585 | and <a href="http://sourceforge.net/projects/pywin32/">pywin32</a>.</li> |
| 586 | 586 | <li>DSN data source: <a href="https://pypi.python.org/pypi/pyodbc/">pydobc</a>.</li> |
| 587 | 587 | <li><a href="http://www.opendocumentformat.org/">OpenDocument</a> spreadsheets: <a href="https://pypi.python.org/pypi/odfpy/">odfpy</a>.</li> |
| 588 | + <li>Excel spreadsheets (read only): <a href="https://pypi.python.org/pypi/xlrd">xlrd</a>.</li> | |
| 588 | 589 | </ul> |
| 589 | 590 | <p class="initial">Connections to SQLite databases are made using Python's standard library, so no |
| 590 | 591 | additional software is needed.</p> |
| @@ -2254,6 +2255,11 @@ | ||
| 2254 | 2255 | SHEET <sheet_name> |
| 2255 | 2256 | </div> |
| 2256 | 2257 | |
| 2258 | +<p class="initial">The syntax for importing data from an Excel spreadsheet is:</p> | |
| 2259 | +<div class="code">IMPORT TO [NEW|REPLACEMENT] <table_name> FROM EXCEL <file_name> | |
| 2260 | + SHEET <sheet_name> | |
| 2261 | +</div> | |
| 2262 | + | |
| 2257 | 2263 | <p class="initial"> Column names and column order in the input must exactly match those in the target table. |
| 2258 | 2264 | The column names in the input must also be valid for the DBMS in use.</p> |
| 2259 | 2265 |
| @@ -887,13 +887,26 @@ | ||
| 887 | 887 | else: |
| 888 | 888 | d = [cell for cell in cells] |
| 889 | 889 | datarow = [] |
| 890 | - for c in range(len(d)): | |
| 890 | + for c in d: | |
| 891 | 891 | if c.ctype == 0: |
| 892 | 892 | # empty |
| 893 | 893 | datarow.append(None) |
| 894 | + elif c.ctype == 1: | |
| 895 | + # This might be a timestamp with time zone that xlrd treats as a string. | |
| 896 | + try: | |
| 897 | + dt = DT_TimestampTZ()._from_data(c.value) | |
| 898 | + datarow.append(dt) | |
| 899 | + except: | |
| 900 | + datarow.append(c.value) | |
| 894 | 901 | elif c.ctype == 3 : |
| 895 | 902 | # date |
| 896 | - datarow.append(datetime.datetime(xlrd.xldate_as_tuple(c.value, self.datemode))) | |
| 903 | + dt = xlrd.xldate_as_tuple(c.value, self.datemode) | |
| 904 | + # Convert to time or datetime | |
| 905 | + if not any(dt[:3]): | |
| 906 | + # No date values | |
| 907 | + datarow.append(datetime.time(*dt[3:])) | |
| 908 | + else: | |
| 909 | + datarow.append(datetime.datetime(*dt)) | |
| 897 | 910 | elif c.ctype == 4: |
| 898 | 911 | # Boolean |
| 899 | 912 | datarow.append(bool(c.value)) |
| @@ -7406,7 +7419,7 @@ | ||
| 7406 | 7419 | try: |
| 7407 | 7420 | alldata = wbk.sheet_data(sheetname) |
| 7408 | 7421 | except: |
| 7409 | - raise ErrInfo(type="cmd", other_msg="%s is not a worksheet in %s." % (sheetname, filename)) | |
| 7422 | + raise ErrInfo(type="cmd", other_msg="Error reading worksheet %s from %s." % (sheetname, filename)) | |
| 7410 | 7423 | return alldata[0], alldata[1:] |
| 7411 | 7424 | |
| 7412 | 7425 |
Fork