good tool to quickly import some csv data into sql server

Question

Every once in a while I find myself typing a one-off script to import some CSV data into a database, doing some column logic (e.g. turn column3 into uppercase) and/or field mapping (column2 in the csv goes to column 3 in de database, etc).

Is there a nice tool which can do this easier?

The least amount of features I'd need is:

• choosing delimiter
• Being able to map column X to column Y in the DB
• setting defaults for columns not in the csv file

SQL server management studio can almost do it, except one can not specify any defaults for values which are not listed in the csv file. So I'd then have to create extra columns in excel first.

Answer 1

Sql Server Integration Services (SSIS) is the tool I typically use for such purposes. What's nice about a tool like this is that it's very fast, flexible and you can control batch sizes.

Answer 2

There are already several questions addressing this:

http://stackoverflow.com/questions/3894345/sql-server-2008-importing-data-from-excel-2003-file

http://stackoverflow.com/questions/610227/how-can-import-xlsx-file-to-sql-server-2000

http://stackoverflow.com/questions/2112691/strategy-for-storing-supplemental-data-alongside-imported-data

http://stackoverflow.com/questions/3446706/import-selected-columns-from-a-csv-files-to-sql-server-table

Did you not find what you were looking for in any of these?

Answer 3

take a look at 6 ways to import data into SQL Server for some ideas, I think SSIS would be your best bet..but there is a learning curve. Or BCP into a staging table, massage the data there and then import intio the real table

Answer 4

right click on your database, go to Task > import Data and follow the wizard to import data. It's a nice, easier way. Also look at following:

http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

Answer 5

The definitive "best" tool for importing and exporting data to and from SQL Server is, in my opinion, the BCP utility.

Do NOT be put off by the fact that there is no graphical user interface available to you. This tool exposes the widest range of functionality to the coder and also delivers the best performance of all SQL Server ETL Tools, fact.