Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

Format file for BCP

I have the attached file that I need to post-process with the following steps:

1. Take Subset of file

The post processed file should start with the section that has the header
"name                                                    number      symbol      initial wgt      optimized wgt      target wgt      min wgt      max wgt      volatility      partial 1 (and 2)      partial 3      partial 4      partial 5      r-squared      tilt value stock alerts"

2. Remove Column

Then the column "Stock Alerts" needs to be removed

The columns after removing the first section are tab delimited. I need to then BCP them into SQL Server. Can someone write either a python or a perl script to do this for me?
0
copiacap
Asked:
copiacap
  • 8
  • 7
1 Solution
 
jeromeeCommented:
Can you provide a file sample to make sure I understand what you are asking?

0
 
copiacapAuthor Commented:
Sorry - I thought I attached the file with the original post
Optimizer.txt
0
 
jeromeeCommented:
This will work to create the BCP file (TAB-separated):
perl -F'\t' -ane'BEGIN{<>} print join("\t",@F[0..$#F-1])."\n"' Optimizer.txt

After that I don't know the SQL Server command format to BCP a file in.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
copiacapAuthor Commented:
Hey Jeromee

I tried running the script but got the following error:

C:\Temp>perl -F'\t' -ane'BEGIN{<>} print join("\t",@F[0..$#F-1])."\n"' Optimizer.txt
> was unexpected at this time.


0
 
jeromeeCommented:
the Window/DOS is having a problem with the <>.
Here's workaround.
Create file called make_bcp.pl which will contain this:
<>;
while( <> ) {
   my @F = split/\t/;
   print join("\t",@F[0..$#F-1])."\n";
}

Then run:
    perl make_bcp.pl Optimizer.txt

Good luck!
0
 
copiacapAuthor Commented:
Thanks Jeromee - I think we're close. Attached is the result of the script you sent me (Optimizer_Formatted.txt) and what the file should look like (Optimizer_Formatted_Correct.txt). The script needs to find the row with the header for the data (name number symbol ...) and then have  that be the start of the file.
Optimizer-Formatted-Correct.txt
Optimizer-Formatted.txt
0
 
jeromeeCommented:
Hi copiacap,
I'll take a look tomorrow am when I'm close to a computer...
0
 
jeromeeCommented:
Hi copiacap,
The file Optimizer-Formatted-Correct.txt that you posted seems to have bogus value.
Now, I think I understand better what you want.... you want to skip the very long list of field descriptions, all the way to the header line, and then keep that header line as the first line of the output file.

If so, try this.

1 while( ($_=<>) !~ /^name\s+number\s+symbol\s+initial/ );
do {
   my @F = split/\t/;
   print join("\t",@F[0..$#F-1])."\n";
} while( <> )
0
 
copiacapAuthor Commented:
Ok one more tweak - the file has 16 columns. Rather than removing the last column which is what we're currently doing, it looks like sometimes the file doesn't put a blank for that last column. So some rows have 15 and some have 16. So what we need to do is append a \t if there are only 15 columns to make sure every row has 16 columns, that way the BCP loads it correctly.
0
 
jeromeeCommented:
Hi copiacap,
I think this is what you need.

1 while( ($_=<>) !~ /^name\s+number\s+symbol\s+initial/ );
do {
   my @F = split/\t/;
   print join("\t",@F[0..13])."\n";
} while( <> )

I replaced the $#F-1 by the hardcoded 13.

Good luck!
0
 
copiacapAuthor Commented:
Ok great, I think we need one more fix. We're dropping one column. I changed the script to be @F[0..14]. Only problem is sometimes that last column includes a newline. See the results when I run the script with @F[0..14]. So can we make sure it doesn't include any new line characters in the actual text except for the one we append to the end?
Optimizer-Formatted.txt
0
 
copiacapAuthor Commented:
One other request - can you add a statement at the beginning to make sure the input file exists and otherwise just exit. Otherwise the perl script hangs waiting for the file I guess
0
 
jeromeeCommented:
Try this...

my $input = $ARGV[0] || die "Please specify input file\n";
open(INPUT, $input) || die "Can't open input file $input: $!\n";
1 while( ($_=<INPUT>) !~ /^name\s+number\s+symbol\s+initial/ );
do {
   chomp;
   my @F = split/\t/;
   print join("\t",@F[0..13])."\n";
} while( <INPUT> );
close(INPUT);

0
 
copiacapAuthor Commented:
Thankes Jeromee - worked like a charm
0
 
jeromeeCommented:
Glad to hear that!

0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now