Re^2: Extract Elements from an array and insert into database

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 ||.

Comment on Re^2: Extract Elements from an array and insert into database Download Code

Replies are listed 'Best First'.
Re^3: Extract Elements from an array and insert into database by dsheroh (Monsignor) on Jun 29, 2007 at 06:42 UTC
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]

Replies are listed 'Best First'.

Re^3: Extract Elements from an array and insert into database
by dsheroh (Monsignor) on Jun 29, 2007 at 06:42 UTC

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]

?

NULL

@fields

($field1, $field2, $field3)

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]