Solved

how to extract data from a table into a fixed length file

Posted on 2014-10-03
10
49 Views
Last Modified: 2015-12-03
Hi,

I have to extract data from table A. Now A has about 200 columns. Now when I am writing data out into a file, every field should have a pre-specified length. How should I do it in perl?  Any ideas?

like for example -  column id should be 10 characters, name should be 30 characters and address should be 40. So in the output file, name should start from 11th position and address from 1st position

Regards,
Sayantan
0
Comment
Question by:sunny82
  • 5
  • 4
10 Comments
 

Author Comment

by:sunny82
ID: 40360395
I mean address from 41st position
0
 
LVL 11

Accepted Solution

by:
tel2 earned 500 total points
ID: 40360487
Hi Sunny,

You can use the 'printf()' function for formatted printing.  For example:
    $id = 123;
    $name = 'My Name';
    $address = 'My Address';
    printf("%10s%-30s%-40s", $id, $name, $address);

Note:
- 's' is for strings
- '-' is for left justification.

Or, if you prefer to assign the output to a variable, you can use 'sprintf()', like this:
    $output = sprintf("%10s%-30s%-40s", $id, $name, $address);

You can also print directly to your output file, as you would with a normal 'print'.

For more help on these functions, type:
    perldoc -f printf
and:
    perldoc -f sprintf
and here's some useful info:
    http://www.sunsite.ualberta.ca/Documentation/Misc/perl-5.6.1/pod/perlfunc/sprintf.html
0
 

Author Comment

by:sunny82
ID: 40363688
Hi,

Many thanks for your response. I used that and it works fine. But the problem is, I have over 200 columns in the table and for each column, I have to write sprintf("%10s%-30s%-40s", $id, $name, $address), corresponding to their widths, which becomes very unwieldy and difficult to maintin.

Is there a way to use config file such that I only need to change the config file once(if there is any field width change in any column) and I can generate the file accordingly?

The config file should just have column name and its width.
0
 
LVL 11

Expert Comment

by:tel2
ID: 40365110
If you put fields in this format in a file called config.txt:
id,10
name,30
address,40

Open in new window

You could then do your processing like this:

open CONFIG, "<config.txt" or die "Can't open config file!";
while (<CONFIG>)
{
        chomp;
        ($name, $length) = split(',');
        $format .= "%${length}s";   # This will left-justify all fields
}

# Populate the @values array with the column values of a
# a row of your table.  I'll just hard code it here, but you
# will probably read a record from the table.
@values = (123, 'My Name', 'My Address');

printf($format, @values);

Open in new window

;
0
 

Author Comment

by:sunny82
ID: 40366276
I am getting the output like this

%10 s%30 s123


below is my code

use strict;

my $core_config = "$ENV{'CFG'}/menir-core-config-test.txt";
my $name;
my $length;
my $format;

open CONFIG, "<$core_config" or die "Can't open config file!";
while (<CONFIG>)
{
        chomp;
        ($name, $length) = split(',');
        $format .= "%${length}s";   # This will left-justify all fields
}

my $outfile = "$ENV{'DATA'}/CORE.txt";

my @row = (123, 'My Name', 'My Address');
printf($format, @row);
----------------------------------------------

this is my config ->

id,10
name,30
address,10
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 11

Expert Comment

by:tel2
ID: 40367307
Hi Sunny,

Your version of the code works for me.  All I changed was:
    my $core_config = "$ENV{'CFG'}/menir-core-config-test.txt";
to:
    my $core_config = 'config.txt';
so it would work in my environment, and I got this output:
       123                       My Name                              My Address

Open in new window


Question:
Does my version of the code work for you (if you just copy/paste it from my previous post)?

tel2
0
 
LVL 28

Expert Comment

by:FishMonger
ID: 40369571
Specifying the field width as part of the csv data is ok, but doesn't scale well.

A better approach would be to use an actual config file for the format specification.  There are a number of config modules to choose from on CPAN.  Here's an example using a YAML module/format.

config.yml
fields:  # list of fields in desired order
    - id
    - name
    - address
    - salary

---  # field format specifications
id:
    width: 10
    justify: '-'
    type: d

name:
    width: 30
    type: s

address:
    width: 30
    type: s

salary:
    width: 12
    type: f
    precision: .2

Open in new window


Test script:
#!/usr/bin/perl

use 5.010;
use strict;
use warnings;
use YAML::Tiny;

my $conf   = YAML::Tiny->read( 'config.yml' );
my $fields = $conf->[0]{fields};
my $format;

foreach my $field ( @$fields ) {
    no warnings 'uninitialized';

    $format .= '%' . $conf->[1]{$field}{justify}
                   . $conf->[1]{$field}{width}
                   . $conf->[1]{$field}{precision}
                   . $conf->[1]{$field}{type};
}

my @row = (123, 'My Name', 'My Address', 12.5);
printf($format, @row);

Open in new window


Output:
D:\test>sunny82.pl
123                              My Name                    My Address       12.50
0
 

Author Comment

by:sunny82
ID: 40382966
Hi,

Sorry for replying a bit late.

@tel2: The output I am getting is "%10 s%30 s       123"

The code is exactly the same as your code, word to word.

Is it because of tis statement ?

$format .= "%${length}s";   # This will left-justify all fields

@FishMonger: @INC does not have YAML::Tiny. Can we use some other module?
0
 
LVL 11

Expert Comment

by:tel2
ID: 40383019
Hi Sunny,

"> The code is exactly the same as your code, word to word."
Perhaps, but the code you pasted above is not the same as mine.
Please copy/paste my code to your machine (again), run it, then copy/paste my code and the results here for analysis.

"> Is it because of tis statement ?
> $format .= "%${length}s";   # This will left-justify all fields"

Dunno.  That statement works in my Perl 5.10 Linux environment I'm using, in both your version and my version of my code.

I doubt this is an issue, but what version of Perl are you using?  Type "perl -v" and paste the output here.

Thanks.
tel2
0
 
LVL 11

Expert Comment

by:tel2
ID: 40383408
What operating system are you running, Sunny?  I'm assuming UNIX/Linux, below.

Further to my post above, here's an alternative way to write that format line (line #6 in my 2nd post):
    $format .= '%' . $length . 's';
If that doesn't work in my script, please run each of the following one-liners from the Linux command line, and post the command and the output here:
    perl -e '$length=12;$format .= "%${length}s";print $format'

    perl -e '$length=12;$format .= "%" . $length . "s";print $format'

When you copy/paste my code back here for analysis (see my last post for details), please use this UNIX/Linux command to display the code before copying it:
    cat -vet the-script-name-of-my-version-of-the-code.pl
Hopefully that will show if there have been any strange characters inserted during the copy/paste to your system.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now