Did you know that within Purchase Order Processing that the list of available ship to addresses pulled from the Company Address ID setup within GP? During a POP/Inventory/SOP implementation, it’s no uncommon for companies to have 100s of potential ship to addresses for purchases. Below we’ve covered our favorite way to get this list of address IDs into the system using .csv/.txt files and SQL.
Step 1.) Create a spreadsheet that list all address lines. Our example contains the following columns.
Company ID, Address ID, Name, Contact, Address 1, City, State, Zip Code
Step 2.) Save spreadsheet to a tab-delimited text file
Step 3.) Open the text file & replace any ” characters w/ {blanks}. ” characters will appear when a field contains a comma.
Step 4.) Import into SQL using SQL Server Management Studio
>>Right-Click Database
>>Select Tasks
>>Select Import Data
>>Select Flat File as source
>>Enter Staging Table Name in SQL Destination (Use name of CoAddressID_Template in our example below)
insert into
gpent..sy00600
(CMPANYID,LOCATNID,LOCATNNM,ADRSCODE,ADRCNTCT,ADDRESS1,ADDRESS2,ADDRESS3,CITY,COUNTY,STATE,ZIPCODE,COUNTRY,PHONE1,PHONE2,PHONE3,FAXNUMBR,CHANGEBY_I,CHANGEDATE_I,CCode)
select
@cmpanyid,left([Address
ID],15),left([Name],30),”,left([Contact],60),left([Address 1],60),”,”,left([City],25),”,left([State],30),left([ZIP Code],10),”,”,”,”,”,”,’1900-01-01 00:00:00.000′,” from coAddressid_template
That should do it. You can add additional columns to your sourcefile if you please. Just be sure to update your insert script to account for the new columns & remember character limitations! left(Address1,60)
Our first preference is to always use the Microsoft eConnect procedures or integration tools like Integration Manager when available. However, when all else fails, a carefully executed SQL script can achieve the same outcome. At the end of the day, it’s all data in a database.
Let us know if this works for you or if you’d like to see similar processes using SQL!
