Import from a file - SQL Server Forums

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

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Import from a file

Author

Topic

pmccann1
Posting Yak Master

107 Posts

Posted - 2006-09-12 : 05:13:13

I was wondering if someone could tell me how to call a folder and import a file from that folder into a sql table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-12 : 05:15:25

What is the information in the file made of?
Excel data? Text file? Other legacy system?

You can use DTS, OPENROWSET or BCP. There are many alternatives.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-12 : 05:15:42

see here http://www.mindsdoor.net/SQLTsql/ImportTextFiles.html

pmccann1
Posting Yak Master

107 Posts

Posted - 2006-09-12 : 05:19:33

it is a .txt file

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-12 : 05:27:38

How many records are there in the file? Are there many files?
What size does any record have? Are the records fixed length or delimited?

For what purpose? Simply importing or updating existing data?
Will this import be done repeatedly?

Help us help you by providing as much information as you can, from the beginning.

Peter Larsson
Helsingborg, Sweden

pmccann1
Posting Yak Master

107 Posts

Posted - 2006-09-12 : 05:34:05

It is comma delimted text file and contains roughly 34,000 rows, it is on a server and is produced weekly on a sunday the file is put in a folder and given the sunday date .csv eg(log_06/09/10.csv)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-09-12 : 05:35:51

use bulk insert.
i acctually prefer it over bcp because it's a T-sql command and not a command line utility.

but use whicever work best for your enviroment.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-12 : 05:40:02

It doesn't seem that the file contains a lot of records. Most probably you need dynamic sql for this task due to the different file names.

Try this!

declare @path varchar(100), @sqlcmd varchar(1000)
select @path = '''c:\documents and settings\selupln\desktop\test.txt'''

create table #temp (a varchar(100), b varchar(100))

select @sqlcmd = '
BULK INSERT #Temp
   FROM ' + @path + '
   WITH 
      (
         FIELDTERMINATOR = '','',
         ROWTERMINATOR = ''\n''
      )'
exec (@sqlcmd)

select * from #temp
drop table #temp

Peter Larsson
Helsingborg, Sweden

pmccann1
Posting Yak Master

107 Posts

Posted - 2006-09-12 : 05:59:01

when i run this i get the following error

Bulk insert data conversion error (truncation) for row 1, column 2 (b)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-12 : 06:02:56

Yes. The #Temp table has to have the same column definition used in the text file.
I just added two columns since I don't know the layout of the file.

If your file has 18 columns, the #Temp table has to have 18 columns too.

Peter Larsson
Helsingborg, Sweden

pmccann1
Posting Yak Master

107 Posts

Posted - 2006-09-12 : 06:16:47

i must be so stupid there is 12 colums so i did the following

create table #temp (a varchar(100), b varchar(100),c varchar(100),
d varchar(100), e varchar(100),f varchar(100),g varchar(100),
h varchar(100), i varchar(100), j varchar(100),k varchar(100), l varchar(100))

but still get the error

Bulk insert data conversion error (truncation) for row 2, column 13 (l).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-12 : 06:26:58

Add one column more and see if the error disappears. Or, extend the VARCHAR(100) to VARCHAR(200) instead.
In the example above, I have set LF as row terminator. This works since the error occur at row two.

Does not all rows have the same amount of columns?

Peter Larsson
Helsingborg, Sweden

pmccann1
Posting Yak Master

107 Posts

Posted - 2006-09-12 : 06:39:41

added another column get same error i must be doing something very silly

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-12 : 06:43:32

Post the first four rows from file here. Use # button to enclose file information with code tags.

And if possible, post also the file format for all columns here.

Peter Larsson
Helsingborg, Sweden

pmccann1
Posting Yak Master

107 Posts

Posted - 2006-09-12 : 07:20:03

here is one row from csv file cos its quite long

9763,06/09/03 17:59:58,06/09/03 17:59:58,C:Component,X:Director,C:Cyan, ,*,N:Not Present,0,arkuser3@arkclient3,Director,04/09/2006,IM:FIR::BIK,1,,6108,||BIK011,Damaged plate of sheet BIK011 (Cyan) of [2006-09-04 -> IM -> FIR] has been requested by user "arkuser3" on "arkclient3".

script for table
CREATE TABLE [dbo].[ark] (
[RecNo:0] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pulication] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Edition] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateStamp] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TimeStamp] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[day] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[page] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PageName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Message:18] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[year] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[month] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dateday] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

pmccann1
Posting Yak Master

107 Posts

Posted - 2006-09-12 : 07:21:36

i can get it to inport some of file now but get the following error

Bulk insert data conversion error (truncation) for row 99091, column 12 (l).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-12 : 07:23:43

The length of "Damaged plate of sheet BIK011 (Cyan) of [2006-09-04 -> IM -> FIR] has been requested by user "arkuser3" on "arkclient3"." is 120 characters. Do as I suggested earlier and replace the VARCHAR(100) with VARCHAR(200) to allow longer string of data to be stored.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-12 : 07:26:34

For what I can see, you have 19 columns.


 1 - 9763
 2 - 06/09/03 17:59:58
 3 - 06/09/03 17:59:58
 4 - C:Component
 5 - X:Director
 6 - C:Cyan
 7 - 
 8 - *
 9 - N:Not Present
10 - 0
11 - arkuser3@arkclient3
12 - Director
13 - 04/09/2006
14 - IM:FIR::BIK
15 - 1
16 - 
17 - 6108
18 - ||BIK011
19 - Damaged plate of sheet BIK011 (Cyan) of [2006-09-04 -> IM -> FIR] has been requested by user "arkuser3" on "arkclient3".

Peter Larsson
Helsingborg, Sweden

pmccann1
Posting Yak Master

107 Posts

Posted - 2006-09-12 : 07:28:16

if you look at the script there is only 12 columns and i ahve set them all to 255

declare @path varchar(100), @sqlcmd varchar(1000)
select @path = '''\\Arkclient4\c$\Arkitex\Logviewer\Logs\Logview_060910.csv'''

create table #temp (a varchar(255), b varchar(255),c varchar(255),
d varchar(255), e varchar(255),f varchar(255),g varchar(255),
h varchar(255), i varchar(255), j varchar(255),k varchar(255), l varchar(255))

select @sqlcmd = '
BULK INSERT #Temp
FROM ' + @path + '
WITH
(
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n''
)'
exec (@sqlcmd)

pmccann1
Posting Yak Master

107 Posts

Posted - 2006-09-12 : 07:30:33

just discovered there is a prob with the table and has to be sorted so has been sent back to company

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-12 : 07:32:22

Yes, but you have 19 columns in the sample you posted.

Peter Larsson
Helsingborg, Sweden

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources