SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Importing data from CSV or txt file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

abuhassan
Posting Yak Master

105 Posts

Posted - 07/27/2006 :  08:01:10  Show Profile  Reply with Quote
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

India
22761 Posts

Posted - 07/27/2006 :  08:23:39  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
See if this helps
http://www.nigelrivett.net/SQLTsql/ImportTextFiles.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 07/27/2006 :  08:24:41  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

abuhassan
Posting Yak Master

105 Posts

Posted - 07/27/2006 :  08:44:49  Show Profile  Reply with Quote
Is it not possible using a sql statement
Go to Top of Page

mahesh_bote
Constraint Violating Yak Guru

India
298 Posts

Posted - 07/27/2006 :  08:45:14  Show Profile  Reply with Quote
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
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 07/27/2006 :  08:52:57  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

abuhassan
Posting Yak Master

105 Posts

Posted - 07/27/2006 :  09:03:19  Show Profile  Reply with Quote
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?

Edited by - abuhassan on 07/27/2006 09:04:32
Go to Top of Page

mahesh_bote
Constraint Violating Yak Guru

India
298 Posts

Posted - 07/27/2006 :  09:17:59  Show Profile  Reply with Quote
http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html

Mahesh
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 07/27/2006 :  09:34:01  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

abuhassan
Posting Yak Master

105 Posts

Posted - 07/27/2006 :  10:11:50  Show Profile  Reply with Quote
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?
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 07/27/2006 :  10:52:39  Show Profile  Visit nr's Homepage  Reply with Quote
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.

Edited by - nr on 07/27/2006 10:53:15
Go to Top of Page

abuhassan
Posting Yak Master

105 Posts

Posted - 07/27/2006 :  11:18:23  Show Profile  Reply with Quote
thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.5 seconds. Powered By: Snitz Forums 2000