Help & FAQs    Search BCF    Members    Groups    Events Calendar
Resend my activation email : Register : Log in 
BCF: Bike Chat Forums


File with data item keys convert to fixed format.

 Reply to topic
Bike Chat Forums Index -> The Geek Zone
View previous topic : View next topic  
Author Message

chris-red
Have you considered a TDM?



Joined: 21 Sep 2005
Karma :
PostPosted: 14:46 - 19 Oct 2015    Post subject: File with data item keys convert to fixed format. Reply with quote
Hi Guys,

I have a file in the format...

aaaa=xxxxx, bbbb=xxxxxxxxx, cccc=,xxxxxxxxx, dddd=xxxxxx
aaaa=xxxxx, bbbb=xxxxxxxxx, dddd=xxxxxx
bbbb=xxxxxxxxx, cccc=,xxxxxxxxx, dddd=xxxxxx
cccc=,xxxxxxxxx, dddd=xxxxxx

i.e. each data item is prefixed by a code identifying what item type it is. With a etc. being item tag and x being data.

I want it in something excel friendly,

e.g.

Code:
aaaa                   bbbb                  cccc           dddd
xxxx                   xxxxxxxxxxxx          xxxxxxxxxxx    xxxxxxxx
xxxx                   xxxxxxxxxxxx                         xxxxxxxx
                       xxxxxxxxxxxx          xxxxxxxxxxx    xxxxxxxx
                                             xxxxxxxxxxx    xxxxxxxx


Any ideas?
____________________
Well, you know what they say. If you want to save the world, you have to push a few old ladies down the stairs.
Skudd:- Perhaps she just thinks you are a window licker and is being nice just in case she becomes another Jill Dando.
WANTED:- Fujinon (Fuji) M42 (Screw on) lenses, let me know if you have anything.

Last edited by chris-red on 14:49 - 19 Oct 2015; edited 2 times in total
 Back to top
View user's profile Send private message You must be logged in to rate posts

Stowelly
Traffic Copper



Joined: 25 Oct 2009
Karma :
PostPosted: 14:49 - 19 Oct 2015    Post subject: Reply with quote
CSV ?

aaaa, bbbb, cccc, dddd,
xxxxx, xxxxxxxxx, xxxxxxxxx, xxxxxx,
xxxxx, xxxxxxxxx, xxxxxxxxx, xxxxxx,
xxxxx, xxxxxxxxx, xxxxxxxxx, xxxxxx,
xxxxx, xxxxxxxxx, xxxxxxxxx, xxxxxx,
 Back to top
View user's profile Send private message You must be logged in to rate posts

chris-red
Have you considered a TDM?



Joined: 21 Sep 2005
Karma :
PostPosted: 14:51 - 19 Oct 2015    Post subject: Reply with quote
Stowelly wrote:
CSV ?

aaaa, bbbb, cccc, dddd,
xxxxx, xxxxxxxxx, xxxxxxxxx, xxxxxx,
xxxxx, xxxxxxxxx, xxxxxxxxx, xxxxxx,
xxxxx, xxxxxxxxx, xxxxxxxxx, xxxxxx,
xxxxx, xxxxxxxxx, xxxxxxxxx, xxxxxx,


Not all records have all data items. it screws it up.
____________________
Well, you know what they say. If you want to save the world, you have to push a few old ladies down the stairs.
Skudd:- Perhaps she just thinks you are a window licker and is being nice just in case she becomes another Jill Dando.
WANTED:- Fujinon (Fuji) M42 (Screw on) lenses, let me know if you have anything.
 Back to top
View user's profile Send private message You must be logged in to rate posts

Stowelly
Traffic Copper



Joined: 25 Oct 2009
Karma :
PostPosted: 14:53 - 19 Oct 2015    Post subject: Reply with quote
its fine to have blank fields in csv just have xxxx,,xxx,,

or just use 0 / null
 Back to top
View user's profile Send private message You must be logged in to rate posts

chris-red
Have you considered a TDM?



Joined: 21 Sep 2005
Karma :
PostPosted: 14:58 - 19 Oct 2015    Post subject: Reply with quote
Stowelly wrote:
its fine to have blank fields in csv just have xxxx,,xxx,,

or just use 0 / null


The field tags are not present if the value is null, how do I assign which tag goes where?
____________________
Well, you know what they say. If you want to save the world, you have to push a few old ladies down the stairs.
Skudd:- Perhaps she just thinks you are a window licker and is being nice just in case she becomes another Jill Dando.
WANTED:- Fujinon (Fuji) M42 (Screw on) lenses, let me know if you have anything.
 Back to top
