Hi
I am new to Ms-sql.
I have installed MSDE 2000. I have downloaded and installed.
and geting into visual studio.net to write query to create tables , viewsand stored procedures.. I have created a database with some tables.
and now i have a CSV file. I have to import data from the csv file to Msde tables.
but I dont have any options to import. and also how to go for DTS .
i DONT FIND DTS option anywhere.
pls help nme to do that.

if there is any method to import.pls explain it clearly how to do that.

dhol

__________________
SureShot

dhol,

Use a Bulk insert
EG:
BULK INSERT Northwind.dbo.[Order Details]
   FROM 'f:\orders\lineitem.tbl'
   WITH
      (
         FIELDTERMINATOR = '|',
         ROWTERMINATOR = '|\n'
      )

Prashant

Hi I used bulk insert , but i get a msg shown that
"BULK INSERT DATA CONVERSION ERROR(TYPE MISMATCH)FOR ROW1, COLUMN 11"
ie, this msg is shown afor all tables while importing. ie, last column .

john

It think your have not set column delimiter as the end or the row delimiter may not be correct.
First Try with a small eg with two cloumns, of it shows nay poblem post the sample row with column names

Prashant

Let me see a smaple

Hi

I will explain my doubt in a clear way.

I have a table named bank with 3 fields
CREATE TABLE dbo.bank
(
bank SMALLINT,
bank_name CHAR(20),
bank_acct CHAR(20)
)


now I have a data file with data's named "bank.unl"

1|ANZ|7757 96745|
2|METWAY||
3|WESTPAC||
5|TOWN & COUNTRY||
6|COMMONWEALTH||
7|BANK OF MELBOURNE||
8|NATIONAL AUST BANK||
10|S.B OF SA||
11|ST GEORGE BANK||
12|BANK OF QLD||
18|MACQUAIRE BANK||
19|ADVANCE BANK||
20|BANK OF NZ||
21|CHASE AMP BANK||
24|CITI BANK||
25|NAT WEST||
27|M ROYAL||
30|RURAL & IND BANK WA||
32|STATEWIDE BANK||
33|BARCLAYS AUST||
40|S.B OF NSW||
42|S.B OF TAS||
46|CIVIC ADVANCE BANK||
47|CHALLENGE BANK||
49|MONEY ORDER||
50|CASH DEPOSIT||
52|TRUST BANK||
53|BENDIGO BANK||


now using bulk insert I have to insert it.

I did it as
BULK INSERT dbo.bank
   FROM 'f:\bank.unl'
   WITH
      (
         FIELDTERMINATOR = '|',
         ROWTERMINATOR = '|\n'
      )      I get an error as follows
Bulk insert data conversion error(truncation)for row1, column3(bank_acct)

John,

I Copied your query in the query analyser and data to a file.
Suprisingly everything ran perfectly and 28 rows got inserted.

You are inserting a 21 chararter data into bank_acct.
or it is not nullable. Try increasing the field width, Or use unicode datatype if some extended characters are present in the data.

search the books online for any settings/formats for Bulk insert/BCP.

Prashant

Hi, now I am able to work. but again a small doubt
I am importing a txt file into a table.

I have a text file with data 's as follows
1|01/07/2000 TO 30/06/2001|Y|SYSTEM|11/09/2000
2|01/01/2000 TO 31/12/2000|Y|SYSTEM|11/09/2000
3|01/01/2001 TO 31/12/2001|Y|SYSTEM|11/09/2000
4|01/07/2001 TO 30/06/2002|Y|SYSTEM|18/07/2001
5|01/01/2002 To 31/12/2002|Y|SYSTEM|21/12/2001
6|01/07/2002 To 30/06/2003|Y|SYSTEM|21/12/2001
7|01/01/2003 TO 31/12/2003|Y|SYSTEM|24/12/2002
8|01/07/2003 TO 30/06/2004|Y|SYSTEM|30/12/2003
9|01/01/2004 TO 31/12/2004|Y|SYSTEM|30/12/2003
10|01/07/2004 to 30/06/2005|Y|system|01/09/2004
11|01/01/2005 to 31/12/2005|Y|SYSTEM|05/01/2005


I have a table named planner
create table planner(
  planner smallint not null,
  plan_desc char(40) not null,
  plan_locked char(1) not null,
  pword char(8),
  lchg_dte datetime not null
)


note that the last field is of datatype DATETIME.
now the column of the last field in the txt field is in the order dd/mm/yyyybut while using bulk insert it is accepting only of the order mm/dd/yyyy

what shall i do to import the data from thetxt file now.

here is the query

BULK INSERT planner
             FROM 'D:\planner.txt' WITH (FIELDTERMINATOR = '|')
pls help me to do this
DHOL

John,

Use the SET DATEFORMAT dmy
before the bulk insert

Prashant

can u give some detail on SET DATEFORMAT. as I am new to MSDE.pls explain it with small example pls.

dhol

It is used to change the input date format
Please find more info @ http://msdn.microsoft.com/library/de...t-set_052s.asp