Here are the things you'll need to read in order to solve your problem:
Text::CSV_XS - read about this module and the sep_char option. Use parse and fields methods to parse the data
DBI - Then use prepare and Placeholders to insert your data.
| [reply]
[d/l]
[select] |
So....what did you try so far? | [reply] |
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. | [reply] |
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] |
# 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);
}
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] |