Summary

This documentation refers to the “ExcelToSQL” program located in M:\GPO Info\Sales Rosters GPO\COMBINE ROSTERS.

This program can be used to automatically export an Excel spreadsheet to SQL. The directory of the ExcelToSQL application must contain the Excel file (.xls/.xlsx) that you want to convert.

It’s recommended to use this program with the GPO sheets.

NOTE: Only the FIRST sheet in the file will be exported.

Before Using the Program

To use this program, you must have working knowledge of SQL Server Management Studio (SSMS). You will need to be able to do the following:

• Identify the target server host (Ex: PRD-SQL-07)
• Identify the target database (Ex: fin_test)
• Be aware of the password associated with the “sa” username for SQL
• Use Remote Desktop Connection to control a server device
• Create a target table in SSMS (If it doesn’t exist already)

If you can’t do these things yourself, contact someone to use the program for you.

1. Use Remote Desktop Connection to access the target server host’s device
2. Open SSMS via Windows Key > Microsoft SQL Server Tools > Microsoft SQL Server Management Studio
3. Locate the database you intend to add to and expand it by clicking the + next to its name
4. Expand the “Tables” section with the + next to its name
5. Right click “Tables” > New > Table… and create your table
   1. Alternatively, create a new query and create it there

Example:

USE (database name);

GO

CREATE TABLE dbo.(table name) (

[index] BIGINT NULL,

[Name] VARCHAR(MAX) NULL,

[Street Address] VARCHAR(MAX) NULL,

[City] VARCHAR(MAX) NULL,

[State] VARCHAR(MAX) NULL,

[Zip Code] VARCHAR(MAX) NULL,

[Source] VARCHAR(MAX) NULL,

[Local ID] VARCHAR(MAX) NULL );

NOTE: The table MUST be created beforehand in SSMS as the program doesn’t have server permissions to create new tables.

Using the Program

With your table prepared, you can then open the program.

1. Enter the name of your file that you want to export to SQL and make sure to include the “.xls” or “.xlsx” at the end
2. The program will search for your file and read it back to you if it’s found
3. Using the credentials associated with the username “sa”, confirm the username and password
4. Enter the host name and database
   1. For GPO Reports, use PRD-SQL-08 and Prd_DW_01
5. Enter the EXACT name of the table you created
   1. For GPO Reports, use SCC_GPO_Merged_Roster
6. The program should then process the transfer of your Excel data to SQL

When the process is completed, the program will prompt you to close it.

Note (For GPOs)

When the GPO reports get updated and need to be imported to SQL, someone will need to clear the existing table so duplicate entries aren’t sent through. This can be done with the following query:

TRUNCATE TABLE SCC_GPO_Merged_Roster

After importing the new GPO Roster, some values might need to be cleaned up (empty rows, spaces in the start of names, etc.) depending on how the source files were put together.