How to export full-text files with SQL?

There are an easy way to import/export full-text fields as files?

• that solve the problem of "load as multiple lines".
  Trying with SQL's COPY I can only to transform full-file into full-table, not into a single text field, because each line from COPY is a raw.

• that solve the save-back problem, to save the full XML file in the filesystem, without changes in bynary representation (preserving SHA1), and without other exernal procedures (as Unix sed use).

The main problem is on export, so this is the title of this page.

PS: the "proof of same file" in the the round trip — import, export back and compare with original — can be obtained by sha1sum demonstration; see examples below. So, a natural demand is also to check same SHA1 by SQL, avoiding to export on simple check tasks.

---

All examples

1. Import a full text into a full-table (is not what I need),
   and test that can export as the same text.
   PS: I need to import one file into one field and one row.

2. Transform full table into one file (is not what I need)
   and test that can export as same text.
   PS: I need one row (of one field) into one file.

3. Calculate the hash by SQL, the SHA1 of the field.
   Must be the same when compare ... Else it is not a solution for me.

The folowing examples show each problem and a non-elegant workaround.

1. Import

CREATE TABLE ttmp (x text); COPY ttmp FROM '/tmp/test.xml' ( FORMAT text ); -- breaking lines lines COPY (SELECT x FROM ttmp) TO '/tmp/test_back.xml' (format TEXT); 

Checking that original and "back" have exactly the same content:

sha1sum /tmp/test*.*   570b13fb01d38e04ebf7ac1f73dfad0e1d02b027  /tmp/test_back.xml   570b13fb01d38e04ebf7ac1f73dfad0e1d02b027  /tmp/test.xml 

PS: seems perfect, but the problem here is the use of many rows. A real import-solution can import a file into a one-row (and one field). A real export-solution is a SQL function that produce test_back.xml from a single row (of a single field).

2. Transform full table into one file

Use it to store XML:

CREATE TABLE xtmp (x xml); INSERT INTO  xtmp (x)    SELECT array_to_string(array_agg(x),E'\n')::xml FROM ttmp ; COPY (select x::text from xtmp) TO '/tmp/test_back2-bad.xml' ( FORMAT text ); 

... But not works as we can check by sha1sum /tmp/test*.xml, not produce the same result for test_back2-bad.xml.

So do also a translation from \n to chr(10), using an external tool (perl, sed or any other)
perl -p -e 's/\\n/\n/g' /tmp/ata_back2.xml > /tmp/test_back2-good.xml

Ok, now test_back2-good.xml have the same hash ("570b13fb..." in my example) tham original. Use of Perl is a workaround, how to do without it?

3. The SHA1 of the field

SELECT encode(digest(x::text::bytea, 'sha1'), 'hex') FROM xtmp; 

Not solved, is not the same hash tham original (the "570b13fb..." in my example)... Perhaps the ::text enforced internal representation with \n symbols, so a solution will be direct cast to bytea, but it is an invalid cast. The other workaround also not is a solution,

SELECT encode(digest( replace(x::text,'\n',E'\n')::bytea, 'sha1' ), 'hex')  FROM xtmp 

... I try CREATE TABLE btmp (x bytea) and COPY btmp FROM '/tmp/test.xml' ( FORMAT binary ), but error ("unknown COPY file signature").

1 Answers

Answers 1

COPY isn't designed for this. It's meant to deal with table-structured data, so it can't work without some way of dividing rows and columns; there will always be some characters which COPY FROM interprets as separators, and for which COPY TO will insert some escape sequence if it finds one in your data. This isn't great if you're looking for a general file I/O facility.

In fact, database servers aren't designed for general file I/O. For one thing, anything which interacts directly with the server's file system will require a superuser role. If at all possible, you should just query the table as usual, and deal with the file I/O on the client side.

That said, there are a few alternatives:

