Hi Robert,
i'm using the following stored proc (sp) for this. I suggest to use field
separators to make it
easier to separate the columns. This sp can be executed by a job.
The imported file should look like this:
1234;abcd;1212
321123;kdkdkd;121233
In the sp you have to replace CPRave15 with your database name.
Hope it helps.
Michael Zankl
http://www.zankl-it.de
Berlin
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/*
================================================== ==========================
==
Syno: imports file specified in @UNCPathFileName into a table, specified
in @DBTable
Use ';' as fieldterminator in the imported file
REMARKS:
- User, who runs this SP, has to be a member of SysAdmin
or BulkAdmin
- User must have Insert-Permission on specified Table or
has to be a member of db_owner
TEST:
DECLARE @RC int,
@UNCPathFileName varchar(1024),
@DBTable varchar(128)
SET @UNCPathFileName = '\\Absrv02\Components\Debitoren.csv'
SET @DBTable = 'cprSYSMD_DebImp'
EXEC @RC = cprIMP_File @UNCPathFileName, @DBTable
PRINT @RC
select * from cprsysmd_debimp
--delete from cprSYSMD_DebImp
Author: MZA,
http://www.zankl-it.de, 14.01.2003
================================================== ==========================
==
*/
CREATE PROCEDURE cprIMP_File @UNCPathFileName varchar(1024),
@DBTable varchar(128)
AS
DECLARE @RetVal int,
@Cmd varchar(8000)
--Example
-- BULK INSERT CPRave15.dbo.cprSYSMD_DebImp
-- FROM '\\Absrv02\Components\Debitoren.csv'
SET @Cmd = '
BULK INSERT CPRave15.dbo.' + @DBTable + '
FROM ''' + @UNCPathFileName + '''
WITH (FIELDTERMINATOR = '';'')' --<== IMPORTANT: use a fieldterminator in
imported file
--print @Cmd
EXEC (@Cmd)
SET @RetVal = @@ROWCOUNT
RETURN @RetVal
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"Robert K" <rkloma@hotmail.com> schrieb im Newsbeitrag
news:bi1qj0$etr$1@news.onet.pl...[color=blue]
> Hello,
>
> I have problem:
>
> My *.txt file is like it:
> "
> 12345612345678123
> abcdefabcdefghabc
> " etc.
>
> i want upload data into table (for example TEST) i want to sql read this
> file and automatically upload to table.(as job for example)
> but i have 3 columns and i dont know how to separate this text to 3[/color]
diffrent[color=blue]
> text columns
>
> 1 column | second column | third column
> -----------------------------------------
> 123456 | 12345678 | 123
> abcdef | abcdefgh |abc
>
> PLEASE HELP ME, i dont know how to do it.
>
> Robert Kloma
>
>
>
>
>
>[/color]
