Copy CSV file to Databricks Delta table

COPY INTO delta.`abfss://container@storageAccount.dfs.core.windows.net/deltaTables/target`
  FROM (SELECT key, index, textData, 'constant_value'
          FROM 'abfss://container@storageAccount.dfs.core.windows.net/base/path')
  FILEFORMAT = CSV
  PATTERN = 'folder1/file_[a-g].csv'
  FORMAT_OPTIONS('header' = 'true')

-- The example below loads CSV files without headers on ADLS Gen2 using COPY INTO.
-- By casting the data and renaming the columns, you can put the data in the schema you want
COPY INTO delta.`abfss://container@storageAccount.dfs.core.windows.net/deltaTables/target`
  FROM (SELECT _c0::bigint key, _c1::int index, _c2 textData
        FROM 'abfss://container@storageAccount.dfs.core.windows.net/base/path')
  FILEFORMAT = CSV
  PATTERN = 'folder1/file_[a-g].csv'

 https://docs.databricks.com/en/ingestion/copy-into/examples.html

Databricks Rename Table Column Name

 ALTER TABLE enterprise_db.tblmf_sources SET TBLPROPERTIES (

  'delta.minReaderVersion' = '2',

  'delta.minWriterVersion' = '5',

  'delta.columnMapping.mode' = 'name'

)

Alter table RENAME enterprise_db.tblmf_sources COLUMN     sourceName TO SourceName

Remove and add widgets in databricks dbutils.widgets

Remove:- dbutils.widgets.removeAll()

Add:- dbutils.widgets.text("EnterCaseNo", "17102603374")

Writing letter using openAI python

This code generated by chat.openai.com and this code is for databricks notebook. I modified according to my need.

 %python

import openai

openai.api_key = os.environ["OPENAI_API_KEY"]

#prompt = "Write a news article about a new breakthrough in cancer research."

#prompt = "Write a letter about cancer research. to a doctor "

#prompt = "Write a letter to supervisor about leave"

prompt =dbutils.widgets.get("SearchBox")

response = openai.Completion.create(

    engine="text-davinci-002",

    prompt=prompt,

    max_tokens=1024,

    n=1,

    stop=None,

    temperature=0.5,

)

article = response.choices[0].text

#print(article)

df = spark.createDataFrame([(article,)], ["article"]  )

# Display the DataFrame as a table

display(df)

How to see Stored Proc code if you don't have access SQL Server

 EXEC [SERVER NAME].[DATABASE NAME].DBO.SP_HELPTEXT 'Stored Proc Name'

Databricks SQL Useful Shortcut

 

 Databricks SQL Useful Shortcut

Ctrl+Shift+L : List tables in the current database.

Ctrl+Shift+T : Create a new table.

Ctrl+Shift+D : Drop a table.

Ctrl+Shift+U : Update a table.

Ctrl+Shift+I : Insert a row into a table.

Ctrl+Shift+D : Delete a row from a table.

Ctrl+Shift+F : Filter data in a table.

Ctrl+Shift+S : Sort data in a table.

Ctrl+Shift+P : Perform a SQL query.

Databricks Notebook Useful Shortcut

Databricks notebook shortcut

Ctrl+Enter : Run the current cell.

Shift+Enter : Run the current cell and move to the next cell.

Ctrl+Alt+p : Create a cell above the current cell.

Ctrl+Alt+n : Create a cell below the current cell.

Ctrl+Z : Undo the last action.

Ctrl+Y : Redo the last action.

Ctrl+A : Select all text in the current cell.

Ctrl+C : Copy the selected text.

Ctrl+V : Paste the copied text.

Ctrl+X : Cut the selected text.

Ctrl+L : Show or hide line numbers.

Ctrl+K : Comment out the selected text.

Ctrl+U : Uncomment the selected text.

Ctrl+Shift+F : Format the code in the current cell.

Ctrl+Shift+B : Run all cells in the current notebook.

Ctrl+Shift+S : Save the current notebook.

Ctrl+Shift+G : Go to line number.

Ctrl+Shift+H : Find and replace text.

Ctrl+Shift+P : Open the command palette.

Ctrl+Shift+Q : Quit Databricks.

Alt + Enter: Run the current cell and insert a new one below it

Esc + 0: Restart the kernel

Esc + M: Convert the current cell to a Markdown cell

Esc + Y: Convert the current cell to a code cell

Esc + A: Insert a new cell above the current cell

Esc + B: Insert a new cell below the current cell

Esc + X: Cut the current cell

Esc + C: Copy the current cell

