Loading text file data into a table

Question

Post reply

Loading text file data into a table

Imke Cronje

SSCarpal Tunnel

Points: 4826
More actions
July 5, 2007 at 1:55 am

#73210

Hi,
I am supposed to create a DTS package that will load data from a text file into a table.
The text files data looks like this:
TestREM01 - REM SMS: Control Dates Failure on Jul 3 2007 8:10AM
There are no headings in this text file.
My table columns are ServerName and Alert. TestREM01 is the ServerName and after the hyphen is the alert. I want them to be in two separate columns in the table.
How do I go about this? My package currently consists of a Text file(Source) connection to get the file(.txt), a Transfer Data Task to transfer the data and a Micosoft OLE DB Provider for SQL Server connection for a connection to the table on the database.
How am I going to separate the text files dataso that they can appear in to separate columns?
Please help!!!
IC

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

It is possible with specifying other as the delimiter option and the using - as your delimiter.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Imke Cronje SSCarpal Tunnel Points: 4826 More actions · Answer 2

Thanks for the info Sugesh.

Another problem: the spec has changed, I no longer have to separate the columns when I take it to the table. The text file data will be taken to the temp table as it is but now I have to the data over from the temp table to another table that has 3 columns: ServerName, Alert and Alert Date.

Can anyone pls explain to me how I will be able to separate the data in 3 columns.

The data looks like this:

TestREM01 - REM SMS: Control Dates Failure on Jul 3 2007 8:10AM

TestREM01 - ServerName,

REM SMS: Control Dates Failure - Alert

Jul 3 2007 8:10AM Alert Date

Please help!!!

IC

bledu SSChampion Points: 12335 More actions · Answer 3

do you have any control on the source application, if so you could consider put delimiters when generating the errors.

if not you can make assumptions about the month being mmm, then use a table valued function to get the starting position of the month (using charindex) and substring to separate the columns

Everything you can imagine is real.

Imke Cronje SSCarpal Tunnel Points: 4826 More actions · Answer 4

I don't have any control of the source application, and I also tried to substring but it won't work because the ServerName are not all 8 characters long.

bledu SSChampion Points: 12335 More actions · Answer 5

select

LEFT(errorCol,DashPoint-1) as Servername,

SUBSTRING

(errorCol,DashPoint + 1,MonthPoint-DashPoint) as Errata,

SUBSTRING

(errorCol,MonthPoint+2,len(errorCol)-MonthPoint) as ErrataDate

FROM

logTable A

inner join

(

select

id,

charindex

('-',errorCol) as DashPoint,

charindex

(dbo.MonthChars(errorCol),errorCol) as MonthPoint

from

logTable

)

B on A.id = B.id

you will have to create a dbo.MonthChars scalar UDF which return the month string incl the on e.g

for Jul 19 2007 will return 'on Jul' that is assuming that the month is the format MMM. and of course you might have to make ammendments to teh code i posted because i di not test it fully

Everything you can imagine is real.

Imke Cronje SSCarpal Tunnel Points: 4826 More actions · Answer 6

Thanks for the info but I have never used scalar UDF 's before.

Can anyone assist me with this one.