Problem with uploading data SQL

[Robert K]

Hello,

I have problem:

My *.txt file is like it:
"
123456123456781 23
abcdefabcdefgha bc
" 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 diffrent
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

[Michael Zankl]

Re: Problem with uploading data SQL

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;1 21233

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_IDENTIFI ER OFF
GO
SET ANSI_NULLS ON
GO



/*
=============== =============== =============== =============== =============== =
==
Syno: imports file specified in @UNCPathFileNam e 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,
@UNCPathFileNam e varchar(1024),
@DBTable varchar(128)


SET @UNCPathFileNam e = '\\Absrv02\Comp onents\Debitore n.csv'
SET @DBTable = 'cprSYSMD_DebIm p'


EXEC @RC = cprIMP_File @UNCPathFileNam e, @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 @UNCPathFileNam e varchar(1024),
@DBTable varchar(128)
AS


DECLARE @RetVal int,
@Cmd varchar(8000)




--Example
-- BULK INSERT CPRave15.dbo.cp rSYSMD_DebImp
-- FROM '\\Absrv02\Comp onents\Debitore n.csv'



SET @Cmd = '
BULK INSERT CPRave15.dbo.' + @DBTable + '
FROM ''' + @UNCPathFileNam e + '''
WITH (FIELDTERMINATO R = '';'')' --<== IMPORTANT: use a fieldterminator in
imported file

--print @Cmd
EXEC (@Cmd)

SET @RetVal = @@ROWCOUNT



RETURN @RetVal

GO
SET QUOTED_IDENTIFI ER 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:
> "
> 123456123456781 23
> abcdefabcdefgha bc
> " 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]

[Simon Hayes]

Re: Problem with uploading data SQL

"Robert K" <rkloma@hotmail .com> wrote in message news:<bi1qj0$et r$1@news.onet.p l>...[color=blue]
> Hello,
>
> I have problem:
>
> My *.txt file is like it:
> "
> 123456123456781 23
> abcdefabcdefgha bc
> " 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 diffrent
> 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]

One option is to create a staging table with one column, load the data
into that table without changing it, then insert into the final table
like this:

insert into dbo.TEST (col1, col2, col3)
select left(StagingCol umn, 6), left(StagingCol umn, 8),
left(StagingCol umn, 3)
from dbo.StagingTabl e

Simon

[Robert K]

Re: Problem with uploading data SQL

hello, thank you for hint but:

i want to read this data
My *.txt file is like it:
"
1234567890

" etc

1 column | second column | third column
-----------------------------------------
12 | 3456 | 789

how to do it ,
[color=blue]
> One option is to create a staging table with one column, load the data
> into that table without changing it, then insert into the final table
> like this:
>
> insert into dbo.TEST (col1, col2, col3)
> select left(StagingCol umn, 2), left(StagingCol umn, 4),
> left(StagingCol umn, 4)
> from dbo.StagingTabl e[/color]

the result is of this is
1 column | second column | third column
-----------------------------------------
12 | 1234 | 1234