test 

..

https://ubiquis.co.uk/loading-csv-files-with-pdi-metadata-injection-2/
-- Create a temporary table to demonstrate the solution
CREATE TABLE YourTable (
    ID INT,
    YourColumn VARCHAR(100)
);

-- Insert sample data
INSERT INTO YourTable (ID, YourColumn) VALUES
(1, '1234_4567_abc'),
(2, '789_012_def'),
(3, 'xyz');

-- Declare variables for storing results
DECLARE @firstPart VARCHAR(100), @secondPart VARCHAR(100);

-- Iterate over rows in the table
DECLARE @currentID INT = 1;
DECLARE @rowCount INT = (SELECT COUNT(*) FROM YourTable);

WHILE @currentID <= @rowCount
BEGIN
    -- Retrieve input string from the table
    DECLARE @inputString VARCHAR(100);
    SELECT @inputString = YourColumn FROM YourTable WHERE ID = @currentID;

    -- Check if the delimiter exists in the input string
    IF CHARINDEX('_', @inputString) > 0
    BEGIN
        -- Find the position of the first occurrence of the delimiter in the input string
        DECLARE @delimiterIndex INT = CHARINDEX('_', @inputString);

        -- Extract the first part before the delimiter
        SET @firstPart = LEFT(@inputString, @delimiterIndex - 1);

        -- Find the position of the second occurrence of the delimiter starting from the position after the first occurrence
        DECLARE @secondDelimiterIndex INT = CHARINDEX('_', @inputString, @delimiterIndex + 1);

        -- Extract the second part between the first and second delimiters
        IF @secondDelimiterIndex > 0
        BEGIN
            SET @secondPart = SUBSTRING(@inputString, @delimiterIndex + 1, @secondDelimiterIndex - @delimiterIndex - 1);
        END
        ELSE
        BEGIN
            -- If second delimiter not found, take the substring from the position after the first delimiter to the end of the string
            SET @secondPart = SUBSTRING(@inputString, @delimiterIndex + 1, LEN(@inputString) - @delimiterIndex);
        END

        -- Output the results
        PRINT 'ID: ' + CAST(@currentID AS VARCHAR(10)) + ', FirstPart: ' + @firstPart + ', SecondPart: ' + @secondPart;
    END
    ELSE
    BEGIN
        -- Handle case when delimiter is not found
        PRINT 'ID: ' + CAST(@currentID AS VARCHAR(10)) + ', Delimiter not found in the input string.';
    END

    -- Move to the next row
    SET @currentID = @currentID + 1;
END

-- Drop the temporary table
DROP TABLE YourTable;


------------------------------------

-- Create a temporary table to demonstrate the solution
CREATE TABLE YourTable (
    ID INT,
    YourColumn VARCHAR(100)
);

-- Insert sample data
INSERT INTO YourTable (ID, YourColumn) VALUES
(1, '1234_4567_abc'),
(2, '789_012_def'),
(3, 'xyz');

-- Query to split the input string into two parts
SELECT
    ID,
    CASE 
        WHEN CHARINDEX('_', YourColumn) > 0 THEN LEFT(YourColumn, CHARINDEX('_', YourColumn) - 1) 
        ELSE YourColumn 
    END AS FirstPart,
    CASE 
        WHEN CHARINDEX('_', YourColumn) > 0 THEN SUBSTRING(YourColumn, CHARINDEX('_', YourColumn) + 1, LEN(YourColumn) - CHARINDEX('_', YourColumn)) 
        ELSE NULL 
    END AS SecondPart
FROM
    YourTable;


========================

-- Create a temporary table to demonstrate the solution
CREATE TABLE YourTable (
    ID INT,
    YourColumn VARCHAR(100)
);

-- Insert sample data
INSERT INTO YourTable (ID, YourColumn) VALUES
(1, '1234_4567_abc'),
(2, '789_012_def'),
(3, 'xyz'),
(4, '123_456');

