0

I am try to use Perl language to interact with Quickbase ,I used the below query to export a data table into a text file but I am not getting right format I want, any thoughts? Or if there is another language easier to interact with Quickbase?

@records = $qdb->doQuery($dbid,"{0.CT.''}","6.7.8.9"); 
$record_count = @records;

foreach $record (@records) {

  print MYFILE "|";

  foreach $field (keys %$record){
    if ($field eq "ColumnA") {
      print MYFILE "\"";
      print MYFILE " $field : $record->{$field}";
      print MYFILE "\"";   
    }

    if ($field eq "ColumnB") {
      print MYFILE "\"";
      print MYFILE "$field : $record->{$field}";
      print MYFILE "\"";
    }

    if ($field eq "ColumnC") {
      print MYFILE "\"";
      print MYFILE "$field : $record->{$field}";
      print MYFILE "\"";
    }

    if ($field eq "ColumnD") {
      print MYFILE "\"";
      print MYFILE "$field : $record->{$field}";
      print MYFILE "\"";
    }   
  }

  print MYFILE "\n";    
}

close LOGFILE;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ray83
  • 11
  • 1
    it might help if you describe the format you want, vs. the format it's currently giving you. – ADyson Mar 23 '17 at 16:45
  • 1
    There is almost certainly a way to get the output that you want. But as you don't tell anything about a) the format of the `$record` hash or b) the output format that you want, it's hard to see how we can be any help at all. – Dave Cross Mar 23 '17 at 16:57

1 Answers1

4

Wondering, for what kind of answer type do you looking for? But...

I am try to use Perl language to interact with Quickbase,

That's great. Perl is very powerful and suitable for (nearly) any task.

I used the below query to export a data table into a text file

Not very concise code. It is probably a legacy code from an Excel or BASIC person. Some comments:

  • the code doing the same actions for every field. So, why do you need the if statemenents?
  • Also, why need break each print into 3 separate prints?
  • why do you need the | at the beginning of the line?
  • you probably want to close MYFILE instead of the LOGFILE.

others

  • it is strange to print to every cell the field_name: field_value, instead of create the column header, but YMMV - so maybe you need this.
  • it is better to use lexical filehandles, like $myfile instead of the MYFILE
  • the foreach could be written as for :)
but I am not getting right format I want, any thoughts?

i'm unable to tell anything about the your wanted format, mainly because:

  • you didn't said anything about what format do you want to get
  • and, unfortunately, my crystal globe is on the scheduled maintenance. :)
Or if there is another language easier to interact with Quickbase?

Probably not.

  • The quickbase has an API for the access, (you can learn about it here, and every language (using some libraries) just does the bridge. For the perl it is the HTTP::QuickBase module. Did you read the doc?
  • Perl is extremely powerful, so anyone can write very concise code. Just need learn the language (as any other one). (Unfortunately, I'am also closer to beginners as experts.)

The above code is could be reduced to:

for my $record ($qdb->doQuery($dbid,"{0.CT.''}","6.7.8.9")) {
    print MYFILE '|',
                 join('|', map {
                           '"' . $_ . ': ' . $_->{field} . '"'
                      } keys %$record
                  ), "\n";
}

And will do exactly as the above.

But need to tell, it is still wrong solution. For example:

  • need cope with the quoting e.g. the "cell content".
  • but also, the cell contents could contain also the " character, so you need espace them. Here are more escaping techniques for the CSV files, one of is doubling the quote character (usually the "). Or prepend them with \. And much more possible problems, like "new line" characters \n in the cells and so on.

To avoid CSV quoting/escaping hell and other possible problems with CSV generation, you should to use the Text::CSV module. It's been developed in the last 20 years, so it is very long time/hard/stress tested module. You could to use it as:

use Text::CSV;
use autodie;

my $csv = Text::CSV->new ( { sep_char => '|', binary => 1 } ) #if you really want use the '|' instead of the standard comma.
               or die "Cannot use CSV: ".Text::CSV->error_diag ();
open $fh, '>', 'some.csv';
$csv->print( $fh, [map { $_->{field} } keys %$_]) for @$records;
close $fh;

Of course, the code is not tested. So, what next?

Community
  • 1
  • 1
clt60
  • 62,119
  • 17
  • 107
  • 194
  • Thank you so much for the answers , I am still infant in perl that's why I used some pre written script to get csv | delimited file . I will try to use text ::csv and the code suggested and see what will happen I will let you know – Ray83 Mar 25 '17 at 16:06
  • If someone can guide me to good materials for learning perl quickly – Ray83 Mar 25 '17 at 16:12
  • @Rakan you can use this online (or download) http://modernperlbooks.com/books/modern_perl_2014/ but you can buy form Addison Wesley: Learning perl, Intermediate perl and Mastering perl. Also here is many good blogs. Unfortunately, here are still too many _OLD_ sites, which teaches the _not very modern perl_ :( (for ex: using the very old CGI) Anyway, please read [tour] and [help] - asking for external resources is forbidden on this site. – clt60 Mar 25 '17 at 16:25
  • Thanks a lot jm666 for your help , I have tried the below code but i am getting only ||||||| with no data !! : – Ray83 Mar 27 '17 at 02:24
  • @records = $qdb->doQuery($dbid,"{0.CT.''}","6.7.8.9"); $record_count = @records; print STDERR "Number of records returned for 01-29-2016 search is $record_count\n"; my $csv = Text::CSV->new ( { sep_char => '|', binary => 1 } ) #if you really want use the '|' instead of the standard comma. or die "Cannot use CSV: ".Text::CSV->error_diag (); open $fh, '>', 'C:\\Strawberry\\Quickbase_Log_files\\some.csv'; $csv->print( $fh, [map { $_->{field} } keys %$_]) for @$records; close $fh; – Ray83 Mar 27 '17 at 02:28