Esc + V: Paste the cell that was cut or copied

Esc + D, D: Delete the current cell

ISNULL in Databricks SQL

Use 

 coalesce

coalesce(member_count,0)

Command line executable sql server ChatGPT

 A command line executable for SQL Server is a file that can run SQL queries or scripts using a command-line utility such as sqlcmd123. It usually has an extension like .sql or .bat. To run a command line executable for SQL Server, you need to know its file path and use the appropriate syntax for your utility. For example:

In sqlcmd, you can use the -i option to specify the input file name. For example: sqlcmd -S server_name -d database_name -i C:\Scripts\script.sql

In a batch file, you can use the sqlcmd command with the input file name as an argument. For example: sqlcmd -S server_name -d database_name -i C:\Scripts\script.sql

You can also pass other options to the sqlcmd utility, such as -o to specify the output file name, -v to define variables, or -q to execute a query directly. For more information on sqlcmd options, see here.

If you want to create your own command line executable for SQL Server, you can

ChatGPT Open AI Excel VBA error

This code is generated by ChatGPT but still getting error to connect to API. help.

Error in this line: request.send JsonConverter.ConvertToJson(body)

 Sub ChatItem()

    'Declare variables

    Dim request As Object

    Dim response As String

    Dim url As String

    Dim headers As Object

    Dim body As Object

    

    'Set url and headers for API call

    url = "https://api.openai.com/v1/engines/chatgpt/completions"

    Set headers = CreateObject("Scripting.Dictionary")

    headers.Add "Authorization", "sk-xxxxxxxxxxxxxxxx" 'Replace with your API key

    

    'Set body for API call with prompt and parameters

    Set body = CreateObject("Scripting.Dictionary")

    

    'Get item name from cell A1

    Dim item As String

    item = Range("A1").Value

    

    'Set prompt to chat about the item using ChatGPT's persona

    body.Add "prompt", "The following is a conversation with ChatGPT, an AI assistant that can help you with Excel tasks. ChatGPT knows how to use VBA and can generate code snippets for you.\n\nHuman: Hi, I want to chat about " & item & ".\nChatGPT:"

    

    'Set parameters for completion such as temperature, max_tokens, etc.

    body.Add "temperature", 0.5 'Lower temperature means more predictable responses

    body.Add "max_tokens", 50 'Maximum number of tokens to generate

    body.Add "stop", "\nHuman:" 'Stop generating when encountering this sequence

    

     'Create a new request object

     Set request = CreateObject("MSXML2.XMLHTTP")

     

     'Send a POST request with the url, headers and body as JSON string

     request.Open "POST", url, False

     For Each Key In headers.keys

        request.setRequestHeader Key, headers(Key)

     Next Key

     

     request.send JsonConverter.ConvertToJson(body)

     

     'Get the response text as JSON object and extract the generated text

     response = request.responseText

     Set json = JsonConverter.ParseJson(response)

     

     Dim generated_text As String

     generated_text = json("choices")(1)("text")

     

     

    

End Sub

Error Databricks 'charindex'. This function is neither a registered temporary function nor a permanent function registered in the database 'test'.; line 6 pos 32

Error in DataBricks SQL 'charindex'. This function is neither a registered temporary function nor a permanent function registered in the database 'test'.; line 6 pos 32

Instead of this 

CHARINDEX('.', JDEAccount)

Use This 

INSTR(JDEAccount, '.')

Generate Insert SQL Script from SQL Server to DataBricks Azure

DECLARE

    @TABLENAME VARCHAR(MAX) ='GLaccountsEIMcsv',

    @FILTER_C VARCHAR(MAX)=''   -- where SLNO = 15 or some value

DECLARE @TABLE_NAME VARCHAR(MAX),

        @CSV_COLUMN VARCHAR(MAX),

        @QUOTED_DATA VARCHAR(MAX),

        @SQLTEXT VARCHAR(MAX),

        @FILTER VARCHAR(MAX) 

SET @TABLE_NAME=@TABLENAME

SELECT @FILTER=@FILTER_C

SELECT @CSV_COLUMN=STUFF

(

    (

     SELECT ',['+ NAME +']' FROM sys.all_columns 

     WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND 

     is_identity!=1 FOR XML PATH('')

    ),1,1,''

)

SELECT @QUOTED_DATA=STUFF

