Help parsing a text file with SQL from a DTS

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 2005 Forums

Transact-SQL (2005)

Help parsing a text file with SQL from a DTS

Author

Topic

RobinM
Starting Member

7 Posts

Posted - 2009-06-05 : 14:27:49

Hello everyone. I have a txt file that I need to pull into a sql table. I want to use a DTS and schedule it to run each day. Here's the problem, I can only get this file in .txt format AND the format is not the same in the .txt file as the table is. Here is an example of what the .txt file looks like:

Data in this row should be column 1
Data in this row should be column 2
Data in this row should be column 3

So, how do I parse this so that the data in rows 2 and 3 become columns 2 and 3? I hope this makes sense.

Thanks for any help you can offer.

Robin M

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-06-05 : 14:36:11

make the column delimiter a carriage-return/ linefeed in addition to your row delimiter being a carriage return/ linefeed. I think that should work.

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-05 : 14:37:38

Maybe row 4 is again for column 1, row 5 for column 2 etc.
How do you know that?

No, you're never too old to Yak'n'Roll if you're too young to die.

RobinM
Starting Member

7 Posts

Posted - 2009-06-05 : 15:36:43

webfred, thanks...yes that is the other question. Obviously, this task is a new one for me. I know how to create a dts, I do NOT know how to parse data from a raw txt file :) Here's a blurb from the actual txt file with the ids and such changed (so I don't get fired lol):

(removed file data...not comfortable leaving it out here)

Ok, so that file is a few 40,000 rows long :) This is what I want to do: First,remove all the s-1-...lines. They can be deleted. Then I want all the lines that start with the word "Mailbox:" to be column 1 and each line that starts with "Novant" to be column 2 so that the end result is a table that looks like this:

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-06-05 : 16:26:57

That is beyond the capabilities of raw dts without putting in a vbscript or some other logic engine.

If you had


ColumnA
ColumnB
ColumnC
ColumnA
ColumnB
ColumnC
...
ColumnA
ColumnB
ColumnC

you could still define it in DTS/ SSIS. What you're talking about requires each line to be evaluated to determine where it goes, and you are officially outside the realm of a standard import.