1

I am writing a perl script where it connect to SQL server. I've compose a query that will get the data that I need. But in perl, how can I export the data from SQL into tab delimited txt file using perl ?

here is my sample script:

my $sql = "SELECT top (20) [code] AS Code
      ,Replace(Replace(Replace(Replace(Replace(Replace(Replace
      (Replace(Replace(Replace(Replace
      ([name],'\“','\"'),'\”','\"'),'<= ','&le;'),'>=','&ge;'),'<','&lt;'),'>','&gt;'),CHAR(10),'<br>'),'\n',' '),CHAR(13),' '),'–','-'),'’',''+NCHAR(39)+'') AS ShortDesc
      ,Replace(Replace(Replace(Replace(Replace(Replace(Replace
      (Replace(Replace(Replace(Replace
      ([description],'\“','\"'),'\”','\"'),'<= ','&le;'),'>=','&ge;'),'<','&lt;'),'>','&gt;'),CHAR(10),'<br>'),'\n',' '),CHAR(13),' '),'–','-'),'’',''+NCHAR(39)+'') AS LongDesc
      ,CASE WHEN isobsolete = 0 THEN 'NULL' ELSE 'Y' END AS Obsolete
      
FROM (
     SELECT ROW_NUMBER() OVER(PARTITION BY code ORDER BY effectivefromdate DESC) rn, * 
     FROM [CodingSuite_STG].[Codes].[Hcpcs] ) cs
     WHERE  rn=1 
     order by code asc";
     
my $sth = $dbh->prepare( $sql );

 
#Execute the statement
$sth->execute();


while ( my @row = $sth->fetchrow_array ) {
     print "@row\n";
}

#Close the connection
$sth->finish();
$dbh->disconnect();
  • Probably the easiest way would be to just use `bcp`, exporting data to files is what it does best. – Stu May 12 '21 at 13:34
  • Hello can you elaborate your answer please –  May 12 '21 at 14:13
  • Your question is not complete. Some DBs allow to extract information into external file and specify delimiter. Please check [SELECT ... INTO Statement](https://dev.mysql.com/doc/refman/5.6/en/select-into.html). There is a very high chance that you do not invent something what is provided by DB. – Polar Bear May 12 '21 at 20:21
  • Skip the Perl part and let the database do it for you so it handles all the weird cases: https://stackoverflow.com/questions/425379/how-to-export-data-as-csv-format-from-sql-server-using-sqlcmd/425463 – brian d foy May 12 '21 at 20:48

1 Answers1

-1

You can try and use join(). Something along the lines of:

...

my $fh;

if (!open($fh, '>', "/path/to/file")) {
  die($!);
}

while (my @row = $sth->fetchrow_array) {
  print($fh, join("\t", @row) . "\n");
}

close($fh);

...
sticky bit
  • 36,626
  • 12
  • 31
  • 42