-- Query to split the input string into two parts
-- Query to split the input string into two parts
SELECT
    ID,
    CASE 
        WHEN CHARINDEX('_', YourColumn) > 0 THEN LEFT(YourColumn, CHARINDEX('_', YourColumn) - 1) 
        ELSE YourColumn 
    END AS FirstPart,
    CASE 
        WHEN CHARINDEX('_', YourColumn) > 0 
            THEN 
                CASE 
                    WHEN CHARINDEX('_', YourColumn, CHARINDEX('_', YourColumn) + 1) > 0
                        THEN
                            CASE 
                                WHEN TRY_CAST(SUBSTRING(YourColumn, CHARINDEX('_', YourColumn) + 1, CHARINDEX('_', YourColumn, CHARINDEX('_', YourColumn) + 1) - CHARINDEX('_', YourColumn) - 1) AS INT) IS NOT NULL 
                                    THEN SUBSTRING(YourColumn, CHARINDEX('_', YourColumn) + 1, CHARINDEX('_', YourColumn, CHARINDEX('_', YourColumn) + 1) - CHARINDEX('_', YourColumn) - 1) 
                                ELSE NULL 
                            END
                    ELSE 
                        CASE 
                            WHEN TRY_CAST(SUBSTRING(YourColumn, CHARINDEX('_', YourColumn) + 1, LEN(YourColumn) - CHARINDEX('_', YourColumn)) AS INT) IS NOT NULL 
                                THEN SUBSTRING(YourColumn, CHARINDEX('_', YourColumn) + 1, LEN(YourColumn) - CHARINDEX('_', YourColumn)) 
                            ELSE NULL 
                        END
                END
        ELSE NULL 
    END AS SecondPart
FROM
    YourTable;


// Input field containing the full file path
var fullpathlocation = 'C:\\Folder1\\folder2\\abc.csv'; // Example input

// Extract directory path from full file path
var directoryPath = fullpathlocation.substring(0, fullpathlocation.lastIndexOf('\\') + 1);

// Set the result to the output field
output_field = directoryPath;




====================================================


DECLARE @RowCount INT = 1;
DECLARE @TotalRows INT;

--CREATE TABLE FundConfig (FundName VARCHAR(255), EMAIL VARCHAR(200))

--INSERT INTO FundConfig VALUES
--('A', 'a@gmail.com')
--,('B', 'b@gmail.com')
--,('C', 'c@gmail.com')


-- Create a temporary table to hold FundName and Email along with row numbers
SELECT FundName, Email, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
INTO  #TempTable
FROM FundConfig;

-- Get the total number of rows
SELECT @TotalRows = COUNT(*) FROM #TempTable;

-- Loop through each row using a WHILE loop
WHILE @RowCount <= @TotalRows
BEGIN
    -- Retrieve FundName and Email for the current row
    SELECT FundName, Email
    FROM #TempTable
    WHERE RowNum = @RowCount;

    -- Increment row counter
    SET @RowCount = @RowCount + 1;
END

-- Drop the temporary table
DROP TABLE #TempTable;



===============

SELECT 
    ID,
    STRING_AGG(CAST(Value AS VARCHAR), ';') WITHIN GROUP (ORDER BY Value) AS Value
FROM 
    YourTableName
GROUP BY 
    ID;



=======================

WITH Dates AS (
    SELECT DISTINCT Date FROM Logtable
),
Funds AS (
    SELECT DISTINCT Fundname FROM Logtable
)
SELECT d.Date, f.Fundname
FROM Dates d
CROSS JOIN Funds f
LEFT JOIN Logtable lt ON d.Date = lt.Date AND f.Fundname = lt.Fundname
WHERE lt.Fundname IS NULL
ORDER BY d.Date, f.Fundname;



DECLARE @StartDate DATE = '2024-01-01';
DECLARE @EndDate DATE = '2024-01-31';

WITH DateRange AS (
    SELECT @StartDate AS Date
    UNION ALL
    SELECT DATEADD(day, 1, Date)
    FROM DateRange
    WHERE DATEADD(day, 1, Date) <= @EndDate
)
SELECT Date
FROM DateRange
WHERE DATEPART(dw, Date) NOT IN (1, 7); -- Filter out Sundays (1) and Saturdays (7)





==========================


import os
import shutil
import pandas as pd
from openpyxl import load_workbook

def remove_password_protection(file_path):
    try:
        # Load the Excel workbook
        wb = load_workbook(filename=file_path)

        # Remove password protection
        for sheet in wb.sheetnames:
            ws = wb[sheet]
            ws.protection.password = None

        # Save the workbook without password protection
        wb.save(file_path)
        print(f"Password protection removed from {file_path}")
    except Exception as e:
        print(f"Error occurred while removing password protection from {file_path}: {e}")

def convert_and_move_files(source_folder, destination_folder):
    # Create destination folder if it doesn't exist
    if not os.path.exists(destination_folder):
        os.makedirs(destination_folder)

    # List files in the source folder
    files = os.listdir(source_folder)

    for file in files:
        if file.endswith('.xls'):
            # Remove password protection if any
            input_file_path = os.path.join(source_folder, file)
            remove_password_protection(input_file_path)

            # Convert xls to xlsx
            input_file = os.path.join(source_folder, file)
            output_file = os.path.join(destination_folder, file.replace('.xls', '.xlsx'))
            try:
                df = pd.read_excel(input_file)
                df.to_excel(output_file, index=False)
                print(f"Converted {file} to {output_file}")

                # Remove the original xls file
                os.remove(input_file)
            except Exception as e:
                print(f"Error occurred during conversion of {file}: {e}")
        
        elif file.endswith('.xlsx'):
            # Directly move the xlsx file to destination folder
            input_file = os.path.join(source_folder, file)
            output_file = os.path.join(destination_folder, file)
            try:
                shutil.move(input_file, output_file)
                print(f"Moved {file} to {destination_folder}")
            except Exception as e:
                print(f"Error occurred during moving of {file}: {e}")