• The built-in pg_read_file() function, and pg_file_write() from the adminpack module, provide the most direct interface to the file system, but they're both restricted to the cluster's data directory (and I wouldn't recommend storing random user-created files in there).
• lo_import() and lo_export() are the only built-in functions I know of which deal directly with file I/O and which have unrestricted access to the server's file system (within the constraints imposed by the host OS), but the Large Object interface is not particularly user-friendly....
• If you install the untrusted variant of a procedural language like Perl (plperlu) or Python (plpythonu), you can write wrapper functions for that language's native I/O routines.
• There isn't much you can't accomplish via COPY TO PROGRAM if you're determined enough - for one, you could COPY (SELECT 1) TO PROGRAM 'mv <source_file> <target_file>' to work around the limitations of pg_file_write() - though this blurs the line between SQL and external tools somewhat (and whoever inherits your codebase will likely not be impressed...).

Read More

Javascript Export - Instantiate Variables/Objects

I am trying to modify the homebridge-wink3 code to add a variable so I can track the state in. I have 5 shades in my house, so each instance of the variable needs to be unique.

In the shade.js file, it has;

exports.default = ({ Characteristic, Service }) => {   return {     type: "shade",     group: "shades",     services: [{       service: Service.WindowCovering,       characteristics: [{         characteristic: Characteristic.TargetPosition,         get: (state, desired_state) => desired_state.position * 100, 

I'd like to change the get (and set elsewhere in the code) so it uses a local variable lastState to track state.

    get: (state, desired_state) => {                  if (desired_state.position != null) {                         lastState = desired_state.position * 100;                 }                 else if (lastState != undefined) {                         desired_state.position = lastState / 100;                 }                 return lastState; 

I've spent hours trying to work out how to have the code maintain individual variables per shade (object instance), but they always seem to be sharing the same instance of the lastState variable.

What do I need to do here?

See https://github.com/sibartlett/homebridge-wink3/blob/master/src/devices/shade.js for the code.

2 Answers

Answers 1

Important: What I understand to your question is that you want to clone an object (lastState or the object with the get and set method).

Suppose I have an object A like this:

var A = {       aVariable: "Panem et circencem",       aMethod: function () {         return (["Veni", "vidi", "vici"]);       }     }; 

Now, suppose that I want to clone the object A to an object B.

function clone(obj) {   if (null == obj || "object" != typeof obj) return obj;   var copy = obj.constructor();   for (var attr in obj) {     if (obj.hasOwnProperty(attr)) copy[attr] = obj[attr];   }   return copy; }  var B = clone(A); 

This is a sample example:

      var A = {    aVariable: "Panem et circencem",    aMethod: function () {      return (["Veni", "vidi", "vici"]);    }  };  function clone(obj) {    if (null == obj || "object" != typeof obj) return obj;    var copy = obj.constructor();    for (var attr in obj) {      if (obj.hasOwnProperty(attr)) copy[attr] = obj[attr];    }    return copy;  }    var B = clone(A);  B.aVariable = "Ad gloriam";  console.log(B);  console.log (A);

Then, you can clone/copy all your object in order to have some distinctives properties in your objects or clone the lastState in your code. I do not understood this part of your question, excuse me.

Note: this question try to answer the question. If I do not understood the question, please tell me a comment.

Also notice: If I do not answer the question, your are free to use the code poste above and copy my post in order to answer the question.

Likewise note: If you have a question, tell me a comment.

Answers 2

You can declare lastState just above the return statement,

let lastState; return {   type: "shade",   group: "shades", 

or above the export statement,

let lastState; export default ({ Characteristic, Service }) => { 

if you declare lastState in the same scope as where you create the 5 instances then they will all share the same lastState.

Read More

Sql Query results to CSV to a specified file path after stored procedure execution

I'm trying to execute a stored procedure (which i know works) in T-SQL that then gets those results into a CSV file and puts that file into a directory. I'm not sure how to formulate that query, exactly though. Here's what i've tried thus far to no avail:

EXECUTE CLR_ExportQueryToCSV @QueryCommand = 'execute databaseName.dbo.StoredProcedureName',                           @FilePath = 'C:\Directory',                           @FileName = '\FileToExport.csv',                           @IncludeHeaders = 1 

I realize CLR_ExportQueryToCSV doesn't exist. Is there any system stored procedure that will do what i'm wanting?

7 Answers

Answers 1

bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S SERVERNAME -T 

docs

Answers 2

Invoking CMD is one way to achieve it and can automate it

Declare @sql VARCHAR(max) declare @CsvFile NVARCHAR(500) DECLARE @cmd NVARCHAR(4000)  set @sql = 'Exec [dbo].[Usp_CSVextract]' set @CsvFile = 'C:\Test.csv' SET @cmd =  'bcp '+CHAR(34)+@sql+CHAR(34)+' queryout '+CHAR(34)+@CsvFile+CHAR(34)+' -S '+@@servername +' -c -t'+CHAR(34)+','+CHAR(34)+' -r'+CHAR(34)+'\n'+CHAR(34)+' -T'  exec master.dbo.xp_cmdshell @cmd 

Answers 3

Unfortunately there's no generic/supported method in SQL Server to do what you're asking.

If you're simply looking for a way to dump the results of a SQL query to CSV then I'd be more inclined to either write an SSIS package to do the job or a C# console app, either of which can be scheduled.

Here's an example in C#:

static void Main(string[] args) {     WriteQueryResultsToCsv(@"c:\SqlResults.csv",          "MyDbConnectionStringName",          "select * from MyTable where x > @x",         new SqlParameter("@x", SqlDbType.Int) {Value = 1}); }  private static void WriteQueryResultsToCsv(string csvPath, string connectionStringName, string sql, params SqlParameter[] parameters) {     // Requires reference to System.Configuration     var connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;      using (var db = new SqlConnection(connectionString))     using (var cmd = new SqlCommand(sql, db))     {         db.Open();         cmd.Parameters.AddRange(parameters);          using (var dr = cmd.ExecuteReader())         using (var dw = new StreamWriter(csvPath))         using (var csv = new CsvWriter(dw)) // Requires CsvHelper package from NuGet         {             // Write column headers             for (var c = 0; c < dr.FieldCount; c++)                 csv.WriteField(dr.GetName(c));              // Write data rows             while (dr.Read())             {                 csv.NextRecord();                 for (var c = 0; c < dr.FieldCount; c++)                 {                     csv.WriteField(dr.GetValue(c).ToString());                 }             }         }     } } 

Answers 4

There IS one way of doing what you're asking in SQL, but it's not neat or supported (AFAIK).

EXEC xp_cmdshell 'SQLCMD -S . -d MyDatabase -Q "select * from MyTable" -s "," -o "\\servername\output\result.csv" -W' 

You can find documentation for SQLCMD here, but essentially what this does is use the xp_cmdshell SP to execute the SQLCMD command line utility on the server and execute a sql statement, piping the output to a CSV file.

The params I've used are as follows:

-S: SQL Server name - . means current server -d: database name -Q: run SQL query and exit -s: column separator -o: output file. This is relative to the SQL Server, not your PC -W: dynamic column witdth 

By default SQL Server does not allow you to run xp_cmdshell, so you may need to run the following SQL to enable it.

-- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1 GO -- To update the currently configured value for this feature. RECONFIGURE GO 

Answers 5

Your best bet I have found is to use a SSIS. Create a Execute SQL Task and run your stored procedure. Then create a data flow set of tasks to move the data from a temporary data table to your CSV file.

Do a search om this web site for how to make a SSIS package. There are a few really nice examples on how to do this.

Good luck to you.

Answers 6

CLR_ExportQueryToCSV is a clr stored procedure located at codeplex.

you need to install project at clr integration enabled sql server

Answers 7

https://www.red-gate.com/simple-talk/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

i think it is possible to use bcp command check if this link is help full to you or not

Read More

Exporting c3.js line charts to png images does not work

I am using SVG.toDataURL() to export c3js charts to png images. Exporting the charts to png works properly.

In case of the line charts, they are not rendered properly. e.g.

1. The x and y axis width is increased.
2. Lines are not proper, instead of the lines it shows dark black regions.

jsfiddle

Below is the code to export png

 function exportImageAsPNG(){         var svgElements = $("#chart").find('svg');         var svg ;         svgElements.each(function() {             svg = this;         });         var img = document.getElementById("fromcanvas");         svg.toDataURL("image/png", {             callback: function(data) {                 img.setAttribute("src", data)             }         })     } 

Same thing happens when I use the canvag library.

var $container = $('#chart'), content = $container.html().trim(), canvas = document.getElementById('svg-canvas');  // Draw svg on canvas canvg(canvas, content);  // Change img be SVG representation var theImage = canvas.toDataURL('image/png');  $("#hiddenPng").attr('href', theImage); $("#hiddenPng span").trigger("click"); 

1 Answers

Answers 1

The issue is that the exporter function only considers inline CSS styles when performing the export. In other words, the exporter is losing track of C3's css settings, thus your graph will look like this fellow's right before it exports

https://github.com/c3js/c3/issues/356

Most importantly, the black triangles are caused by the fill property of some specific .c3 elements. c3.css sets these to none by default, but your exporter doesn't know that.

See:

And, if you manually turn off the fill property from c3.min.css...

You'll want to somehow set the fill CSS property of those specific elements as inline CSS (as in ) before exporting

Here's a quick, plainJS fix for this, add these lines between genChart(); and exportImageAsPNG(); as shown to fix your problem.

    genChart();     var nodeList = document.getElementById('chart').querySelector('svg').querySelectorAll('.c3-chart path');     var nodeList2 = document.getElementById('chart').querySelector('svg').querySelectorAll('.c3-axis path');     var nodeList3 = document.getElementById('chart').querySelector('svg').querySelectorAll('.c3 line');     var line_graph = Array.from(nodeList);     var x_and_y = Array.from(nodeList2).concat(Array.from(nodeList3));     line_graph.forEach(function(element){         element.style.fill = "none";     })     x_and_y.forEach(function(element){         element.style.fill = "none";         element.style.stroke = "black";     })     exportImageAsPNG(); 

JSFiddle: https://jsfiddle.net/vtange/vajs3cmf/

Read More

Error: This page can't be reached in Kartik Yii2 Export

In Kartik Yii2 Export, While exporting as Excel am getting This Page Cant't Reached Error in Localhost.

if i export as Text or CSV, export get worked but if i open the exported file Text or CSV, Half the report is printing like html code

Help will be really appreciated.

GridCode:

 <?php $gridColumns = [             ['class' => 'yii\grid\SerialColumn'],                 'membercode',                 'member_name',                 [                    'attribute' => 'payment_category',                    'format' => 'raw',                    'label' => 'Payment Category',                    'value' => function($model, $key, $index, $grid) {                         $temp = $model->payment_category;                         $si = Category::find()->where(['category_id' => $temp])->one();                         return $si['category_name'];                     },                   ],                 'member_gender',                 'member_address:ntext',                 'payment_date',                 'amount',                 'receipt_no',                 'payment_mode',                 'pledge_amount',                 'young_amount',                 'tv_amount',                 'building_amount',                 [                    'attribute' => 'payment_subcategory',                    'format' => 'raw',                    'value' => function($model, $key, $index, $grid) {                         $exp = explode(',', $model->payment_subcategory);                         $relation_name = ArrayHelper::map(Subcategory::find()->where(['subcategory_id' => $exp])->all(), 'subcategory_id', 'subcategory_name');                         $relation = implode(',', $relation_name);                         return $relation;                     },                     'filter' => Html::activeDropDownList($searchModel, 'payment_subcategory', ArrayHelper::map(Subcategory::find()->asArray()->all(), 'id', 'subcategory_name'),['class'=>'form-control','multiple' => true]),                 ],             ['class' => 'yii\grid\ActionColumn'],         ]; ?>      <?= ExportMenu::widget([             'dataProvider' => $dataProvider,             'columns' => $gridColumns,             'columnSelectorOptions'=>[                 'label' => 'Columns',                 'class' => 'btn btn-danger'             ],             'fontAwesome' => true,             'dropdownOptions' => [                 'label' => 'Export All',                 'class' => 'btn btn-primary'             ]         ]); ?>      <?= GridView::widget([         'dataProvider' => $dataProvider,         'filterModel' => $searchModel,         'columns' => $gridColumns,         'pager' => [             'firstPageLabel' => 'First',             'lastPageLabel' => 'Last',         ],     ]); ?> 

Above is my Grid view Code. Help will be really appreciated.

Updated:

Error geeting while exporting as CSV:

Error geeting while exporting as EXCEL

3 Answers

Answers 1

There is an issue in your gridview, one of the field in gridview carries "=" equal to sign. please check it out PhpOffice/PhpExcel

Answers 2

Try exportConfig settings this

 <?= ExportMenu::widget([         'dataProvider' => $dataProvider,         'columns' => $gridColumns,         'columnSelectorOptions'=>[             'label' => 'Columns',             'class' => 'btn btn-danger'         ],         'fontAwesome' => true,         'dropdownOptions' => [             'label' => 'Export All',             'class' => 'btn btn-primary'         ]         'exportConfig' => [     ExportMenu::FORMAT_HTML => false,     ExportMenu::FORMAT_TEXT => false, ],     ]); ?> 

Answers 3

Try this code for your GridVew::Widget:

  GridView::widget([     'dataProvider' => $dataProvider,     'filterModel' => $searchModel,     'columns' => $gridColumns,     'exportConfig'=> [         GridView::CSV=>[             'label' => 'CSV',             'icon' => '',             'iconOptions' => '',             'showHeader' => false,             'showPageSummary' => false,             'showFooter' => false,             'showCaption' => false,             'filename' => 'yii',             'alertMsg' => 'created',             'options' => ['title' => 'Semicolon -  Separated Values'],             'mime' => 'application/csv',             'config' => [                 'colDelimiter' => ";",                 'rowDelimiter' => "\r\n",             ],          ],     ], ]); 

Read More