| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2004-12-02 : 12:38:31
|
| My text file has a trailing record like: TRAILER RECORD0000000851This will cause problem for my import DTS. How can I get ride of the trailing record?Thanks,Canada DBA |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-12-02 : 12:44:29
|
| Don't you want to audit the file?What's the table DDL look likeWhat's the file look like(Delimited, fixed width?)Brett8-) |
 |
|
|
CanadaDBA
583 Posts |
Posted - 2004-12-02 : 13:18:27
|
The file is being generated everyday by mainframe and I don't want to mannually change it.I couldn't get this: What's the table DDL look likeIt is fixed width. Like:00000000210000000029001593400023630000000000000002SYLVI HAYAN quote:
Originally posted by X002548
Don't you want to audit the file?What's the table DDL look likeWhat's the file look like(Delimited, fixed width?)Brett8-)
Canada DBA |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
CanadaDBA
583 Posts |
Posted - 2004-12-03 : 10:20:17
|
Thanks robvolk. I could remove the trailing row from the text file and create a new text file. FindSTR /V "TRAILER" Insurance.txt > InsuNew.txt Since I am going to use an SP and DTS, I have to run this command from within the SP and then call the DTS, right? Do you have better suggestion?quote:
Originally posted by robvolk
Take a look at these:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=41614http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=42436That's probably the easiest way to do it.
Canada DBA |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-03 : 10:46:00
|
| You can add an Execute process task to your DTS package to run findstr before it executes the rest of the steps.As an alternative, you could use bcp with a format file to import the new text file (you need a format file to import fixed-width data) Doing this will let you avoid DTS entirely. Both findstr and bcp can be put into a batch file that is called from a job, or as individual job steps. You can then call the job using the sp_start_job procedure, or schedule it to run whenever you wish. There's nothing wrong with using DTS for this but it's not really necessary or beneficial either, the process is pretty simple. |
|
|
|
CanadaDBA
583 Posts |
Posted - 2004-12-03 : 11:59:52
|
| Both findstr and bcp can be put into a batch fileThen I have to have the batch file somewhere in my server. I have to avoid having external files but I can put both lines in a SP and execute them from within that SP. I need to put each data in its own field. Does bcp do it? I am going to BOL to study the bcp.Canada DBA |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-03 : 12:09:52
|
| Doing it as a regular job lets you avoid a batch file. You *can* use a stored procedure but I don't see any benefit to it.bcp and BULK INSERT can't parse out fixed-length data without a format file. If you absolutely can't have external files, you can import the entire line into a varchar(8000) staging table and parse it from there. |
|
|
|
CanadaDBA
583 Posts |
Posted - 2004-12-03 : 14:04:30
|
| Yes, you are right. I totally forgot about format file. I am studing bcp.I couldn't get this:Doing it as a regular job lets you avoid a batch file.what do you mean?Canada DBA |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-03 : 14:09:22
|
| Instead of putting both findstr and bcp into a batch file and running it, you can create a job and add two CmdExec steps. The first step runs findstr, the 2nd runs bcp. When the job runs both steps will be executed. |
|
|
|
|
Detecte trailing record