# Replace 'source_folder' and 'destination_folder' with your folder paths
source_folder = 'source_folder'
destination_folder = 'destination_folder'

convert_and_move_files(source_folder, destination_folder)



====================

import os
import shutil
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

import zipfile
from openpyxl import load_workbook

def remove_password_protection(file_path, password='1234'):
    try:
        with open(file_path, 'rb') as f:
            data = f.read()
        # Locate the password hash in the encrypted file
        if data[0x20:0x24] == b'\x2F\xE5\x4F\x3C':
            data = data[0x20:]
        # Replace the hash with null bytes
        data = data.replace(data[0x12:0x2E], b'\x00' * 14)
        # Write the modified data back to the file
        with open(file_path, 'wb') as f:
            f.write(data)
        print(f"Password protection removed from {file_path}")
    except Exception as e:
        print(f"Error occurred while removing password protection from {file_path}: {e}")



def convert_and_move_files(source_folder, destination_folder):
    # Create destination folder if it doesn't exist
    if not os.path.exists(destination_folder):
        os.makedirs(destination_folder)

    # List files in the source folder
    files = os.listdir(source_folder)

    for file in files:
        if file.endswith('.xls'):
            # Remove password protection if any
            input_file_path = os.path.join(source_folder, file)
            remove_password_protection(input_file_path)

            # Convert xls to xlsx
            input_file = os.path.join(source_folder, file)
            output_file = os.path.join(destination_folder, file.replace('.xls', '.xlsx'))
            try:
                # Read data from xls using pandas and save to xlsx using openpyxl
                df = pd.read_excel(input_file)
                with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
                    df.to_excel(writer, index=False)
                    writer.save()
                print(f"Converted {file} to {output_file}")

                # Remove the original xls file
                os.remove(input_file)
            except Exception as e:
                print(f"Error occurred during conversion of {file}: {e}")
        
        elif file.endswith('.xlsx'):
            # Directly move the xlsx file to destination folder
            input_file = os.path.join(source_folder, file)
            output_file = os.path.join(destination_folder, file)
            try:
                shutil.move(input_file, output_file)
                print(f"Moved {file} to {destination_folder}")
            except Exception as e:
                print(f"Error occurred during moving of {file}: {e}")

# Replace 'source_folder' and 'destination_folder' with your folder paths
source_folder = 'source_folder'
destination_folder = 'destination_folder'

convert_and_move_files(source_folder, destination_folder)


====


import os
import shutil
from pyexcelerate import Workbook
from xlrd import open_workbook
from xlutils.copy import copy

def remove_password_protection(file_path, password):
    try:
        # Create a new workbook using pyexcelerate
        new_wb = Workbook()
        # Load the original workbook using xlrd
        rb = open_workbook(file_path, formatting_info=True, on_demand=True, password=password)
        # Create a copy of the workbook to write changes
        wb = copy(rb)
        # Iterate through each sheet and add to the new workbook
        for sheet_index in range(wb.nsheets):
            sheet = wb.get_sheet(sheet_index)
            new_wb.new_sheet(sheet.name, data=sheet._cell_values)
        # Save the new workbook to a temporary file
        temp_file_path = file_path + '.tmp.xlsx'
        new_wb.save(temp_file_path)

        # Replace the original file with the modified one
        shutil.move(temp_file_path, file_path)
        print(f"Password protection removed from {file_path}")
    except Exception as e:
        print(f"Error occurred while removing password protection from {file_path}: {e}")

# Replace 'source_folder' with your folder path containing the password-protected .xls files
source_folder = 'source_folder'
password = 'your_password'

# List files in the source folder
files = os.listdir(source_folder)

for file in files:
    if file.endswith('.xls'):
        # Remove password protection if any
        input_file_path = os.path.join(source_folder, file)
        remove_password_protection(input_file_path, password)






======================================


# Define variables
$folderPath = "C:\Path\To\Your\Folder"
$timeLimit = (Get-Date).AddHours(-1)
$emailTo = "recipient@example.com"
$emailFrom = "sender@example.com"
$smtpServer = "smtp.example.com"
$smtpPort = 587
$smtpUsername = "your_smtp_username"
$smtpPassword = "your_smtp_password"
$emailSubject = "No New Files Detected"
$emailBody = "No new files have been detected in the folder '$folderPath' within the last hour."

