Trim the length of the string containing the decimal values

Help Studio
, ,
1

Hi,
I have a data table containing some data which I have to push to the data base on sql server.
In that data table some columns are string which will be pushed into varchar datatype variables in database.
but some columns are having decimal value whose length I need to trim and then push it to database.
Here I have attached sheet containing the data
Listing.xlsx (10.9 KB)

DB Data Type
|Age| Decimal(9,0)|
|Use_Only| Decimal(11,0)|
|Order|Decimal(11,0)|
|Sales_Order|Decimal(11,0)|
Can anyone help me with the code?

2

@supermanPunch @Gokul001 @ushu @kirankumar.mahanthi1 @Palaniyappan @Sudharsan_Ka @Anil_G

3

Hi @Kunal_Jain

  1. Prepare the DataTable: Make sure your DataTable is properly populated with data from the source. Ensure that the column types in the DataTable match the data types in the SQL Server table.
  2. Connect to the SQL Server Database: Use the “Connect” activity to establish a connection to your SQL Server database. Provide the necessary connection details, such as server name, database name, authentication credentials, etc. Save the connection to a variable.
  3. Use “For Each Row” to iterate through the DataTable: Drag and drop the “For Each Row” activity to loop through each row in your DataTable.
  4. Inside the loop, use “Assign” activities to modify the values if necessary: For string columns, you can directly pass them to the SQL Server query. For decimal columns, you need to trim them according to your requirements before passing them to the query.For example, if you want to trim the decimal column values to two decimal places, you can use the following code:
row("DecimalColumn") = Decimal.Round(Convert.ToDecimal(row("DecimalColumn")), 2)
  1. Build and execute the SQL Insert query: Within the loop, build your SQL Insert query using the data from the current row. You can use parameterized queries to avoid SQL injection.For example, the query might look like this:
INSERT INTO YourTableName (StringColumn, DecimalColumn) VALUES (@paramString, @paramDecimal)
  1. Execute the query: Use the “Execute Non Query” activity to execute the SQL query, passing the values from the current row as parameters. Remember to use the SQL connection variable you created earlier.

Hope it helps!!

4

Above it is mentioned decimal(9,0) or (11,0)
Than what to do in that case?
Thanks in advance

5

@Kunal_Jain

Based on the number of decimal value places you can edit the 4th step.

Regards,

6

Hi can you help me with a code that I can invoke using Invoke code activity

7

Try this:

' Assuming you have a data table named "dataTable" with the necessary data

For Each row As DataRow In dataTable.Rows
    ' Initialize variables to store the data
    Dim column1Value As String = row("Column1").ToString() ' Assuming Column1 is a string in the data table
    Dim column2Value As String = row("Column2").ToString() ' Assuming Column2 is a string in the data table
    Dim column3Value As Decimal = Decimal.Parse(row("Column3").ToString()) ' Assuming Column3 is a decimal in the data table
    Dim column4Value As Decimal = Decimal.Parse(row("Column4").ToString()) ' Assuming Column4 is a decimal in the data table

    ' Trim decimal values to a specific length (e.g., two decimal places)
    column3Value = Decimal.Round(column3Value, 2)
    column4Value = Decimal.Round(column4Value, 2)

    ' Prepare the SQL query to insert data into the database
    Dim insertQuery As String = "INSERT INTO YourTableName (Column1, Column2, Column3, Column4) VALUES ('" & column1Value & "', '" & column2Value & "', " & column3Value.ToString() & ", " & column4Value.ToString() & ");"

    ' Execute the SQL query to insert data into the database
    Try
        ' Use UiPath's "Execute Non Query" activity to execute the SQL query
        ' Set the connection and pass the "insertQuery" as the SQL statement

    Catch ex As Exception
        ' Handle the exception (e.g., log the error, retry, etc.)
        ' For example:
        ' LogMessage("Error occurred while inserting data: " & ex.Message)
    End Try
Next

Hope it helps!!