Return the external data to the active sheetĬlick Existing sheet. In the Returning External Data to Microsoft Excel dialog box, do any of the following:Ĭhoose query definition, refresh control, and data layout optionsĬhange the settings for a parameter query When you have finished, click Return Data to import the data into Excel.įor more information about connecting to a data source and using Microsoft Query, see Microsoft Query Help. Use Microsoft Query to connect to a data source and to create a query. On the Data menu, point to Get External Data, and then click New Database Query. For more information about installing ODBC drivers, see Microsoft Query Help.
ACCESSING MICROSOFT SQL SERVER ON MAC DRIVERS
For more information, see ODBC drivers that are compatible with Excel for Mac. Compatible ODBC drivers are available from third-party vendors.
ACCESSING MICROSOFT SQL SERVER ON MAC DRIVER
To import data from a database, such as Microsoft SQL Server, you must have an ODBC driver that is compatible with Microsoft Query installed on your computer. To ensure the connection is working, enter some data, and then select Data > Refresh All. After you import the data, you can also select Data > Connections, and then in the Connection Properties dialog box, select Properties. To set connection properties on the Usage and Definition tabs of the Properties dialog box, select Properties. When you're ready, select Return Data. The Import Data dialog box appears. You can change the SQL statement as you see fit.
In the left pane, navigate to the table you want, and then select it.Ĭonfirm the SQL statement on the right pane. Under Authentication, select a method from the list: Username/Password, Kerberos, or NTLM.Įnter credentials in the User Name and Password boxes. Get this information from the database administrator. Select Data > From SQL Server ODBC. The Connect to SQL Server ODBC Data Source dialog box appears.Įnter the server in the Server Name box, and optionally, enter the database in the Database Name box. To ensure the connection is working, enter some data, and then select Connections > Refresh. The Import Data dialog box appears.Ĭhoose where you want the data to be added: either on the existing sheet, on a new sheet, or in a PivotTable.
You can also convert the data after you import it. You can further set the date format and select Advanced to change numeric data settings. If you chose Fixed width on the first page, follow the instructions to create, delete, or move a break line in the Preview of selected data box.įor each column under Preview of selected data, select it, and then change it to a different column format if you want. In Text qualifier, select the character that encloses values in your text file, which is most often the quote (") character. Select Treat consecutive delimiters as one if your data contains a delimiter of more than one character between data fields or if your data contains multiple custom delimiters. If you chose Delimited on the first page, under Delimiters, select the delimiter character or use the Other check box to enter one not listed. In most cases, you can leave this setting at its default. Row Number In Start import at row, select a row number to specify the first row of data that you want to import.Ĭharacter Set In File origin, select the character set that is used in the text file.
Tip Repeatedly check the Preview of selected data pane to confirm your choices.įile Type To choose the type of text file, select Delimited or Fixed width. If the Grant File Access dialog box appears, select Select, and then select Grant Access to the top-level folder containing the data source files.
If you get a security warning about external data connections being disabled, select Enable Content.
0 kommentar(er)
