Tag: table

VBA – create separate spreadsheets from a filtered table

The task is relatively simple and until now it was always performed by some poor admin soul. There is a large table that needs to be filtered based on a simple criteria and each view has to be saved as a new spreadsheet.

The code is as simple as the task (and as my coding skills) so I would love to get some feedback about any tricks I might have missed to make it more robust or “best practices” advice.

Option Explicit

Sub SplitWorksheet()

    Dim d As Long
    Dim dctList As Object
    Dim varList As Variant
    Dim varName As Variant
    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim rng As Range
    Dim wkbNew As Workbook
    Dim strPath As String

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("Data")
    Set rng = wks.Range("A1").CurrentRegion
    
    strPath = Application.ThisWorkbook.Path & "Distribution"
        
    Set dctList = CreateObject("Scripting.Dictionary")
    dctList.CompareMode = vbTextCompare

    With wks
    
        varList = .Range(.Cells(6, "H"), .Cells(Rows.Count, "H").End(xlUp)).Value2

        For d = LBound(varList) To UBound(varList)
            dctList.Item(varList(d, 1)) = vbNullString
        Next

        For Each varName In dctList
            .Range("a1").CurrentRegion.AutoFilter Field:=8, Criteria1:="=" & varName, Operator:=xlFilterValues
        
            Set wkbNew = Workbooks.Add
            .Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _
                Destination:=wkbNew.Sheets(1).Range("A1")
            wkbNew.SaveAs strPath & varName & ".xlsx"
            wkbNew.Close
        
        Next
        
        .Range("a1").CurrentRegion.AutoFilter
        
    End With

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    MsgBox "All done, individual spreadsheets have been saved", vbOKOnly, "Great success!"
 
End Sub

t sql – How to join 2 tables when second table will return more rows?

im trying to join 2 tables with condition – if it matches than im expecting to get all results from another table.

Current querry that doesn’t work :

SELECT (CASE WHEN ZL.NumerCzesci LIKE '%ZŁOŻENIE%' 
  THEN (SELECT NumerCzesci FROM PodZlozenie WHERE NumerPodZlozenia = ZL.NumerCzesci) ELSE ZL.NumerCzesci END) AS (NumerCzesci) FROM Zlozenie ZL LEFT JOIN PodZlozenie PD ON ZL.NumerZlozenia = PD.NumerPodZlozenia

Table (Zlozenie) looks like :

CREATE TABLE (dbo).(Zlozenie) (
    (Id_Zlozenia)   INT          IDENTITY (1, 1) NOT NULL,
    (NumerZlozenia) VARCHAR (50) NOT NULL,
    (NumerCzesci)   VARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ((Id_Zlozenia) ASC)
);
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-001-000-ROTOR-3-1-3','300-000-001 - WKŁADKA GÓRNA');
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-001-000-ROTOR-3-1-3','300-000-002 - WKŁADKA DOLNA');    
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-001-000-ROTOR-3-1-3','300-000-003 - UCHO ROTORA 11 RAM 3 SEKCJE');    
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-001-000-ROTOR-3-1-3','300-000-005 - RAMIĘ ROTORA 565mm');    
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-021-000 - DACH-BA3-1','300-021-001 - ZŁOŻENIE POKRYWY GÓRNEJ BA3-1');
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-021-000 - DACH-BA3-1','300-021-002 - ZŁOŻENIE POKRYWY DOLNEJ TYŁ BA3-1');
INSERT INTO Zlozenie (NumerZlozenia, NumerCzesci) VALUES ('300-021-000 - DACH-BA3-1','300-021-003 - ZŁOŻENIE POKRYWY DOLNEJ PRZÓD BA3-1');

Table (PodZlozenie) looks like :

CREATE TABLE (dbo).(PodZlozenie) (
    (Id_PD)            INT          IDENTITY (1, 1) NOT NULL,
    (NumerPodZlozenia) VARCHAR (50) NOT NULL,
    (NumerCzesci)      VARCHAR (50) NOT NULL
);
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-001 - ZŁOŻENIE POKRYWY GÓRNEJ BA3-1','300-020-001 - BOK POKRYWY GÓRNEJ')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-001 - ZŁOŻENIE POKRYWY GÓRNEJ BA3-1','300-020-002 - DEKIEL POKRYWY GÓRNEJ')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-001 - ZŁOŻENIE POKRYWY GÓRNEJ BA3-1','300-020-003 - KĄTOWNIK')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-001 - ZŁOŻENIE POKRYWY GÓRNEJ BA3-1','300-020-004 - WSTAWKA UZUPEŁNIAJĄCA')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-002 - ZŁOŻENIE POKRYWY DOLNEJ TYŁ BA3-1','300-020-010 - BLASZKA PROWADZĄCA POKRYWĘ')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-002 - ZŁOŻENIE POKRYWY DOLNEJ TYŁ BA3-1','300-020-011 - POKRYWA DOLNA TYŁ BA3-1')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-002 - ZŁOŻENIE POKRYWY DOLNEJ TYŁ BA3-1','300-020-012 - PŁASKOWNIK BA3-1')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-003 - ZŁOŻENIE POKRYWY DOLNEJ PRZÓD BA3-1','300-020-013 - WZMOCNIENIE POKRYWY')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-003 - ZŁOŻENIE POKRYWY DOLNEJ PRZÓD BA3-1','300-020-014 - UCHWYT')
INSERT INTO PodZlozenie (NumerPodZlozenia, NumerCzesci) VALUES ('300-021-003 - ZŁOŻENIE POKRYWY DOLNEJ PRZÓD BA3-1','300-020-015 - POKRYWA DOLNA PRZÓD BA3-1')

Desired output:

(NumerCzesci)
300-000-001 - WKŁADKA GÓRNA
300-000-002 - WKŁADKA DOLNA
300-000-003 - UCHO ROTORA 11 RAM 3 SEKCJE
300-000-005 - RAMIĘ ROTORA 565mm
300-020-001 - BOK POKRYWY GÓRNEJ
300-020-002 - DEKIEL POKRYWY GÓRNEJ
300-020-003 - KĄTOWNIK
300-020-004 - WSTAWKA UZUPEŁNIAJĄCA
300-020-010 - BLASZKA PROWADZĄCA POKRYWĘ
300-020-011 - POKRYWA DOLNA TYŁ BA3-1
300-020-012 - PŁASKOWNIK BA3-1
300-020-013 - WZMOCNIENIE POKRYWY
300-020-014 - UCHWYT
300-020-015 - POKRYWA DOLNA PRZÓD BA3-1

postgresql – SQL Error [42501]: ERROR: permission denied for table

I am very new to postgres so please my apologies in advance if I sound naive. I am still trying to learn. I am trying to create a readonly role and then create a role and assign readonly role to the user. I logged in as postgres user

CREATE ROLE readonly;
GRANT CONNECT ON DATABASE test_db TO readonly;

GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

CREATE USER readonlyuser WITH PASSWORD 'read123';
grant readonly  to readonlyuser;

Now I can login as user readonlyuser but I can’t read data from any tables. I get error SQL Error [42501]: ERROR: permission denied for table.

Any help would be appreciated.

list manipulation – Build Table of Edge Weight Sums

Is there a convenient way to build a table as generated by the code below where a 5th column (at the far right) could be added to the table that is the sum of the edge weights for the nodes in a given row. Then also be able to sort that table from smallest edge weight sum to largest edge weight sum?

Clear(edges,g)
edges={N1->N2,N1->N3,N1->N4,N2->N5,N3->N5,N3->N6,N4->N6,N5->N7,N6->N7};
g=Graph(edges,VertexLabels->"Name",EdgeWeight->{1,2,3,4,5,6,7,8,9},EdgeLabels->{"EdgeWeight"},EdgeLabelStyle->Directive(Red,20))
WeightedAdjacencyMatrix(g)//MatrixForm
TableForm(FindPath(g,N1,N7, Infinity,All))

enter image description here

8 – Database query a table where field type is MEDIUMTEXT

I’m trying to get webform submission data from the database. My webform has a hidden field for a related node id, but I cannot use a query with a condition on the field. In the database, the field type for value is MEDIUMTEXT. If the value is a string, I can call the condition as a quoted string, but since it’s a number, I get no results. Whether I pass the value as quoted or not, I get no results.

How can I pass in the proper value to get any results?

$query = $database->select('webform_submission_data', 'wsd')
      ->condition('name', 'node_id', '=')
      ->condition('webform_id', 'scores', '=')
      //this is mediumtext in the database
      ->condition('value', "169", '=')
      ->fields('wsd', ('sid'));
    $result = $query->execute();

How do I design a mobile table with multiple headers?

I think the problem lies in the origin, not the destination.

The hierarchy ranks in the original table are given by position, typographic style and color, except in the second hierarchy, which maintains the same color as the first.

By modifying just this, you already have much of the problem solved. In this extremely simple example, there’s an indentation to accentuate it:

enter image description here

Hierarchies in a landscape table are more evident because the eye follows the reading order:

  • left → right
  • top → bottom

In a vertical table one of these readings is eliminated so hierarchies must be emphasized by other means. The visual contrasts is the design tool for those cases.
With very simple design tricks you can get better results:

enter image description here

postgresql – High amount of disk writes during upsert with no changes on table

We are running some nightly data sync jobs which move data from an external database into postgres using an upsert statement. The data is updated in batches and using parallel workers (10 currently).

The problem we are having is that even when no data is changed (e.g. when running two consecutive syncs with no changes in between), there is a high amount of disk io (100% disk usage, 200 MB/s is writes). Our monitoring reports that there are no updated rows and only reads are performed on the table itself.

The expected behavior would be a high amount of reads but close to zero write operations on the disk. We can reproduce this consistently, even when running two syncs on a previously empty table.

What can be the reason for this high amount of disk write operations?

Some more details on our setup:

  • Postgres 12.5 running on Kubernetes with 25 GB of memory
  • 1TB disk with 3000 IOPS gp3 volumes (AWS)
  • Table has around 570 millions rows (120 GB)
  • Primary key index is 43 GB

Upsert-Statements used:

INSERT INTO ... (...)
VALUES (...)
ON CONFLICT (primayKeys...)
DO UPDATE ...
WHERE (tableColumns...) IS DISTINCT FROM (excludedColumns...)

How to loop throug a database table, to safe every column as a single node?

I have a module, which load company data from external db, to save this as content.

function example_create_nodes() {
// Get Data from external DATABASE
  db_set_active('db_wirdfit');
  $result = db_query('SELECT * FROM wirdfit.wirdfit_data');
  db_set_active();

 foreach ($result as $row) {
  $node->language = LANGUAGE_NONE;
  $node->type = "page";
  $node->title = $row->post_title;
  node_object_prepare($node);  //Set some default values

 // Try to set your custom field
  $node->field_id($node->language)(0)('value') = $row->id;
  $node->field_post_content($node->language)(0)('value') = $row->post_content;
  $node->field_post_author($node->language)(0)('value') = $row->post_author;

  $node->date = 'complaint_post_date';
  $node->created = strtotime('complaint_post_date');

  $path = 'content/mytest-' . date('YmdHis');
  $node->path = array('alias' => $path);
  print_r($row);
  node_save($node);

  }

Thats works but saved only one node from the the last row of my table.
How can i loop throug the table and save every row as a single content page.

Thanks for help

vb.net – Connect to Oracle table through Visual Studio 2019 vb

I’ve been beating my head against the wall for far too long on this.

What is the code to connect to an Oracle table through Visual Studio 2019 – using Visual Basic?

On a form load event, I want to connect to Oracle, and import the data from a specific table and then paste it into a new Excel file.

————————————————————————
Imports System.Data
Imports Microsoft.Office.Interop.Excel

Public Class ImportForm
Private Sub ImportForm_Load(sender As Object, e As System.EventArgs) Handles Me.Load
Dim conn As New OleDbConnection(“Data Source=123.123.1.12:1521;User Id=test”) ‘fake data
conn.Open()
(select data and put into Excel)
conn.Dispose()
End Sub
End Class

Any help would be much appreciated!

MySQL count rows from other table AND get most recent title

I have a situation which can be boiled down to the following:

TABLE students
COLUMNS id, name, email

TABLE attendances
COLUMNS id, student_id, event_id

TABLE events
COLUMNS id, name, date_of_event

I want to be able to see all the students, and how many events they’ve attended, so I have:

select name, email, count(attendances.id) as number_of_shows_attended from students
left join attendances
   on student_id = students.id
group by name, email

RESULT

Jane Doe | jane@uni.edu | 0
John Doe | john@uni.edu | 10
Will Doe | will@uni.edu | 2

Perfect. Except I also wish to have a final column which lists the most recent event attended (which can be based on the events.date_of_event date column).

I have tried simply adding the missing column, like so:

select name, email, count(attendances.id) as number_of_shows_attended, 
   event.name as most_recent_event_attended from students 
left join attendances
   on student_id = students.id
inner join events
   on attendances.event_id on events.id
group by name, email

It gives me an event name as expected, but how can I ensure it is the most recent one? I tried adding order by events.date_of_event desc, but of course it’s sorting the results of the query.