in reply to Extract Elements from an array and insert into database

It sounds like it should be easily solved with Perl, provided you have a way for the program to determine when to group elements in pairs and when to group them as triads. Without a way to do that, the task is impossible in any language. The only rule you have provided for making that distinction is "if the first field is DG306AE25, then there are three, otherwise there are two", which I rather doubt to be sufficient for handling the full set of your real data.
  • Comment on Re: Extract Elements from an array and insert into database

Replies are listed 'Best First'.
Re^2: Extract Elements from an array and insert into database
by parshtml (Initiate) on Jun 28, 2007 at 18:20 UTC
    Hello monks, I am thankful to all of you who have replied. I was able to modify the string in such a way that when if there is an & sign it means that it is a different group now it is possible to say when there is a pair and when it is triads. But still I am confused on how will I tell perl that when you encounter & you need to insert it into another line ZJ224HF|Intersil Corp&ZJ224HU|Intersil Corp&ZJ224LA|Intersil Corp&ZJ224LB|Intersil Corp&ZJ224LC|Intersil Corp&ZJ224LD|Intersil Corp&ZJ224LF|Intersil Corp&ZJ224LU|Intersil Corp&MCT3A65P100F2|Intersil Corp||D0035591&MCT3D65P100F2|Intersil Corp||D0035591&MCTA65P100F1|Intersil Corp||D0035545&MCTA75P60E1|Intersil Corp||D0035542&MCTG35P60F1|Intersil Corp||D0035549&MCTV35P60F1D|Intersil Corp||D0035556&MCTV65P100F1|Intersil Corp||D0035545&MCTV75P60E1|Intersil Corp||D0035542&ACS102-5T1-TR|ST Microelectronics||D8128653&ACS102-5TA|ST Microelectronics||D8128653&ACS102-5T1|ST Microelectronics||D8128653&SG1400EX25|Toshiba America Electronic Components, Inc.||D2722193&SG3000JX26G|Toshiba America Electronic Components, Inc.&SHR400EX25|Toshiba America Electronic Components, Inc.||D2722200&SG6000JX28|Toshiba America Electronic Components, Inc.&SDM170HK2|Silicon Power Corporation&SDM270HK2|Silicon Power Corporation&. Here we can see that an & says that it is a new data and the 3rd column is differentiated through a ||.
[reply]
[d/l]
      Ah, well then. If you have control over the delimiters, then that definitely makes things easy. Let's go with what you used there, but always use a single | to delimit fields instead of doubling it between the second and third fields (when a third field is present).

      Some basic code to split all the data up and insert it into a database would look something like: 

      # This code assumes that you've already initialized a
# database handle, $dbh, and your string of data is in
# $input
my $sth = $dbh->prepare('INSERT INTO my_table (field1, field2, field3)
+ VALUES (?, ?, ?);');
my @records = split '&', $input;
foreach my $record (@records) {
  my @fields = split '\|', $record;
  $sth->execute(@fields);
}

      [download]
      If there are only two fields in the record, the database placeholders (the ?s in the SQL text) should convert the nonexistent third element of @fields into a NULL in the database. If you get an error about not having enough values to bind, replace both occurrences of @fields with ($field1, $field2, $field3) to force it to always pass three values. (The code is untested, but simple enough that I should have gotten it right...)

      Note the backslash escape in the second split. The | character is special and you'll get every character individually if you split on '|' instead of '\|'.

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom