| Author |
 Topic  |
|
|
abuhassan
Posting Yak Master
105 Posts |
 Posted - 07/27/2006 : 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
India
22461 Posts |
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 07/27/2006 : 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
Posting Yak Master
105 Posts |
Posted - 07/27/2006 : 08:44:49
|
| Is it not possible using a sql statement |
|
|
|
mahesh_bote
Constraint Violating Yak Guru
India
298 Posts |
Posted - 07/27/2006 : 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
United Kingdom
12543 Posts |
Posted - 07/27/2006 : 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
Posting Yak Master
105 Posts |
Posted - 07/27/2006 : 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? |
Edited by - abuhassan on 07/27/2006 09:04:32 |
|
|
|
mahesh_bote
Constraint Violating Yak Guru
India
298 Posts |
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 07/27/2006 : 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
Posting Yak Master
105 Posts |
Posted - 07/27/2006 : 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
United Kingdom
12543 Posts |
Posted - 07/27/2006 : 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. |
Edited by - nr on 07/27/2006 10:53:15 |
|
|
|
abuhassan
Posting Yak Master
105 Posts |
Posted - 07/27/2006 : 11:18:23
|
| thanks |
|
|
| |
Topic |
|
Importing data from CSV or txt file
