Create scripts from Excel

  • Thread starter Thread starter Sam Commar
  • Start date Start date
S

Sam Commar

I have an excel table which has old and new values for Vendors

So my excel file looks like this- It actually has about 5000+ values:

Old Vendor ID New Vendor ID
ADPINC00 ADPEAS00'
THC TYCHEA
UNHOSE UNIHOS
USS UNISTA


I have to run SQL scripts on a database which will update the value of the
Old Vendor ID with the new Vendor ID
on 5 tables and delete the old vendor ID from one table =Actual e.g below:

update apdoc set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update aptran set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update aphist set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update apadjust set vendid = 'ADPINC00' where vendid = 'ADPEAS00';
update gltran set Id = 'ADPINC00' where Id = 'ADPEAS00' and module = 'GL';
delete from aphist where vendid = 'ADPEAS00'


I would be really grateful if someone could suggest a mechanism to easily
create the scripts as above for the Vendor IDs in my Excel file.

Thank you

S Commar
 
It would help if you did a manual query of the database in excel while
recording a macro. This would give the basic templet for making the SQL
changes you need.

do the following from Excel worksheet menu
1) Tools - Macro - Record Macro
2) Data - Import External Data - New Database Query
3) Fetch data from the tables you need to modify
4) tools Macro - Stop Recording
5) Alt-F11 (get to VBA window) and get macro from Module Window. Then
modify the code yourself or post the code for others to modify.
 
Just create formulas on the sheet which give you the SQL
Eg.

="update apdoc set vendid = '" & A2 & "' where vendid = '" & B2 & "';"

Fill down and copy-paste into a SQL command window.

Tim
 
Back
Top