Opening a connection to a Microsoft Excel spreadsheet (e.g. *.xls) is achieved using the Microsoft Excel driver, typically called 'Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)'. Unfortunately, this driver is available only on Windows, not Unix.
Microsoft SQL Server JDBC for macOS. This tutorial shows how to set up a data source and connect to a Microsoft ® SQL Server ® database using the Database Explorer app or the command line. This tutorial uses the Microsoft JDBC Driver 4.0 for Microsoft SQL Server to connect to a Microsoft SQL Server 2016 Express database. This article explains how to connect to Microsoft SQL Server using unixODBC and FreeTDS on Linux and the Microsoft ODBC driver on the Mac. It includes installation and configuration instructions for Ubuntu, CentOS and the Mac. It covers the following topics: How to connect to Microsoft SQL Server (with code samples). How to setup ODBC.
The easiest way to check if the driver is available to your Python environment (on Windows) is to use
The resulting list will show any matching drivers, e.g.,
You can also check for the driver on your PC by navigating to Control Panel -> Administrative Tools -> Data Sources (ODBC), and then click on the 'Drivers' tab. The Excel driver will be listed there, if it is installed. It may have a slightly different name. Note also that there are separate 64-bit and 32-bit versions of the ODBC Administrator utility (odbcad32.exe).
The Microsoft Excel driver does not support transactions, so you must set
autocommit
to True
on the connection or else you will get an error, e.g.:Excel defaults to a read-only connection, so if you want to update the spreadsheet include
ReadOnly=0
in your connection string:Be careful of the data types in your Excel spreadsheet. The Excel driver uses the most common data type from the first 8 rows of the spreadsheet to determine the data type of each column. So if you have 5 numbers and 3 text values in the first rows of a column, the column will be considered numeric, and the 3 text values will be returned as NULL!
Also, the driver may treat the first row of the worksheet as the column names, rather than data, so be aware of this.
Overall though, it has to be said, Excel is not best suited for being accessed with an ODBC connection. You may want to consider using some other Python module instead of pyodbc, for example xlrd.
-->The bcp utility is available in the Microsoft ODBC Driver for SQL Server on Linux and macOS. This page documents the differences from the Windows version of
bcp
.- The field terminator is a tab ('t').
- The line terminator is a newline ('n').
- Character mode is the preferred format for
bcp
format files and data files that do not contain extended characters.
Note
A backslash ' on a command-line argument must either be quoted or escaped. For example, to specify a newline as a custom row terminator, you must use one of the following mechanisms:
- -rn
- -r'n'
- -r'n'
The following is a sample command invocation of
bcp
to copy table rows to a text file:Available Options
In the current release, the following syntax and options are available:
[database.]schema.tableindata_file | outdata_file
- -a packet_size
Specifies the number of bytes, per network packet, sent to and from the server. - -b batch_size
Specifies the number of rows per batch of imported data. - -c
Uses a character data type. - -d database_name
Specifies the database to connect to. - -D
Causes the value passed to thebcp
-S option to be interpreted as a data source name (DSN). For more information, see 'DSN Support in sqlcmd and bcp' in Connecting with sqlcmd. - -e error_fileSpecifies the full path of an error file used to store any rows that the
bcp
utility cannot transfer from the file to the database. - -E
Uses an identity value or values in the imported data file for the identity column. - -f format_file
Specifies the full path of a format file. - -F first_row
Specifies the number of the first row to export from a table or import from a data file. - -k
Specifies that empty columns should retain a null value during the operation, rather than have any default values for the columns inserted. - -l
Specifies a login timeout. The -l option specifies the number of seconds before a login to SQL Server times out when you try to connect to a server. The default login timeout is 15 seconds. The login timeout must be a number between 0 and 65534. If the value supplied is not numeric or does not fall into that range,bcp
generates an error message. A value of 0 specifies an infinite timeout. - -L last_row
Specifies the number of the last row to export from a table or import from a data file. - -m max_errors
Specifies the maximum number of syntax errors that can occur before thebcp
operation is cancelled. - -n
Uses the native (database) data types of the data to perform the bulk-copy operation. - -P password
Specifies the password for the login ID. - -q
Executes the SET QUOTED_IDENTIFIERS ON statement in the connection between thebcp
utility and an instance of SQL Server. - -r row_terminator
Specifies the row terminator. - -R
Specifies that currency, date, and time data is bulk copied into SQL Server using the regional format defined for the locale setting of the client computer. - -S server
Specifies the name of the SQL Server instance to connect to, or if -D is used, a DSN. - -t field_terminator
Specifies the field terminator. - -T
Specifies that thebcp
utility connect to SQL Server with a trusted connection (integrated security). - -U login_id
Specifies the login ID used to connect to SQL Server. - -v
Reports thebcp
utility version number and copyright. - -w
Uses Unicode characters to perform the bulk copy operation.
In this release, Latin-1 and UTF-16 characters are supported.
Unavailable Options
In the current release, the following syntax and options are not available:
Connecting To Hw Beltline 01
Connecting To Perf Service
- -C
Specifies the code page of the data in the data file. - -h hint
Specifies the hint or hints used during a bulk import of data into a table or view. - -i input_file
Specifies the name of a response file. - -N
Uses the native (database) data types of the data for noncharacter data, and Unicode characters for character data. - -o output_file
Specifies the name of a file that receives output redirected from the command prompt. - -V (80 | 90 | 100)
Uses data types from an earlier version of SQL Server. - -x
Used with the format and -f format_file options, generates an XML-based format file instead of the default non-XML format file.