Import CSV To MSSql

Import CSV to MSSql Rate Topic:

#1 oddvalue

New D.I.C Head

Group: New Members
Posts: 1
Joined: 26-May 09

Dream Kudos: 0

Post icon Posted 26 May 2009 - 10:55 AM

One of my clients has lost an entire mailing list database and obviously wants to get it back. They have CSV backups of the mailing list table and have asked me to merge the fresh emails in the list with the backups.
The table only has two fields, id and email, so this should be possible.

I don't personally host the database and I use SQL Server Management Studio Express 2005 to view and edit it. After some extensive research the only thing I could come up with was some info on the bcp utility which I cannot get to work.

I have tried many variations of code but to give an example,

bcp [database].dbo.[table] in emails.csv -T -f

Any information as to how I should be using the bcp utility or any other methods of importing CSV files to MSSql databases would be greatly appreciated.

Thanks.

Was This Post Helpful? 0

Register for Free to Make These Ads Go Away!

#2 Robin Marsh

New D.I.C Head

Group: Members
Posts: 13
Joined: 07-May 09

Dream Kudos: 0

Posted 27 May 2009 - 01:01 PM

BCP is not always the best method, sometimes you will need alternative methods.

If you save the csv as a full spreadsheet (xls) you can create the insert commands in the xls file and then copy into management studio and run.

Example using two columns:
1. Save your csv file as an xls file.
2. Using a blank column, add the following code...

="INSERT INTO <table>(<column1>,<column2>)VALUES('"&A1&"','"&B1&"')"

3. You will need to insert your own table, columns and cell references into the query.
4. Copy the query down and it will self populate(fill) with the correct data.
5. You will notice that the cell references have changed for the correct data.
6. You can then copy the queries into a SQL window and run them against the correct database.

This post has been edited by Robin Marsh: 27 May 2009 - 01:07 PM

Was This Post Helpful? 0

#3 mikeblas

D.I.C Regular

Group: Expert w/DIC++
Posts: 390
Joined: 08-February 08

Windows Favorite Browser Favorite Processor Favorite Gaming Platform Your Car
Dream Kudos: 0

Posted 27 May 2009 - 06:33 PM

The easiest way to do this is with SQL Server Integration Services, which is included with SQL Server 2005 and newer. You can run the "dtswizard", which will guide you through selecting and describing the source data, then selecting and describing the target.

Was This Post Helpful? 0

#4 Trogdor

D.I.C Addict

Group: Contributors
Posts: 619
Joined: 06-October 06

Mac Favorite Browser Favorite Processor Favorite Gaming Platform Your Car
Dream Kudos: 200

Posted 27 May 2009 - 11:32 PM

i think that dts stuff has been around at least since 2000.

Was This Post Helpful? 0

#5 mikeblas

D.I.C Regular

Group: Expert w/DIC++
Posts: 390
Joined: 08-February 08

Windows Favorite Browser Favorite Processor Favorite Gaming Platform Your Car
Dream Kudos: 0

Posted 28 May 2009 - 05:10 AM

DTS was in SQL Server 7 and SQL Server 2005. It was replaced by SQL Server Integration Services in SQL Server 2005.

If oddvalue is using SQL Server Express, though, I'm not sure he has these tools. They're most readily obtainable by getting SQL Server Developer Edition.

I've done Robin's Excel trick myself, and it's viable if there's less than a few thousand rows or no goofy data formats; and when you don't need to do the import repeatedly. At those points, it's got problems.

This post has been edited by mikeblas: 28 May 2009 - 05:11 AM

Was This Post Helpful? 0

#6 kzimmerm

D.I.C Head

Group: Members
Posts: 67
Joined: 08-February 09

Windows Favorite Browser Favorite Processor Favorite Gaming Platform Your Car
Dream Kudos: 0

Posted 01 June 2009 - 10:46 AM

There is an "Import Wizard" that allows you to create, on the fly, an SSIS package which will allow you to import the entire excel spreadsheet into the database. This works and I use it a lot.

A .CSV can be use by BCP to import the data. BCP has been around for a very long time and there is a lot of information you can find by simply using Google.

Just an FYI, what can drive you crazy is if you have commas in your data which is very common in address information.

Good luck.
Kurt

This post has been edited by kzimmerm: 01 June 2009 - 10:48 AM

Was This Post Helpful? 0