School Assignment? Project Due Tomorrow? Chat LIVE With A Programming Expert!

Programming Help

Web Development



What's Here?

  • Members: 306,831
  • Replies: 841,308
  • Topics: 140,566
  • Snippets: 4,465
  • Tutorials: 1,166
  • Total Online: 1,746
  • Members: 122
  • Guests: 1,624

Welcome to Dream.In.Code
Become an Expert!

Join 306,831 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,746 people online right now. Registration is fast and FREE... Join Now!




Import CSV to MSSql

 

Import CSV to MSSql
oddvalue

26 May, 2009 - 10:55 AM
Post #1

New D.I.C Head
*

Joined: 26 May, 2009
Posts: 1
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,

CODE
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.

User is offlineProfile CardPM
+Quote Post


Robin Marsh

RE: Import CSV To MSSql

27 May, 2009 - 01:01 PM
Post #2

New D.I.C Head
*

Joined: 7 May, 2009
Posts: 13



Thanked: 1 times
My Contributions
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...

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
User is offlineProfile CardPM
+Quote Post

mikeblas

RE: Import CSV To MSSql

27 May, 2009 - 06:33 PM
Post #3

D.I.C Regular
Group Icon

Joined: 8 Feb, 2008
Posts: 390



Thanked: 27 times
My Contributions
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.
User is offlineProfile CardPM
+Quote Post

Trogdor

RE: Import CSV To MSSql

27 May, 2009 - 11:32 PM
Post #4

D.I.C Addict
Group Icon

Joined: 6 Oct, 2006
Posts: 619



Thanked: 15 times
Dream Kudos: 200
My Contributions
i think that dts stuff has been around at least since 2000.
User is offlineProfile CardPM
+Quote Post

mikeblas

RE: Import CSV To MSSql

28 May, 2009 - 05:10 AM
Post #5

D.I.C Regular
Group Icon

Joined: 8 Feb, 2008
Posts: 390



Thanked: 27 times
My Contributions
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
User is offlineProfile CardPM
+Quote Post

kzimmerm

RE: Import CSV To MSSql

1 Jun, 2009 - 10:46 AM
Post #6

D.I.C Head
**

Joined: 8 Feb, 2009
Posts: 67



Thanked: 3 times
My Contributions
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: 1 Jun, 2009 - 10:48 AM
User is offlineProfile CardPM
+Quote Post

Fast ReplyReply to this topicStart new topic

Time is now: 11/20/09 10:46PM
Invision Power Board v2.1.7 © 2009  IPS, Inc.
Licensed to: MediaGroup1

Live Help!

Be Social

Dream.In.Code RSS Feed Dream.In.Code LinkedIn Group Follow Us On Twitter Fan Us On Facebook

Tutorials

Programming

Web Development

Reference Sheets

Code Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month