Excel As Database without column header

Help Activities
, ,
1

Hi All,
Thinking how Excel DB works without header…
If an excel file without header how the DML command works?
for Select we will write as “SELECT * FROM [Sheet1$]”
But for update, insert to any column how to handle this type?

Thank you all

2

Hi @Sharanabasava

Yes, DML still works with the position instead of header name for update or insert whatever you want to perform.

For Inserting:

INSERT INTO [Sheet1$] (F1, F2, F3) VALUES (‘Value1’, ‘Value2’, ‘Value3’)

For Updating:

UPDATE [Sheet1$] SET F2 = ‘NewValue’ WHERE F1 = ‘ConditionValue’

Hope it helps you to understand !!
Happy Automation :uipath:

3

@Nawazish_Ahmad
Thank you for quick response, but I have tried and not able to achieve…am making any wrong?

A table lists sales data by date, region, representative, item, units, unit cost, and total. (Captioned by AI)

Here I am making change where column contains Pencil update with Sharan

query I wrote is
“UPDATE [Sheet1$] Set C1 =‘Sharan’ WHERE D1 = ‘Pencil’”

getting below error as
image

4

@Sharanabasava

Try this:

UPDATE [Sheet1$] SET F3 = 'Sharan' WHERE F4 = 'Pencil'

Thanks :slight_smile:

5

Same error I am getting…

6

Hi @Sharanabasava

Can you try the below queries!!

For inserting a new row,

INSERT INTO [Sheet1$] (F1, F2, F3) VALUES ('John', 'Doe', 30)

For updating a specific row,

UPDATE [Sheet1$] SET F2 = 'Smith' WHERE F1 = 'John' AND F3 = 30

For deleting a row,

DELETE FROM [Sheet1$] WHERE F1 = 'John' AND F3 = 30

When Excel files don’t have headers, columns are named automatically as F1, F2, F3, etc., corresponding to the order of columns in the sheet.

Hope it helps!!

7

Hi @mkankatala

Am trying to INSERT
“INSERT INTO [Sheet1$] (F1, F2, F3, F4, F5, F6, F7) VALUES (‘5-24-24’,‘Top’,‘Sharan’,‘Rubber’,39,2.00,99.99)”
but I am getting this error

image

8

image

9

Try this method… @Sharanabasava

Create a string variable called connstr and give the below expression in assign activity,

- Assign -> connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\to\yourfile.xlsx;Extended Properties='Excel 12.0 Xml;HDR=NO;'"

In the connect to database activity, Set the ConnectionString property to connStr, Set the ProviderName property to System.Data.OleDba and Set the Output property to dbConnection.

Configure the above options in Connect to database activity and try with the query that I have provided in the above post.

Instead of using the Run query use the Execute non query activity.

Hope it helps!!

10

ODBC.xaml (10.6 KB)
Am still getting error while executing Run command…

image
image715×219 9.91 KB

<?xml version="1.0" encoding="utf-16"?>__ReferenceID0

image

Thank you for your time…

11

Anyone Please guide…

12

In the place of Run Command activity use the Execute non query activity… @Sharanabasava

Give the same query in Execute non query activity.

Hope you understand!!

13

@mkankatala

For INSERT try this:

"INSERT Into [Sheet1$] VALUES ('My', 'Name', 'IS', 'Nawazish', 'Ahmad')"

  • After Insert Command. Again take Run Query activity:

"SELECT * FROM [Sheet1$]" //Output: DT

Hope it helps!!

14

image
image1166×541 34.9 KB

In the latest UiPath version ExecuteNonQuery is updated with Run Command activity and Execute Query to Run Query activity

15

Share the exception what you are facing when executing the Job… @Sharanabasava

16

Hi Nawazish,

yes I am able to insert data, but every time while doing any DML command we need to use this combination?

Thank you …

17

Thank you Mahesh for your time and helping me…

1 Like
18

For update how need to do? hence will not having column name

19

“UPDATE [Sheet1$] SET F3 = ‘Jones’ WHERE F4 = ‘Pencil’”

I can UPDATE DATA…but why ‘F’ we used? Is there any specific

20

@Sharanabasava

The prefix F stands for Field, Actually its a standard convention used by ODBC drivers to refer to columns in Excel sheets that do not have defined headers like in your case.

Hope it helps you to understand better!!
Happy Automation :uipath: