Table – Devops Scripts & Patterns

GNU/Linux ASCII Table in CSV/TXT format

2020-01-01 / GNU/Linux / Tim van Kooten Niekerk

Simple ASCII table in (formatted) text and available for download in CSV-format

|   | Binary0  | Oct0 | Dec0 | Hex0 | Chr0 |   | Binary3  | Oct3 | Dec3 | Hex3 | Chr3 |   | Binary6  | Oct6 | Dec6 | Hex6 | Chr6 |
| - | -------- | ---- | ---- | ---- | ---- | - | -------- | ---- | ---- | ---- | ---- | - | -------- | ---- | ---- | ---- | ---- |
|   | 00000000 |  000 |   00 |   00 | NUL  |   | 00110000 |  060 |   48 |   30 | 0    |   | 01100000 |  140 |   96 |   60 | `    |
|   | 00000001 |  001 |   01 |   01 | SOH  |   | 00110001 |  061 |   49 |   31 | 1    |   | 01100001 |  141 |   97 |   61 | a    |
|   | 00000010 |  002 |   02 |   02 | STX  |   | 00110010 |  062 |   50 |   32 | 2    |   | 01100010 |  142 |   98 |   62 | b    |
|   | 00000011 |  003 |   03 |   03 | ETX  |   | 00110011 |  063 |   51 |   33 | 3    |   | 01100011 |  143 |   99 |   63 | c    |
|   | 00000100 |  004 |   04 |   04 | EOT  |   | 00110100 |  064 |   52 |   34 | 4    |   | 01100100 |  144 |  100 |   64 | d    |
|   | 00000101 |  005 |   05 |   05 | ENQ  |   | 00110101 |  065 |   53 |   35 | 5    |   | 01100101 |  145 |  101 |   65 | e    |
|   | 00000110 |  006 |   06 |   06 | ACK  |   | 00110110 |  066 |   54 |   36 | 6    |   | 01100110 |  146 |  102 |   66 | f    |
|   | 00000111 |  007 |   07 |   07 | BEL  |   | 00110111 |  067 |   55 |   37 | 7    |   | 01100111 |  147 |  103 |   67 | g    |
|   | 00001000 |  010 |   08 |   08 | BS   |   | 00111000 |  070 |   56 |   38 | 8    |   | 01101000 |  150 |  104 |   68 | h    |
|   | 00001001 |  011 |   09 |   09 | HT   |   | 00111001 |  071 |   57 |   39 | 9    |   | 01101001 |  151 |  105 |   69 | i    |
|   | 00001010 |  012 |   10 |   0A | LF   |   | 00111010 |  072 |   58 |   3A | :    |   | 01101010 |  152 |  106 |   6A | j    |
|   | 00001011 |  013 |   11 |   0B | VT   |   | 00111011 |  073 |   59 |   3B | ;    |   | 01101011 |  153 |  107 |   6B | k    |
|   | 00001100 |  014 |   12 |   0C | FF   |   | 00111100 |  074 |   60 |   3C | <    |   | 01101100 |  154 |  108 |   6C | l    |
|   | 00001101 |  015 |   13 |   0D | CR   |   | 00111101 |  075 |   61 |   3D | =    |   | 01101101 |  155 |  109 |   6D | m    |
|   | 00001110 |  016 |   14 |   0E | SO   |   | 00111110 |  076 |   62 |   3E | >    |   | 01101110 |  156 |  110 |   6E | n    |
|   | 00001111 |  017 |   15 |   0F | SI   |   | 00111111 |  077 |   63 |   3F | ?    |   | 01101111 |  157 |  111 |   6F | o    |
|   | 00010000 |  020 |   16 |   10 | DLE  |   | 01000000 |  100 |   64 |   40 | @    |   | 01110000 |  160 |  112 |   70 | p    |
|   | 00010001 |  021 |   17 |   11 | DC1  |   | 01000001 |  101 |   65 |   41 | A    |   | 01110001 |  161 |  113 |   71 | q    |
|   | 00010010 |  022 |   18 |   12 | DC2  |   | 01000010 |  102 |   66 |   42 | B    |   | 01110010 |  162 |  114 |   72 | r    |
|   | 00010011 |  023 |   19 |   13 | DC3  |   | 01000011 |  103 |   67 |   43 | C    |   | 01110011 |  163 |  115 |   73 | s    |
|   | 00010100 |  024 |   20 |   14 | DC4  |   | 01000100 |  104 |   68 |   44 | D    |   | 01110100 |  164 |  116 |   74 | t    |
|   | 00010101 |  025 |   21 |   15 | NAK  |   | 01000101 |  105 |   69 |   45 | E    |   | 01110101 |  165 |  117 |   75 | u    |
|   | 00010110 |  026 |   22 |   16 | SYN  |   | 01000110 |  106 |   70 |   46 | F    |   | 01110110 |  166 |  118 |   76 | v    |
|   | 00010111 |  027 |   23 |   17 | ETB  |   | 01000111 |  107 |   71 |   47 | G    |   | 01110111 |  167 |  119 |   77 | w    |
|   | 00011000 |  030 |   24 |   18 | CAN  |   | 01001000 |  110 |   72 |   48 | H    |   | 01111000 |  170 |  120 |   78 | x    |
|   | 00011001 |  031 |   25 |   19 | EM   |   | 01001001 |  111 |   73 |   49 | I    |   | 01111001 |  171 |  121 |   79 | y    |
|   | 00011010 |  032 |   26 |   1A | SUB  |   | 01001010 |  112 |   74 |   4A | J    |   | 01111010 |  172 |  122 |   7A | z    |
|   | 00011011 |  033 |   27 |   1B | ESC  |   | 01001011 |  113 |   75 |   4B | K    |   | 01111011 |  173 |  123 |   7B | {    |
|   | 00011100 |  034 |   28 |   1C | FS   |   | 01001100 |  114 |   76 |   4C | L    |   | 01111100 |  174 |  124 |   7C | |    |
|   | 00011101 |  035 |   29 |   1D | GS   |   | 01001101 |  115 |   77 |   4D | M    |   | 01111101 |  175 |  125 |   7D | }    |
|   | 00011110 |  036 |   30 |   1E | RS   |   | 01001110 |  116 |   78 |   4E | N    |   | 01111110 |  176 |  126 |   7E | ~    |
|   | 00011111 |  037 |   31 |   1F | US   |   | 01001111 |  117 |   79 |   4F | O    |   | 01111111 |  177 |  127 |   7F | DEL  |
|   | 00100000 |  040 |   32 |   20 |      |   | 01010000 |  120 |   80 |   50 | P    |   |          |      |      |      |      |
|   | 00100001 |  041 |   33 |   21 | !    |   | 01010001 |  121 |   81 |   51 | Q    |   |          |      |      |      |      |
|   | 00100010 |  042 |   34 |   22 | "    |   | 01010010 |  122 |   82 |   52 | R    |   |          |      |      |      |      |
|   | 00100011 |  043 |   35 |   23 | #    |   | 01010011 |  123 |   83 |   53 | S    |   |          |      |      |      |      |
|   | 00100100 |  044 |   36 |   24 | $    |   | 01010100 |  124 |   84 |   54 | T    |   |          |      |      |      |      |
|   | 00100101 |  045 |   37 |   25 | %    |   | 01010101 |  125 |   85 |   55 | U    |   |          |      |      |      |      |
|   | 00100110 |  046 |   38 |   26 | &    |   | 01010110 |  126 |   86 |   56 | V    |   |          |      |      |      |      |
|   | 00100111 |  047 |   39 |   27 | '    |   | 01010111 |  127 |   87 |   57 | W    |   |          |      |      |      |      |
|   | 00101000 |  050 |   40 |   28 | (    |   | 01011000 |  130 |   88 |   58 | X    |   |          |      |      |      |      |
|   | 00101001 |  051 |   41 |   29 | )    |   | 01011001 |  131 |   89 |   59 | Y    |   |          |      |      |      |      |
|   | 00101010 |  052 |   42 |   2A | *    |   | 01011010 |  132 |   90 |   5A | Z    |   |          |      |      |      |      |
|   | 00101011 |  053 |   43 |   2B | +    |   | 01011011 |  133 |   91 |   5B | [    |   |          |      |      |      |      |
|   | 00101100 |  054 |   44 |   2C | ,    |   | 01011100 |  134 |   92 |   5C | \    |   |          |      |      |      |      |
|   | 00101101 |  055 |   45 |   2D | -    |   | 01011101 |  135 |   93 |   5D | ]    |   |          |      |      |      |      |
|   | 00101110 |  056 |   46 |   2E | .    |   | 01011110 |  136 |   94 |   5E | ^    |   |          |      |      |      |      |
|   | 00101111 |  057 |   47 |   2F | /    |   | 01011111 |  137 |   95 |   5F | _    |   |          |      |      |      |      |

MSSQL Quick Copy Table

2014-02-28 / MSSQL Server / Tim van Kooten Niekerk

Quickly duplicate a table with the following query.

SELECT * INTO newtable FROM table

Copy Insert Table

MSSQL Table Switch IN/OUT examples

2013-03-15 / MSSQL Server / Tim van Kooten Niekerk

MSSQL table switch IN, switch OUT examples…

-- Switch in/out procedure examples...
-- Tim van Kooten Niekerk


-- Partition function en schema maken...
--DROP PARTITION FUNCTION pfTable_1
--DROP PARTITION SCHEME psTable_1
CREATE PARTITION FUNCTION pfTable_1 (int) AS RANGE LEFT FOR VALUES (2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020)
CREATE PARTITION SCHEME psTable_1 AS PARTITION pfTable_1 ALL TO ([PRIMARY])


-- Make  table with a partition funciton on column jaar (year)...
CREATE TABLE [dbo].[Table_3](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[value] [nchar](10) NULL,
	[jaar] [int] NOT NULL
) ON [psTable_1]([jaar])


-- Check partitioned table...
SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('Table_3') ORDER BY partition_number

-- Make Switch OUT and truncate table so you can reload 2015...
-- Make Switch OUT table without constraints...
CREATE TABLE [dbo].[Table_3_OUT](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[value] [nchar](10) NULL,
	[jaar] [int] NOT NULL 
)
ALTER TABLE Table_3 SWITCH PARTITION 5 TO Table_3_OUT
TRUNCATE TABLE [dbo].[Table_3_OUT] 


-- Now you can reload (INSERT) new values for 2015 in the original table... 
-- Or... Make Switch IN with using a switch IN table for 2015...

 
-- Switch IN table with constraint on jaar = 2015...
CREATE TABLE [dbo].[Table_3_IN](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[value] [nchar](10) NULL,
	[jaar] [int] CHECK (jaar = 2015) NOT NULL 
)
-- INSERT new values in switch IN table and make switch...
ALTER TABLE Table_3_IN SWITCH TO Table_3 PARTITION 5


-- Extend partition function with a new year...
ALTER PARTITION SCHEME psTable_1 NEXT USED [PRIMARY]
ALTER PARTITION FUNCTION pfTable_1 () SPLIT RANGE (2021)

Alter Partition Switch Table

VBS Convert HTML Tables

2010-04-16 / VisualBasic Scripting / Tim van Kooten Niekerk

Visual Basic script to convert HTML to CSV or TEXT using the Excel.Application object.

'****************************************************************************************
'bestandsnaam: ConvertTable2X.vbs
'geschreven_door: Tim van Kooten Niekerk
'versie: 17:38 15-4-2010
'Converteer html tabellen naar CSV of TEXT m.b.v. het Excel object
'****************************************************************************************

' Global variable and const declarations
option explicit
'on error resume next
dim oEXC, oArg
const xlCSV=6
const xlTXT=21

' Global objectdeclarations
set oArg = WScript.Arguments 
set oEXC = CreateObject("Excel.Application")

' Functie inlezen argumenten van de commandline (0-?)
function fReadArgument(vArgNumber)
  On Error Resume Next
  fReadArgument = oArg(vArgNumber)
end function

' Convert2X function...
function fTable2X(sHTMLFile, sCSVFile, sFileType)
  if (sHTMLFile ‹› "") and (sCSVFile ‹› "") then
    dim oWB
    set oWB =  oEXC.Workbooks.Open (sHTMLFile)
    'oEXC.Visible = True
    oEXC.Application.DisplayAlerts = False
    oEXC.ActiveWorkbook.SaveAs sCSVFile,  sFileType
    oEXC.ActiveWorkbook.Close False 
    oEXC.Application.DisplayAlerts = True

    ' Opschonen
    Set oWB =  Nothing
  end if
end function

' Start programma...
if fReadArgument("0") ‹› "" and fReadArgument("1") ‹› ""  and fReadArgument("2") ‹› "" then
  fTable2X fReadArgument("0"), fReadArgument("1"), fReadArgument("2")
else
  MsgBox ("Een of meerdere argumenten ontbreken..." & vbCrLf & vbCrLf & "Gebruik: convertTable2X   " & vbCrLf & "(filetypenum: 6=CSV, 21=TXT)")
end if

' Opschonen
set oArg = Nothing
set oEXC = Nothing

Convert CSV HTML Table Text

MSSQL Remove All Table Rows

2009-06-04 / MSSQL Server / Tim van Kooten Niekerk

TRUNCATE TABLE tablename

Table Truncate

MSSQL Create Table Partitions

2008-06-13 / MSSQL Server / Tim van Kooten Niekerk

Instruction written in the Dutch language…

Open SQL Server Management Studio en klik met de rechter muisknop op de gewenste database. Selecteer properties.
Maak nieuwe bestandsgroepen op de de Filegroups pagina. Markeer zonodig de filegroup als readonly.
Selecteer de gewenste database in het drop-down menu.
Voer onderstaande query uit om de partition function aan te maken op basis van een kolom met datums waarbij de eerste partitie stopt bij datums kleiner of gelijk aan de huidige datum – 365 dagen (LEFT). Bij RANGE RIGHT wordt de tabel gesplitst bij waarden kleiner dande opgegeven waarde.
```
CREATE PARTITION FUNCTION pfTablename (datatype) AS RANGE LEFT FOR VALUES (Getdate() - 365)
```

Voer onderstaande query uit om een partion schemeaan te maken met de eerder aangemaakte filegroups.

CREATE PARTITION SCHEME pfTablename AS PARTITION pfTablename TO (Filegroup1, Filegroup2, Filegroup3)

Koppel het partition scheme aan de kolom in de tabel m.b.v. de table designer (Regular Data Space Specification).

Partition Table