Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 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 July 16th, 2008, 01:18 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 108
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to split a long string with tab delimited

Hi, all,

I have to create a stored procedure in SQL Server 2005.
I have a text file inside it each row has 65 data delimited by tab, there is no array and split function in stored procedure, what is the best way to parse this file?



 
Old July 16th, 2008, 02:43 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Are you looking for something like this??

CREATE PROCEDURE demo
    @myText NVARCHAR(4000) -- a great big long string?
AS

DECLARE @tabAt INT
DECLARE @data NVARCHAR(100)

WHILE LEN(@myText) > 65
BEGIN
    SET @tabAt = CHARINDEX( CHAR(9), @myText )
    IF @tabAt = 0 OR @tabAt > 65
    BEGIN
        -- if no tab found with 65 characters,
        -- break string at 65 characters anyway
        SET @data = LEFT( @myText, 65 )
        SET @tabAt = 65
    ELSE
        SET @data = LEFT( @myText, @tabAt - 1 )
    END
    -- so insert a new record with up to 65 characters of text
    INSERT INTO tablename ( fieldname ) VALUES( @data )

    -- then chop the front off the big string and continue
    SET @myText = SUBSTRING( @myText, @tabAt + 1, LEN(@myText)-@tabAt )
END
-- insert the remaining text (if any)
IF LEN(@myText) > 0
BEGIN
    INSERT INTO tablename ( fieldname ) VALUES( @myText )
END

If your text is longer than 4000 Unicode characters, then I think it can still be done, but is a bit more complex.
 
Old July 16th, 2008, 03:57 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 108
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you, for your reply. I am new about stored procedure.
my data is as the following, tab delimitered, total there are 65 data each line.
STR_ID TITLE PFIELD PDESC DEF_UNIT COMP_ID ID_NO Q_ID CODE COMPDESC S_NOD ...
111 32 21 No 00000043 UIM03/D CLOSED SELECTED ANODE BAN A105(-)42M .....
112 32 21 No 00000043 UIM03/D CLOSED SELECTED ANODE BAN A105(-)42M .....
113 32 21 No 00000043 UIM03/D CLOSED SELECTED ANODE BAN A105(-)42M .....
114 32 21 No 00000043 UIM03/D CLOSED SELECTED ANODE BAN A105(-)42M .....

Do I need to create 65 variables and then read all the 65 data each line into these variable, then insert these 65 data in my real table?

I saw you create a temporary table to store the data, what's the advantage of it?
in the temporary table you store each data in one row, by in my real table, one row contains all these 65 data, how can I do it? Do I need to clearly specify the column name while inert into my real table?

Thanks again

Andraw


 
Old July 16th, 2008, 04:36 PM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Does this have to be done in SQL Server? It sounds like you are trying to hammer a nail with a saw: wrong tool for the job. Perhaps this should be parsed programmatically first, then translated into a logic data structure that SQL can handle.

-Peter
compiledthoughts.com
 
Old July 16th, 2008, 04:41 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

The OP is using SQL Server 2005, so I think you could use NVARCHAR(max) instead of NVARCHAR(4000).

However, as Peter pointed out, this may be the wrong tool for the job.

Personally, I would use SSIS / DTS, or Bulk Insert, or parse the file in another language (.NET, whatever) and then send the parsed data to a stored procedure.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of Beginning ASP.NET 3.5 : in C# and VB, ASP.NET 2.0 Instant Results and Dreamweaver MX 2004
Want to be my colleague? Then check out this post.
 
Old July 16th, 2008, 05:05 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 108
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That's my headache problem, I already study and post my questions in last two days but no body answer me, that's why I use this way to do it, it's so complicated

Can you give me some suggestion for my case?
My client will give us a text file as I described above everyday, we need to create a job and schedule this job to run at midnight everyday.

Inside the job I need to read the data from the file, the data in each row is delimitered with tab, there are total of 65 fields of data each line, after I read these data, I need to insert them in my database, WHAT'S THE RIGHT WAY TO CREATE THIS JOB???? I USE MS SQL 2005.

Before when I create job in MS SQL 2000 at DTS, I can write script codes to do it, it's much easier. now with sp, it's so hard.

CAN I ALSO USE SCRIPT CODE TO DO IT IN MS SQL 2005. IF THERE ARE SOME SAMPLE CODES, I WILL BE VERY APPRECIATE.

THANKS!




 
Old July 16th, 2008, 05:48 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

The fastest way to do this is to use Bulk Insert to load the file into a staging table and then validate the data there. The sample code you are looking for is in Books Online (help system for SQL Sever).

You can use the following to see things in a directory...

EXEC Master.dbo.xp_DirTree 'filepath here',1,1

Of course, you can do an INSERT/EXEC to get that directory listing into a table to "step through" or select the file name.

--Jeff Moden
 
Old July 16th, 2008, 06:17 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Yes, for sure BULK INSERT is the way to go.

Sorry...I ready your "65" as meaning "65 characters", not "65 data fields". I thought we were talking about free-form text here. Silly me.
 
Old July 16th, 2008, 06:27 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 108
Thanks: 0
Thanked 0 Times in 0 Posts
Default

if use BULK INSERT, it store data in a temporary table, but I still need to read data from this temporary table, split the line, then store the data in my real table, am I right?

is it good to use SSIS?

I am a beginner, I want to have a easier way to solve this issue.
Thanks!

 
Old July 16th, 2008, 06:31 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

No... the line is already split at the tab. Please, check out "BULK INSERT" in Books Online.

--Jeff Moden





Similar Threads
Thread Thread Starter Forum Replies Last Post
Write Tab-delimited text file VikramMullick Pro VB.NET 2002/2003 1 May 5th, 2006 11:18 AM
Tab delimited Split ajindal General .NET 1 April 25th, 2006 08:20 AM
Split a long vedio to several parts Andraw Classic ASP Basics 0 May 3rd, 2005 10:58 AM
Frustrations of Split(long) enterbase Access VBA 6 January 27th, 2004 04:19 PM
Upload tab delimited into Access? mariakovacs Classic ASP Databases 0 October 10th, 2003 10:54 AM





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