THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

Simplifying CSV Data Loads

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

Published Thursday, October 27, 2011 1:55 PM by KKline

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

DWBIArch said:

Here's a working URL for CSVexpress

http://csvexpress.expressor-software.com/

The expressor full data integration platform is presented here:

http://www.expressor-software.com/products-editions.htm

We are actively using their server hosted product on a cancer research data warehousing project and couldn't be more impressed with thier latest version 3.4.2

October 29, 2011 4:58 PM

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement