Importing data from CSV or txt file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Importing data from CSV or txt file

Author

Topic

abuhassan

105 Posts

Posted - 2006-07-27 : 08:01:10

Hi

How can i import data from a csv file or text file

i have the text file located at C:Imports/Importdata.csv

the table that i have is as follows:

CREATE TABLE Invoice
(
InvoiceID INT IDENTITY (1, 1) PRIMARY KEY NOT NULL,
Invoice_no VARCHAR(50),
RefText VARCHAR(50),
CustomerDetails text,
)

i dont have any data for the invoiceID is it possible to import the data from the csv file .... the csv file partially is shown below...

Invoice_no, RefText, Customer details
272727, treesale, Garstang
345667, TidyBox, northampton

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-27 : 08:23:39

See if this helps
http://www.nigelrivett.net/SQLTsql/ImportTextFiles.html

Madhivanan

Failing to plan is Planning to fail

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-27 : 08:24:41

Easiest is to create a view on the table which excludes the identity column and bulk insert into that.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

abuhassan

105 Posts

Posted - 2006-07-27 : 08:44:49

Is it not possible using a sql statement

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-07-27 : 08:45:14

SQL Server has one utility called - BCP

BULK INSERT <DB>..<Table> FROM <File Path & Name>
WITH (
DATAFILETYPE = '<Type>', i.e. char etc
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n')

user Books OnLine for more information under
"bcp utility, copying data from data file to SQL Server"

Mahesh

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-27 : 08:52:57

You will probably have problems with thye identity though - hence the view.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

abuhassan

105 Posts

Posted - 2006-07-27 : 09:03:19

In Mysql there is a Load data statement that lets you define the colunms in which you wish to send the data.....

LOAD DATA LOCAL INFILE '/importfile.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, filed2, field3);

the field line at the bottom lets u specify the fields in which you wish to send the data in.....

is there any thing similar in sql server tsql?

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-07-27 : 09:17:59

[url]http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html[/url]

Mahesh

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-27 : 09:34:01

quote:
Originally posted by abuhassan

In Mysql there is a Load data statement that lets you define the colunms in which you wish to send the data.....

LOAD DATA LOCAL INFILE '/importfile.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, filed2, field3);

the field line at the bottom lets u specify the fields in which you wish to send the data in.....

is there any thing similar in sql server tsql?

There's nothing like that - you would use a format file to specify the columns.
You could also import into a staging table then move to the productiontable
see
http://mindsdoor.com/topic.asp?TOPIC_ID=103

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

abuhassan

105 Posts

Posted - 2006-07-27 : 10:11:50

hi

is it possible to use

BULK INSERT <DB>..<Table> FROM <File Path & Name>
WITH (
DATAFILETYPE = '<Type>', i.e. char etc
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n')

and to specify some how ... the fields in which to put the data and allow the invoiceid to be auto matically be generated?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-27 : 10:52:39

No - not without a view or format file - see my previous post.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

abuhassan

105 Posts

Posted - 2006-07-27 : 11:18:23

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources