eCharcha.Com   Support eCharcha.Com. Click on sponsor ad to shop online!

Advertise Here

Go Back   eCharcha.Com > Science and Technology > How to

Notices

How to Stuck for an answer to "How to do .."?

Reply
 
Thread Tools Display Modes
  #1  
Old August 21st, 2006, 08:41 AM
ShivSainik's Avatar
ShivSainik ShivSainik is offline
Senior eCharchan
 
Join Date: Apr 2001
Location: San Jose, CA
Posts: 5,253
ShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond repute
Load data in SQL server from CSV

I am absolutely new to SQL server nad I have this quick requirement of loading some CSV files (generated from 2 different data sources) into SQL server and firing some queries on loaded data to find data discrepancy.

Could somebody tell me how to bulk load CSV formatted data into SQL server ? (I want table definitions to be created dynamically if possible)

Thanks in advance.
__________________
"Once you start thinking too much about your place in history, you're at a point where you're no longer trying as hard as you should be"
Reply With Quote
  #2  
Old August 21st, 2006, 08:58 AM
krantikari's Avatar
krantikari krantikari is offline
Senior eCharchan
 
Join Date: Jul 2000
Posts: 4,617
krantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond repute
Re: Load data in SQL server from CSV

Quote:
Originally Posted by ShivSainik
I am absolutely new to SQL server nad I have this quick requirement of loading some CSV files (generated from 2 different data sources) into SQL server and firing some queries on loaded data to find data discrepancy.

Could somebody tell me how to bulk load CSV formatted data into SQL server ? (I want table definitions to be created dynamically if possible)

Thanks in advance.

Its been Oracle all the way for me. Never tried SQL server. I have tried MySQL though sometime.

For some of the data migrations, I have used MS Excel to generate scripts. I found they are better then SQL Loader or using the GUI for DBs.

Steps to follow:
1. Open your file in Excel so that each element occupies one cell.
2. Insert formula (usde text concatinate) in the cell next to last element in the first row of the excel sheet. (Need to be careful of the apostopies in the text)
3. So you will be able to see your insert query for the first row. Now copy the formula for the rest of the rows.
4. And presto, the script is ready. I suggest copy the query column to a text file first and save int '.sql'. Then run the script in the db.

Sorry, you have to create your tablethough.

I found the above procedure saves a lot of time as compared to sql loader or using GUI like Toad etc..
Reply With Quote
  #3  
Old August 21st, 2006, 09:13 AM
TerminatorJR's Avatar
TerminatorJR TerminatorJR is offline
Banned
 
Join Date: Nov 2004
Posts: 4,006
TerminatorJR has much to be proud ofTerminatorJR has much to be proud ofTerminatorJR has much to be proud ofTerminatorJR has much to be proud ofTerminatorJR has much to be proud ofTerminatorJR has much to be proud ofTerminatorJR has much to be proud ofTerminatorJR has much to be proud of
Re: Load data in SQL server from CSV

Okay, tumne hamaara chance maar liya tha ek baar. yaad hai ? Chalo aaj hamaara chance hai.

Assuming you are using SQL Server 2000, Open the SQL Enterprise Manager.

Create an empty database if you already don't have one.
Right click on the Database--> All tasks --> Import Data.

Rest of the steps are self-explanatory. You can choose your destination table name.

Here are some pics demonstrating the same.
Reply With Quote
  #4  
Old August 21st, 2006, 09:42 AM
ShivSainik's Avatar
ShivSainik ShivSainik is offline
Senior eCharchan
 
Join Date: Apr 2001
Location: San Jose, CA
Posts: 5,253
ShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond reputeShivSainik has a reputation beyond repute
Re: Load data in SQL server from CSV

Thanks kranti ani TJ !!!

I followed TJ's method and it worked like charm !! Tusi great ho TJ I owe you one now.
__________________
"Once you start thinking too much about your place in history, you're at a point where you're no longer trying as hard as you should be"
Reply With Quote
  #5  
Old August 21st, 2006, 06:49 PM
krantikari's Avatar
krantikari krantikari is offline
Senior eCharchan
 
Join Date: Jul 2000
Posts: 4,617
krantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond reputekrantikari has a reputation beyond repute
Re: Load data in SQL server from CSV

Quote:
Originally Posted by ShivSainik
Thanks kranti ani TJ !!!

I followed TJ's method and it worked like charm !! Tusi great ho TJ I owe you one now.

Great bhai.... do try out my method if the data is huge, and the database is located in some other part of the globe. It might save you maybe half a day of drudgery... Trust me.
Reply With Quote
Reply

Bookmarks

Tags
csv, load csv into sql, ms sql server, sql server


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
data center/data storage PeaceSeeker Technology 3 February 5th, 2011 10:57 PM
First letter of email address determines spam load echarcha Computing 3 December 13th, 2008 05:42 AM
Panoramas from Utah! (Warning: Hi Quality Photos, Slow page load!) rameshp Life Abroad 37 November 5th, 2007 08:07 AM
Oracle External Table to Load XML File motowner Computing 2 February 9th, 2007 10:25 AM
Load Shedding now in Mumbai too! Alibaba Taaza Khabar - Current news 12 May 4th, 2005 01:29 AM


All times are GMT -7. The time now is 07:02 AM.


Powered by vBulletin® Version 3.7.2
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Site Copyright © eCharcha.Com 2000-2012.