(

    (

     SELECT ' ISNULL(QUOTENAME('+NAME+','+QUOTENAME('''','''''')+'),'+'''NULL'''+')+'','''+'+' FROM sys.all_columns 

     WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND 

     is_identity!=1 FOR XML PATH('')

    ),1,1,''

)

-- FOR SQL SERVER

--SELECT @SQLTEXT='SELECT ''INSERT INTO '+@TABLE_NAME+'('+@CSV_COLUMN+')VALUES('''+'+'+SUBSTRING(@QUOTED_DATA,1,LEN(@QUOTED_DATA)-5)+'+'+''')'''+' Insert_Scripts FROM '+@TABLE_NAME + @FILTER

-- FOR DATABRICKS SQL

SELECT @SQLTEXT='SELECT ''INSERT INTO '+@TABLE_NAME+'   VALUES('''+'+'+SUBSTRING(@QUOTED_DATA,1,LEN(@QUOTED_DATA)-5)+'+'+''')'''+' Insert_Scripts FROM '+@TABLE_NAME + @FILTER

EXECUTE (@SQLTEXT)

SQL

https://www.mytecbits.com/microsoft/sql-server/auto-generate-insert-statements

Create Table Databricks select into a temp table

 %sql

CREATE TABLE tblBU_list_XX40_Desc_01 as

select * from tblBU_list_XX40_Desc

How to read Parquet file in DataBricks

# Databricks notebook source

df_GLDATAXYZA = spark.read.option("header",True).parquet("/mnt/ABCprdadls/2_RAW/mload/XYZA_GL/GLDATA",inferSchema="True")

df_GLDATAXYZA.createOrReplaceTempView("GLDATA")

df_GLDATAXYZA = spark.read.option("header",True).parquet("/mnt/ABCprdadls/2_RAW/mload/XYZA_GL/GL_Details",inferSchema="True")

df_GLDATAXYZA.createOrReplaceTempView("GL_Details")

df_GLDATAXYZA = spark.read.option("header",True).parquet("/mnt/ABCprdadls/2_RAW/mload/XYZA_GL/GLDetails_2023",inferSchema="True")

df_GLDATAXYZA.createOrReplaceTempView("GLDetails_2023")

df_GLDATAXYZA = spark.read.option("header",True).parquet("/mnt/ABCprdadls/2_RAW/mload/XYZA_GL/tblBU_list_MF40_Desc",inferSchema="True")

df_GLDATAXYZA.createOrReplaceTempView("tblBU_list_MF40_Desc")

df_GLDATAXYZA = spark.read.option("header",True).parquet("/mnt/ABCprdadls/2_RAW/mload/XYZA_GL/BudgetData",inferSchema="True")

df_GLDATAXYZA.createOrReplaceTempView("Budgetdata")

df_GLDATAXYZA = spark.read.option("header",True).parquet("/mnt/ABCprdadls/2_RAW/mload/XYZA_GL/Tbl_PurePeriod",inferSchema="True")

df_GLDATAXYZA.createOrReplaceTempView("Tbl_PurePeriod")

spark.catalog.setCurrentDatabase("test")

# COMMAND ----------

# MAGIC %sql

# MAGIC CREATE TEMPORARY VIEW VWtempGLDetails

# MAGIC     AS

# MAGIC select * from GLDetails_2023

# MAGIC union 

# MAGIC Select *  from GL_Details  --where glfy=22 and glpn=12

# COMMAND ----------

# MAGIC %sql

# MAGIC select * from VWtempGLDetails where glfy=23 and glpn=1 and gllt in('AA','AL','AN') and glco=2

# MAGIC limit 10

# COMMAND ----------

# MAGIC %sql

# MAGIC SELECT * FROM GLDATA WHERE fy=23 AND lt IN('AA','AL','AN') AND co=2 

# COMMAND ----------

# MAGIC %sql

# MAGIC select * from Tbl_PurePeriod

Set Database Catalog DataBricks Azure Data Lake

spark.catalog.setCurrentDatabase(StrDatabaseName)

Databricks create table from parquet file Azure Datalake

%sql

CREATE TABLE tblBU_list_MF40_Desc

USING parquet 

OPTIONS (path "File Location")

e.g /mnt/Dir1/Dir2/Dir3/Fin_DB_GL/tblBU_data

How to read Parquet files in Databricks

How to read parquet file in databricks and run select statement.

 

1. Write following code in cell1 and execute

df_GLDATAMFDB = spark.read.option("header",True).parquet("/mnt/FileServer/Folder1/Folder2/Folder3/",inferSchema="True")

df_GLDATAMFDB.createOrReplaceTempView("MFDB_GL")

2. After that in second cell run following:-

%sql

select  * from MFDB_GL

How to Create Next Number for Batch Processing in SQL SERVER

 

CREATE TABLE [dbo].[TBL_BatchNN](

[NN] [int] IDENTITY(1,1) NOT NULL,

[strgetdate] [datetime] NULL,

 CONSTRAINT [PK_TBL_BatchNN] PRIMARY KEY CLUSTERED 

(

[NN] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

ALTER Proc [dbo].[SP_BatchLoadNN] AS

Set nocount on

declare @NN int 

Insert into TBL_BatchNN(dateupdate) select getdate()

select @NN=max(NN) from TBL_BatchNN

delete TBL_BatchNN

SELECT  @NN

Overpunch characters vb.net Function for Money & decimal

 Private Function overpunchDec(def As String) As Decimal

        Dim Intvalint As Decimal

        Dim strval

        Dim strvalreal

        Dim strover = def.Trim().Substring((Len(def) - 1), 1)

        strvalreal = def.Trim().Substring(0, (Len(def) - 1))

        Dim strovernum As String

        'Dim strsignI As Decimal = 1 / 100

        'Dim strsignD As Decimal = -1 / 100

        Dim strsign As Decimal

        ''decimal

        Select Case strover

            Case "{"

                strovernum = "0"

                strsign = 1 / 100

            Case "A"

                strovernum = "1"

                strsign = 1 / 100

            Case "B"

                strovernum = "2"

                strsign = 1 / 100

            Case "C"

                strovernum = "3"

                strsign = 1 / 100

            Case "D"

                strovernum = "4"

                strsign = 1 / 100

            Case "E"

                strovernum = "5"

                strsign = 1 / 100

            Case "F"

                strovernum = "6"

                strsign = 1 / 100

            Case "G"

                strovernum = "7"

                strsign = 1 / 100

            Case "H"

                strovernum = "8"

                strsign = 1 / 100

            Case "I"

                strovernum = "9"

                strsign = 1 / 100

            Case "}"

                strovernum = "0"

                strsign = -1 / 100

            Case "J"

                strovernum = "1"

                strsign = -1 / 100

            Case "K"

                strovernum = "2"

                strsign = -1 / 100

            Case "L"

                strovernum = "3"

                strsign = -1 / 100

            Case "M"

                strovernum = "4"

                strsign = -1 / 100

            Case "N"

                strovernum = "5"

                strsign = -1 / 100

            Case "0"

                strovernum = "6"

                strsign = -1 / 100

            Case "P"

                strovernum = "7"

                strsign = -1 / 100

            Case "N"

                strovernum = "8"

                strsign = -1 / 100

            Case "N"

                strovernum = "9"

                strsign = -1 / 100

        End Select

        strval = strvalreal & strovernum

        Intvalint = Convert.ToInt32(strval) * strsign

        Return Intvalint

    End Function

VB.NET Read/Parse text file and Insert Data into SQL Server Table

VB.NET Read text file and Insert Data into SQL Server Table

Dim FILE_NAME As String = "c:/test/FileToUpload_20230102.013721.txt"

        Dim TextLine As String

        If System.IO.File.Exists(FILE_NAME) = True Then

            Dim objReader As New System.IO.StreamReader(FILE_NAME)

            Dim Str1

            Dim Str2

            Dim Str3

            Dim Str4

            Dim lineCount As Integer 'lines read so far in file

            Do While objReader.Peek() <> -1

                '                TextLine =   objReader.ReadLine() 

                TextLine = objReader.ReadLine()

                Str1 = TextLine.Substring(0, 2)

                If Str1 = "DE" Then

                    Str2 = TextLine.Substring(3, 1)

                    Str3 = TextLine.Substring(4, 3)

                    Str4 = TextLine.Substring(5, 10)

                    ' insert data into sql table

                    Dim query As String = "INSERT INTO  DBO.LoaddataPharmacy_Pal([RECORD TYPE],[RECORD INDICATOR],[ELIGIBLE COVERAGE CODE],[USER BENEFIT ID]) "

                    query = query & " VALUES ('" & Str1 & "' , '" & Str2 & "','" & Str3 & "','" & Str4 & "')"

                    Dim cmd As SqlCommand = New SqlCommand(query, _cn)

                    'cmd.Parameters.AddWithValue("@ROLEID", txtroleId.Text)

                    _cn.Open()

                    cmd.ExecuteNonQuery()

                    _cn.Close()

                End If

                Str1 = ""

                lineCount = lineCount + 1 'increment lineCount

           Loop

            ' Textbox1.Text = TextLine

        Else

            MessageBox.Show("File Does Not Exist")

        End If