View user's profile Send private message You must be logged in to rate posts

Rogerborg
nimbA



Joined: 26 Oct 2010
Karma :
PostPosted: 15:47 - 19 Oct 2015    Post subject: Reply with quote
Sample data with the erroneous "cccc=," commas stripped but the whitespace around commas left in.

Code:
aaaa=xxxxx, bbbb=xxxxxxxxx, cccc=xxxxxxxxx, dddd=xxxxxx
aaaa=xxxxx, bbbb=xxxxxxxxx, dddd=xxxxxx
bbbb=xxxxxxxxx, cccc=xxxxxxxxx, dddd=xxxxxx
cccc=xxxxxxxxx, dddd=xxxxxx



Code:

use strict;
my $columnHeaders = 'aaaa,bbbb,cccc,dddd';

my @lookForTags = split(',', $columnHeaders);
my $numberOfTagsToLookFor = @lookForTags;

print $columnHeaders . "\n";

while(<STDIN>) {

    chomp;
    s/\s*,\s*/,/g;
   
    my @foundTags = split(",");
    my $foundTag = 0;
   
    my @foundTagAndData = split("=", $foundTags[$foundTag++]);
   
    for(my $i = 0; $i < $numberOfTagsToLookFor; ++$i)  {
   
        if(@foundTagAndData[0] eq $lookForTags[$i]) {
            print @foundTagAndData[1];
            @foundTagAndData = split('=', $foundTags[$foundTag++]);
        }
       
        if($i < $numberOfTagsToLookFor - 1) {
            print ",";
        }
    }
   
    print "\n";
}


Code:

$ perl create-csv.pl  < sampleData.txt
aaaa,bbbb,cccc,dddd
xxxxx,xxxxxxxxx,xxxxxxxxx,xxxxxx
xxxxx,xxxxxxxxx,,xxxxxx
,xxxxxxxxx,xxxxxxxxx,xxxxxx
,,xxxxxxxxx,xxxxxx


https://i.imgur.com/y6r5o7I.png
____________________
Biking is 1/20th as dangerous as horse riding.
GONE: HN125-8, LF-250B, GPz 305, GPZ 500S, Burgman 400 // RIDING: F650GS (800 twin), Royal Enfield Bullet Electra 500 AVL, Ninja 250R because racebike
 Back to top
View user's profile Send private message You must be logged in to rate posts

chris-red
Have you considered a TDM?



Joined: 21 Sep 2005
Karma :
PostPosted: 16:15 - 19 Oct 2015    Post subject: Reply with quote
Rogerborg wrote:
Sample data with the erroneous "cccc=," commas stripped but the whitespace around commas left in.

Code:
aaaa=xxxxx, bbbb=xxxxxxxxx, cccc=xxxxxxxxx, dddd=xxxxxx
aaaa=xxxxx, bbbb=xxxxxxxxx, dddd=xxxxxx
bbbb=xxxxxxxxx, cccc=xxxxxxxxx, dddd=xxxxxx
cccc=xxxxxxxxx, dddd=xxxxxx



Code:

use strict;
my $columnHeaders = 'aaaa,bbbb,cccc,dddd';

my @lookForTags = split(',', $columnHeaders);
my $numberOfTagsToLookFor = @lookForTags;

print $columnHeaders . "\n";

while(<STDIN>) {

    chomp;
    s/\s*,\s*/,/g;
   
    my @foundTags = split(",");
    my $foundTag = 0;
   
    my @foundTagAndData = split("=", $foundTags[$foundTag++]);
   
    for(my $i = 0; $i < $numberOfTagsToLookFor; ++$i)  {
   
        if(@foundTagAndData[0] eq $lookForTags[$i]) {
            print @foundTagAndData[1];
            @foundTagAndData = split('=', $foundTags[$foundTag++]);
        }
       
        if($i < $numberOfTagsToLookFor - 1) {
            print ",";
        }
    }
   
    print "\n";
}


Code:

$ perl create-csv.pl  < sampleData.txt
aaaa,bbbb,cccc,dddd
xxxxx,xxxxxxxxx,xxxxxxxxx,xxxxxx
xxxxx,xxxxxxxxx,,xxxxxx
,xxxxxxxxx,xxxxxxxxx,xxxxxx
,,xxxxxxxxx,xxxxxx


https://i.imgur.com/y6r5o7I.png


This looks promising, I can't see the picture, work block more or less everything.

Is it as simple as putting the specific headers in replacing a,b,c,d etc.? I have 117 are there any width restrictions in the input script?

This looks too easy I'm used to mainframes where you have to specify all manor of file/item lengths.

