Traditionally to import this type of data I would have created a small screen to import the data via a loop, or maybe I would have used a database utility and mapped the columns. This time I used dataclean to convert this information into a long series of SQL Update statements which where then simply run as a script.

The procedure I was followed was this:

• Open the spreadsheet using DataClean
• Select Column A, Select all the Rows
• Perform an Insert / Beginning of Column
• Type: update countries_table set shipcost =
• Select Column B
• Perform an Insert / Beginning of Column
• Type: , set max_wgt =
• ....... (Each Column)
• Select Column G
• Perform an Insert / Beginning of Column
• Type: WHERE country_code = ‘
• Perform an Insert / End of Column
• Type: ‘ ;
• Save the Document using a Space Character as the Custom Delimiter
• Run the Document as a SQL Script

It may look complicated but all you are doing is surrounding the columns with the SQL necessary to make each line a valid SQL Update statement. You end up with a text file full of statements like this:

update bas_cho_ccry set cry_1stvalue = 600 , cry_2ndvalue = 950 , cry_freevalue = 1300 where cry_code = 'AL' and cry_comp_fk_com = 61;

Very quick to do and very efficient. I love it

Steven Cholerton
Arten Science

Subscribe to our RSS Feed: http://feeds.feedburner.com/ArtenScienceRssFeed

Tags: Arten Science, DataClean, SQL