Simplifying CSV Data Loads

KKline — Thu, 27 Oct 2011 17:55:00 GMT

Data files containing comma separated values, or CSV, are some of the most common data formats used for data representation and storage outside the database. When it comes to loading CSV data into the database, many options exist, however, few make it as simple as CSVexpress, powered by expressor software. I recently visited www.csvexpress.com to check out just how simple it could get. In short, CSVexpress offers a repeatable and quick way to load any CSV file into SQL Server (or any other database). For those whose data quality is not as pristine as it should be, CSVexpress also offers a wide variety of built-in functionality to repair the data issues. These are in addition to the data transformation components available out of the box, but let’s not get ahead of ourselves.

The first thing I notice when I visit CSVexpress is that there are some video tutorials available on the main page. I found it pretty straightforward to load a CSV file into the database without watching the tutorials beforehand. However, by watching the tutorials I was able to learn more about some neat features and functions that I had not previously noticed.

For my test, I grabbed a simple CSV data file containing the following data:
City,User_ID,Name,Street_Address,Status
"Dallas",47,"Janet Fuller","445 Upland Pl.","Trial"
"Lyon",38,"Andrew Heiniger","347 College Av.","Active"
"Dallas",43,"Susanne Smith","2 Upland Pl.","Active"
"Berne",22,"Bill Ott","250 - 20th Ave.","Active"
"Boston",32,"Michael Ott","339 College Av.","Trial"
"New York",41,"Bill King","546 College Av.","Deleted"
"Oslo",45,"Janet May","396 Seventh Av.","Active"
As you can see from the diagram below, the import of the data to create a schema was not difficult at all:
Once the schema is configured, I can create the following simple data flow to move data from my CSV input file to my target table in SQL Server:

While there are other tools available for performing similar tasks, CSVexpress makes it very simple and intuitive. However, as I mentioned earlier, where it starts getting really interesting is when you need to pre-process and clean-up the data prior to loading it. Whether it involves enriching the data from external data sources or web services, or identifying and repairing bad data, CSVexpress maintains a simple interface for all of that.

The best part - it’s all free of charge. The version you can download from www.csvexpress.com is expressor’s free Community Edition. expressor also offers a licensed Desktop and Standard Edition with even more advanced features, which are available for a 30-day trial. As a matter of fact, at the end of November, expressor will be introducing Salesforce support into their commercial editions and CSVexpress will feature a 30-day trial version. You will be able to load Salesforce just as easily as if loading to SQL Server, or download your Salesforce contact, lead, and opportunity data and transform it before generating the right CSV output file (or files) that meets your daily, weekly, and monthly Excel reporting and analysis needs. Now that’s easy!

- Kev

Search results matching tags 'DBA', 'data cleansing', and 'ETL'

Simplifying CSV Data Loads