Cheers Thumbs Up
____________________
Well, you know what they say. If you want to save the world, you have to push a few old ladies down the stairs.
Skudd:- Perhaps she just thinks you are a window licker and is being nice just in case she becomes another Jill Dando.
WANTED:- Fujinon (Fuji) M42 (Screw on) lenses, let me know if you have anything.
 Back to top
View user's profile Send private message You must be logged in to rate posts

chris-red
Have you considered a TDM?



Joined: 21 Sep 2005
Karma :
PostPosted: 16:23 - 19 Oct 2015    Post subject: Reply with quote
It's running, the input file is 50Mb so this might take a while!
____________________
Well, you know what they say. If you want to save the world, you have to push a few old ladies down the stairs.
Skudd:- Perhaps she just thinks you are a window licker and is being nice just in case she becomes another Jill Dando.
WANTED:- Fujinon (Fuji) M42 (Screw on) lenses, let me know if you have anything.
 Back to top
View user's profile Send private message You must be logged in to rate posts

chris-red
Have you considered a TDM?



Joined: 21 Sep 2005
Karma :
PostPosted: 16:37 - 19 Oct 2015    Post subject: Reply with quote
One small issue,

It only picked up the first 2 data items, I'm guessing the script reads through each record and if the input data is in the different order to the headers in the script it will miss some out, anyway round this? I don't have a specific order of these fields nor do I know if they are in such an order (I think they are though.)
____________________
Well, you know what they say. If you want to save the world, you have to push a few old ladies down the stairs.
Skudd:- Perhaps she just thinks you are a window licker and is being nice just in case she becomes another Jill Dando.
WANTED:- Fujinon (Fuji) M42 (Screw on) lenses, let me know if you have anything.
 Back to top
View user's profile Send private message You must be logged in to rate posts

chris-red
Have you considered a TDM?



Joined: 21 Sep 2005
Karma :
PostPosted: 16:42 - 19 Oct 2015    Post subject: Reply with quote
I also tried using just a space selection of about 8 or so fields I was interested in this returned nothing, do I need to have a heading for each field?
____________________
Well, you know what they say. If you want to save the world, you have to push a few old ladies down the stairs.
Skudd:- Perhaps she just thinks you are a window licker and is being nice just in case she becomes another Jill Dando.
WANTED:- Fujinon (Fuji) M42 (Screw on) lenses, let me know if you have anything.
 Back to top
View user's profile Send private message You must be logged in to rate posts

Rogerborg
nimbA



Joined: 26 Oct 2010
Karma :
PostPosted: 17:01 - 19 Oct 2015    Post subject: Reply with quote
chris-red wrote:
if the input data is in the different order to the headers in the script it will miss some out, anyway round this?


Out-of-order sample data.
Code:
aaaa=xxxxx, cccc=xxxxxxxxx, bbbb=xxxxxxxxx, dddd=xxxxxx
bbbb=xxxxxxxxx, aaaa=xxxxx, dddd=xxxxxx
dddd=xxxxxx, cccc=xxxxxxxxx, bbbb=xxxxxxxxx
cccc=xxxxxxxxx, dddd=xxxxxx


Less efficient script.

Code:
use strict;
my $columnHeaders = 'aaaa,bbbb,cccc,dddd';

my @lookForTags = split(',', $columnHeaders);

print $columnHeaders . "\n";

while(<STDIN>) {

    chomp;
    s/\s*,\s*/,/g;
   
    my @foundTags = split(",");
    my $foundTag = 0;
   
    for(my $i = 0; $i < scalar @lookForTags; ++$i)  {
   
        foreach my $foundTag (@foundTags) {
            my @foundTagAndData = split("=", $foundTag);
           
            if(@foundTagAndData[0] eq $lookForTags[$i]) {
                print @foundTagAndData[1];
                last;
            }
        }
           
        if($i < scalar @lookForTags - 1) {
            print ",";
        }
    }
   
    print "\n";
}

____________________
Biking is 1/20th as dangerous as horse riding.
GONE: HN125-8, LF-250B, GPz 305, GPZ 500S, Burgman 400 // RIDING: F650GS (800 twin), Royal Enfield Bullet Electra 500 AVL, Ninja 250R because racebike
 Back to top
View user's profile Send private message You must be logged in to rate posts

chris-red
Have you considered a TDM?



Joined: 21 Sep 2005
Karma :
PostPosted: 17:25 - 19 Oct 2015    Post subject: Reply with quote
Great thanks that looks good, last another thing, can I write this to a file?

