Bulking Up

The problem

A common problem I’ve run across is that people will have these elaborate spreadsheets in Excel that contain all of their data that they need to run things. Excel is the poor man’s database, but people prefer its interface over the challenge of having to learn some SQL. (Let’s ignore the fact that learning to manipulate Excel can take just as long if not longer then learning some SQL). I’ve spent some time thinking about various ways that I could import spreadsheet data into a DB and show people how easy it is to switch and do basic manipulation to get them interested. Although I have yet to figure out how to import calculations and elaborate cross-spreadsheet connections, I have figure out import a lot of basic Excel data into a SQL server.

If you can get the data into an approximation of a table structure, (e.g get it into a standard set of columns and a bunch of rows) then the data can be saved as a CSV (A standard format to save in in any spreadsheet application I’ve seen) and it can be directly imported into a table on MS SQL using bulk insert. Bulk insert is command that will, among other things, directly import data from a file into a table.

Fixing a problem where the rain gets in

Here’s a test I ran:


create table inputTest
(
ID int,
FName varchar(2000),
LName varchar(2000),
age int
)

BULK INSERT dbo.inputTest
from 'C:\someFileName.csv'
WITH
(
FIELDTERMINATOR =',',
ROWTERMINATOR ='\n'
);

select * from inputTest

Where ‘someFileName.csv’ contains the data:


64,Joe,Blow,23
78,Alica,Aba,35

With the result being:

ID FName LName age
64 Joe Blow 23
78 Alica Aba 35

This is a very simple example but it shows the basic format and how to easily import a bunch of data from a file. The command has a whole host of other features, including the ability to specify an error file to throw bad rows that can’t be parsed into. Seems like the first step in turning a spreadsheet nightmare into something useful.

Leave a Reply