# Check for new files in the folder
$newFiles = Get-ChildItem -Path $folderPath | Where-Object { $_.LastWriteTime -gt $timeLimit }

# If no new files are found, send an email notification
if ($newFiles.Count -eq 0) {
    $smtpSecurePassword = ConvertTo-SecureString $smtpPassword -AsPlainText -Force
    $credentials = New-Object System.Management.Automation.PSCredential ($smtpUsername, $smtpSecurePassword)
    
    Send-MailMessage -To $emailTo `
                     -From $emailFrom `
                     -Subject $emailSubject `
                     -Body $emailBody `
                     -SmtpServer $smtpServer `
                     -Port $smtpPort `
                     -UseSsl `
                     -Credential $credentials
}

Write-Output "Script executed at: $(Get-Date)"






=========================


DECLARE @InputString NVARCHAR(MAX) = 'A,B,C,D'
DECLARE @Delimiter CHAR(1) = ','

;WITH SplitString AS
(
    SELECT 
        LEFT(@InputString, CHARINDEX(@Delimiter, @InputString + @Delimiter) - 1) AS Part,
        SUBSTRING(@InputString, CHARINDEX(@Delimiter, @InputString + @Delimiter) + 1, LEN(@InputString)) AS Remainder
    UNION ALL
    SELECT 
        LEFT(Remainder, CHARINDEX(@Delimiter, Remainder + @Delimiter) - 1),
        SUBSTRING(Remainder, CHARINDEX(@Delimiter, Remainder + @Delimiter) + 1, LEN(Remainder))
    FROM 
        SplitString
    WHERE 
        Remainder <> ''
)
SELECT 
    Part
FROM 
    SplitString
OPTION (MAXRECURSION 0);


-====================


import pyodbc

# Define your connection parameters
server = 'your_server_name'  # e.g., 'localhost\\SQLEXPRESS' or 'your_server_ip'
database = 'your_database_name'  # e.g., 'master'
username = 'your_username'  # e.g., 'sa'
password = 'your_password'  # e.g., 'your_password'

# Define the connection string
connection_string = f'DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={server};DATABASE={database};UID={username};PWD={password}'

# Establish the connection
try:
    connection = pyodbc.connect(connection_string)
    print("Connection successful!")

    # Create a cursor object
    cursor = connection.cursor()

    # Execute a simple query
    cursor.execute("SELECT @@version;")
    row = cursor.fetchone()
    while row:
        print(row[0])
        row = cursor.fetchone()

except pyodbc.Error as e:
    print("Error in connection:", e)
finally:
    # Close the connection
    if 'connection' in locals() and connection:
        connection.close()

Comments

Post a Comment

Popular posts from this blog

host

import pandas as pd from datetime import datetime import os # Example DataFrame df_final = pd.DataFrame({     'Name': ['Alice', 'Bob', 'Charlie'],     'Age': [25, 30, 35],     'City': ['New York', 'Los Angeles', 'Chicago'] }) # Generate today's date in 'YYYY-MM-DD' format today = datetime.now().strftime("%Y-%m-%d") # Create a dynamic filename filename = f"{today}_data.csv" # Network drive path (Windows) network_folder = r"\\network_drive\shared_folder" # Full path to save the file full_path = os.path.join(network_folder, filename) # Save DataFrame to CSV try:     df_final.to_csv(full_path, index=False)     print(f"File saved successfully to {full_path}") except Exception as e:     print(f"Error saving file: {e}")
Read more

Steps to create SSH key from git bash

Generating an SSH key in OpenSSH involves a few simple steps. Here’s how you can do it: 1. Open a Terminal If you're on Linux or macOS, you can use the built-in terminal. On Windows, you can use the Command Prompt, PowerShell, or Git Bash if you have Git installed. 2. Generate the SSH Key Pair Run the following command: bash Copy code ssh-keygen -t rsa -b 4096 -C "your_email@example.com" Here’s what each option means: -t rsa : Specifies the type of key to create, in this case, RSA. -b 4096 : Specifies the number of bits in the key, 4096 bits is a good choice for strong encryption. -C "your_email@example.com" : Adds a label to the key, usually your email address. 3. Choose a File Location After running the above command, you'll be prompted to choose a location to save the key. By default, it will be saved in ~/.ssh/id_rsa (on Linux or macOS) or in C:\Users\YourUserName\.ssh\id_rsa (on Windows). Press Enter to accept the default location, or specify a diff...
Read more