DataClean can be useful for many types of data manipulation. Recently I have been working with an ArtenSUITE customer who was supplying large quantities of data as an Excel spreadsheet to be imported into ArtenSUITE.

A simplified version of the spreadsheet looks like this:

Spreadsheet Image

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

DataClean Image

Steven Cholerton
Arten Science

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

Tags: Arten Science, DataClean, SQL

Arten Science

Productivity and CRM for Macintosh and Windows

document.write(archivesTitle)

Awesome DataClean Review on Version Tracker

Creating SQL Update Statements for Bulk Imports using DataClean