For the import, we will create a separate comma delimited file for each Tab in the spreadsheet. To make this simpler, follow these steps:

1. If you are using a Google Spreadsheet, export it to Excel.

1. Open the spreadsheet in Excel

1. Make sure the Spreadsheet is not in ReadOnly or Restriced mode.

1. Press Alt + F11 keys simultaneously to open the Microsoft Visual Basic Application window.

1. In the Microsoft Visual Basic Application window, click Insert > Module.

1. Copy and paste the following code routines into the Module window.

Sub ExportSheetsToCSV()

Dim xWs As Worksheet

Dim xPath As String

xPath = “”

Dim xFile As String

For Each xWs In ThisWorkbook.Worksheets

xWs.Copy

xFile = xPath & “\” & xWs.Name & “.csv”

Application.ActiveWorkbook.SaveAs Filename:=xFile, FileFormat:=xlCSV, CreateBackup:=False

Application.ActiveWorkbook.Saved = True

Application.ActiveWorkbook.Close

Next

End Sub

For some reason, this VBA does not always work. If you get an error message, you can use this version

Sub ExportSheetsToCSV()

Dim xPath As String

Dim xFile As String

xPath = “C:\Users\scott\Desktop\Customer Data\Tabs” 'Application.ActiveWorkbook.Path

For Each xWs In ThisWorkbook.Worksheets

xFile = xPath & “\” & xWs.Name & “.csv”

xWs.SaveAs Filename:=xFile, FileFormat:=xlCSV, CreateBackup:=False

Next

Application.ActiveWorkbook.Close

End Sub

1. Set the directory you want the Tab delimited files created in the xPath variable. I recommend you create a sub-directory called Tabs for these files

1. Press the F5 key to run the code.

1. The screen will blink a bunch. Don't worry, this is just old 1993 code so it's not the prettiest.

1. You will see all exported CSV files in the specified folder.

Now that you have the CSV files, you need to get them into the Database. This routine brings in all of the CSV files into their own tables called PartImport.{filename}.

You'll have to add a lot of stored procedures and functions to the database for this to run. See Scott or a consultant for the latest versions.

In order to retrieve the list of files, SQL must make a directory call to read the contents of the folder. By default, this access is denied in SQL since it could be used by malicious software to access the local drive.

To enable these features, run these SQL commands

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE;

GO

sp_configure 'xp_cmdshell', 1;

GO

RECONFIGURE;

sp_configure 'Ad Hoc Distributed Queries', 1;

GO

RECONFIGURE;

You also need to download and install Access Database Engine(AccessDatabaseEngine_X64.exe). You can get the file using the link below.

https://www.microsoft.com/en-us/download/details.aspx?id=39358.

To import the files as parts involves these steps:

1. Add the following procedures and functions to SQL if they don't exist (they probably won't)

1. csp_ImportPartSpreadsheetTabs

1. csp_ImportPart

1. csp_ImportPartCategory

1. csf_MergeXML

1. csf_String_Split

1. csf_TextToBit

1. csf_TextToDecimal

1. csf_TextToUnitID

1. csf_TextToUnitType

1. csp_BulkFilesToTables

1. BACKUP AND RESTORE A TEST DATABASE. While I show SQLs below to delete the import, it will delete a lot of other things to and you should only plan to import on the live database after you have successfully tested everything on the backup.

1. Run the following SQL command to execute the import. (You'll need to fill in the actual path first)

DECLARE @Path VARCHAR(255) = '{fill in}'

, @SkipRows VARCHAR(255) = 3

;

EXEC csp_ImportPartSpreadsheetTabs

@Path = @Path

, @SkipRows = 3

, @DontCommit = 0 – 1 to test only but not save, 0 to save

1. Note that the import produces a lot of single row table outputs (one for each part added).

1. You MUST click on the Messages tab in SQL Management Studio and scroll down the list to see any errors. Most errors will not appear on the table output and you will only know if there are problems if you scroll through that list looking for red output.

1. If the UDF names are not correct, there is no warning. You'll have to check the parts to find that out.

If you made a mistake and want to re-import, you can delete all of the parts that were imported that day with this query, fix the data, and then run it again. This will delete anything done that day, whether by you or a user so only run this on a backup of the data.

DELETE from Part

where ModifiedbyUser = 'csp_ImportPartSpreadsheet'

DELETE from PricingElement

where ModifiedbyUser = 'csp_ImportPartSpreadsheet'

DELETE from PartInventoryConversion

where ModifiedbyUser = 'csp_ImportPartSpreadsheet'

DELETE from Inventory

where ModifiedbyUser = 'csp_ImportPartSpreadsheet'

DELETE from InventoryLog

where ModifiedbyUser = 'csp_ImportPartSpreadsheet'

Not all areas are currently implemented. These include:

• Default Units. All units must be specified on every column. A quick copy-and-paste is usually all that is needed for this. Any units not specified are treated as Each.
• Setting products Inactive with the Active = No value.

Other Notes:

• The routine does not delete the import files when it is done (though it will overwrite them if you re-import). You may want to clean them up when you are done.