Currently I'm running this from a Command Line and it is putting the output in the command line, which isn't big enough.
____________________
Well, you know what they say. If you want to save the world, you have to push a few old ladies down the stairs.
Skudd:- Perhaps she just thinks you are a window licker and is being nice just in case she becomes another Jill Dando.
WANTED:- Fujinon (Fuji) M42 (Screw on) lenses, let me know if you have anything.
 Back to top
View user's profile Send private message You must be logged in to rate posts

Stowelly
Traffic Copper



Joined: 25 Oct 2009
Karma :
PostPosted: 17:30 - 19 Oct 2015    Post subject: Reply with quote
Rogerborg wrote:

[code]
$ perl create-csv.pl < sampleData.txt




the < will output to file
 Back to top
View user's profile Send private message You must be logged in to rate posts

chris-red
Have you considered a TDM?



Joined: 21 Sep 2005
Karma :
PostPosted: 17:30 - 19 Oct 2015    Post subject: Reply with quote
Stowelly wrote:
Rogerborg wrote:

[code]
$ perl create-csv.pl < sampleData.txt




the < will output to file


That's the input file.
____________________
Well, you know what they say. If you want to save the world, you have to push a few old ladies down the stairs.
Skudd:- Perhaps she just thinks you are a window licker and is being nice just in case she becomes another Jill Dando.
WANTED:- Fujinon (Fuji) M42 (Screw on) lenses, let me know if you have anything.
 Back to top
View user's profile Send private message You must be logged in to rate posts

chris-red
Have you considered a TDM?



Joined: 21 Sep 2005
Karma :
PostPosted: 17:32 - 19 Oct 2015    Post subject: Reply with quote
Never mind worked it out Thumbs Up

> text.csv
____________________
Well, you know what they say. If you want to save the world, you have to push a few old ladies down the stairs.
Skudd:- Perhaps she just thinks you are a window licker and is being nice just in case she becomes another Jill Dando.
WANTED:- Fujinon (Fuji) M42 (Screw on) lenses, let me know if you have anything.
 Back to top
View user's profile Send private message You must be logged in to rate posts

chris-red
Have you considered a TDM?



Joined: 21 Sep 2005
Karma :
PostPosted: 17:40 - 19 Oct 2015    Post subject: Reply with quote
Big thanks Rog, Thumbs Up Got some decent output now.
____________________
Well, you know what they say. If you want to save the world, you have to push a few old ladies down the stairs.
Skudd:- Perhaps she just thinks you are a window licker and is being nice just in case she becomes another Jill Dando.
WANTED:- Fujinon (Fuji) M42 (Screw on) lenses, let me know if you have anything.
 Back to top
View user's profile Send private message You must be logged in to rate posts

Stowelly
Traffic Copper



Joined: 25 Oct 2009
Karma :
PostPosted: 17:51 - 19 Oct 2015    Post subject: Reply with quote
my bad Very Happy
 Back to top
View user's profile Send private message You must be logged in to rate posts

Rogerborg
nimbA



Joined: 26 Oct 2010
Karma :
PostPosted: 09:11 - 20 Oct 2015    Post subject: Reply with quote
No problem, now I need to shower to get the stench of Perl off of me.
____________________
Biking is 1/20th as dangerous as horse riding.
GONE: HN125-8, LF-250B, GPz 305, GPZ 500S, Burgman 400 // RIDING: F650GS (800 twin), Royal Enfield Bullet Electra 500 AVL, Ninja 250R because racebike
 Back to top
View user's profile Send private message You must be logged in to rate posts

nowhere.elysium
The Pork Lord



Joined: 02 Mar 2009
Karma :
PostPosted: 10:52 - 20 Oct 2015    Post subject: Reply with quote
Rogerborg wrote:
No problem, now I need to shower to get the stench of Perl off of me.
It never goes away. Not entirely.
____________________
'10 SV650SF, '83 GS650GT (it lives!), Questionable DIY dash project, 3D Printer project, Lasercutter project
 Back to top
View user's profile Send private message You must be logged in to rate posts
Old Thread Alert!

The last post was made 11 years, 11 days ago. Instead of replying here, would creating a new thread be more useful?
  Display posts from previous:   
This page may contain affiliate links, which means we may earn a small commission if a visitor clicks through and makes a purchase. By clicking on an affiliate link, you accept that third-party cookies will be set.

Post new topic   Reply to topic    Bike Chat Forums Index -> The Geek Zone All times are GMT + 1 Hour
Page 1 of 1

 
 You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You cannot download files in this forum

Read the Terms of Use! - Powered by phpBB © phpBB Group
 
Debug Mode: ON - Server: birks (www) - Page Generation Time: 0.12 Sec - Server Load: 0.45 - MySQL Queries: 13 - Page Size: 111.98 Kb