Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
|
SQL Server DTS Discussion specific to Data Transformation Service with SQL Server. General SQL Server discussions should use the general SQL Server forum. Readers of the book Professional SQL Server 2000 DTS with questions specific to that book should post in that book forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server DTS section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 22nd, 2005, 11:19 PM
Authorized User
 
Join Date: Dec 2004
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default Import from CSV to MSDE tables

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
 
Old April 23rd, 2005, 12:00 AM
Friend of Wrox
 
Join Date: Apr 2005
Posts: 186
Thanks: 0
Thanked 0 Times in 0 Posts
Default

dhol,

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

Prashant

 
Old April 26th, 2005, 11:48 PM
Authorized User
 
Join Date: Dec 2004
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old April 27th, 2005, 02:01 AM
Friend of Wrox
 
Join Date: Apr 2005
Posts: 186
Thanks: 0
Thanked 0 Times in 0 Posts
Default


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


 
Old April 27th, 2005, 06:30 AM
Authorized User
 
Join Date: Dec 2004
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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)



 
Old April 27th, 2005, 06:56 AM
Friend of Wrox
 
Join Date: Apr 2005
Posts: 186
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old April 28th, 2005, 12:55 AM
Authorized User
 
Join Date: Dec 2004
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old April 28th, 2005, 02:02 AM
Friend of Wrox
 
Join Date: Apr 2005
Posts: 186
Thanks: 0
Thanked 0 Times in 0 Posts
Default

John,

Use the SET DATEFORMAT dmy
before the bulk insert

Prashant

 
Old April 28th, 2005, 04:55 AM
Authorized User
 
Join Date: Dec 2004
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

dhol

 
Old April 28th, 2005, 06:51 AM
Friend of Wrox
 
Join Date: Apr 2005
Posts: 186
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
Import CSV files to Database in C# prasanna.nadgir C# 2 September 4th, 2007 11:03 AM
Import data to CSV itHighway Classic ASP Basics 0 September 15th, 2006 10:31 AM
Import data in CSV file itHighway Classic ASP Professional 0 September 15th, 2006 10:29 AM
CSV and Excel sheet import itHighway Classic ASP Professional 0 August 5th, 2006 08:25 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.