How to insert data from a csv file into a SQL server database?

$enthil · #1 (**permalink**) 10-29-2007, 10:02 PM

I am having a csv file with comma (,) seperated values having some 3 lakh records in it. I have to insert it into the database. Is it possible to insert the data from csv file to database? If so, how? Can anyone help me....

kingmaker · #2 (**permalink**) 10-29-2007, 10:45 PM

Yes...we can insert the data from csv using bulk insert for example

-- insert the data
bulk insert [dbo].[tablename]
from '<<ToDo: add path to sample.csv>>'
with
(
fieldterminator = ',',
rowterminator = '\n'
)
GO

$enthil · #3 (**permalink**) 10-30-2007, 07:54 AM

yes.. its working fine... thanks for ur help

priyan · #4 (**permalink**) 11-09-2007, 10:29 PM

Quote:

Originally Posted by kingmaker

Yes...we can insert the data from csv using bulk insert for example

-- insert the data
bulk insert [dbo].[tablename]
from '<<ToDo: add path to sample.csv>>'
with
(
fieldterminator = ',',
rowterminator = '\n'
)
GO

Hi,

Can we able to import data from Excel / Text (Tab Delimited) file into SQL server ?

What are all the file types SQL server supports for import data ?

$enthil · #5 (**permalink**) 11-12-2007, 01:19 AM

Quote:

Originally Posted by priyan

Hi,

Can we able to import data from Excel / Text (Tab Delimited) file into SQL server ?

What are all the file types SQL server supports for import data ?

yes.. we can able to import data from excel or text file...

Import from EXCEL file:

Follow the steps
1. Right click the database--> Tasks--> Import Data
2. Select the Datasource from the drop down and click next
For excel, select Microsoft excel
For text and csv files, select Flat File source
3. Select destination database and table to insert the data from the file to db

Supported file types:
1. xls
2. csv
3. txt
4. mdb

The above files i have tested..

priyan · #6 (**permalink**) 11-12-2007, 05:14 AM

Hi,

Thanks for your reply..

Can you please provide the command line syntax for importing XLS and MDB file into SQL Server.

$enthil · #7 (**permalink**) 11-12-2007, 08:31 PM

Try this query to bulk insert data from a file

syntax:

Quote:

BULK INSERT TableName
FROM 'filepath'
WITH
(
FIELDTERMINATOR = ' |',
ROWTERMINATOR = '|\n'
)

example:

Quote:

BULK INSERT AdventureWorks.Sales.SalesOrderDetail
FROM 'f:\orders\lineitem.tbl'
WITH
(
FIELDTERMINATOR =' |',
ROWTERMINATOR =' |\n'
)

jaydon · #8 (**permalink**) 05-01-2008, 09:45 PM

where do u insert the script into cause when i import it into vb script. theres alot of problem.. help is required!! thanks

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Insert Multiple records using single Insert Statement	vadivelanshanmugam	Database Support	0	03-04-2008 09:26 PM
example of Inserting and Retrieving data from xml file	hanusoft	ASP and ASP.NET Programming	1	11-12-2007 04:13 AM
Copy SQL data structures from one SQL server to another SQL server?	arjkhanna	Server Management	5	11-05-2007 02:57 AM
reading data from a file using C#	oxygen	C# Programming	2	08-23-2007 12:48 AM
Data and Database Integrity Testing	sudhir	Software Testing	1	04-01-2007 08:35 PM