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

DataClean Preview Version for Windows XP and Vista

08/12/08 09:51 Filed: Preview

The Preview Edition of DataClean is available for download. Versions are now available for Mac OSX, Windows XP and Vista.

DataClean is a Cross Platform Utility designed to make it straightforward to ‘clean’ large amounts of data. Data can be imported from a CSV or TAB delimited file, modified (any column, any row) and then saved back in several different formats including native Excel (XLS).

DataClean supports the following Actions: Remove / Replace / Find / Insert and Format. Within each Action DataClean provides Tasks. For example; within the Remove Action there is the Task ‘Remove Trailing Spaces’.

The Preview Version of DataClean is Framework complete, but at the moment has few Tasks. I am looking for feedback as to what you think should be available within DataClean. All suggestions are welcome.

ss_dataclean4

Steven Cholerton
Arten Science

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

Tags: Arten Science, DataClean

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

DataClean Preview Version for Windows XP and Vista