Save data table to database (web extracted data)

Help
, ,
1

Hi
I’m new to UiPath and trying to make my first project.
I want to extract data from a website data table and store it in an SQL Server table.
I have read different topics about this, but I can’t get rid of an invalid column name problem.

My web table has a column header with a blank in the name “C ID”. For the database I tried both “C ID” and C_ID". didn’t work.

Kurse
Kurse1643×398 117 KB

Any help greatly appreciated

2

Hi @relsig

You can use the build data table activity and declare all the columns name and write the extracted web data into that build data table and execute the same into the database.

Declare the C ID column name as CID or C_ID

Try this

Cheers :blush::v:

3

Hi @abu.behlim

Thank you for your answer. I’m afraid I need some more assistance. I created a separate data table with the coresponding columns, of course using CID instead of C ID. Now I have two data tables, the scraped extracted data in one data table and the build data table. How can I store the data from the first into the second one? Using add row activity? Or is there a simpler way by assigning the table variables to each other?

Thank you

4

Hi @relsig

You can use the clone method to write the datatable into the build Data table variable

example:

Variable BuildTDataTable = WebScrappedData.Clone

and call the BuilDataTable variable into the write Range or Non Execute Query.

Try this method and let me know

5

Hi @abu.behlim

Using an assign activity? Tried the following

Unbenannt
Unbenannt2025×1429 216 KB

I used BuildTDataTable = WebScrappedData.Clone() without the brackets there is the error message above.

6

I replaced the write to database with an Excel for the time being. Writing to the Excel works fine for the original data table. Can’t get the clone data table to work at the moment. The assign is probably not the correct activity. Right?

7

Any hints?

8

Use the below method using the assign activity

ExtractedDataTable.Columns(0).ColumnName = “CID”

It will surely resolve your issue and you can change your column name

cheers

9

Hi @relsig,

Possible quick resolution: I would suggest you go to the webpage where you scrape the datatable and right click on the table header and use “inspect”. You can then within the browser inspect window copy the string value highlighted. This way you know what the actual string Header is within HTML. However, while scrapping data fom a webpage UiPath might also extract some non ASCII characters present in the HTML header. But sometimes this might actually show the decoded string but when UiPath scrapes values it may also pickup encoded strings which may or may not have Non ASCII Characters. You can read this thread on how to avoid such characters

Here is what I would try if the above resolution fails.

How to check for datatable header string?

  1. Use assign activity and clone your extracted datatable. (SourceTable variable here is of a DataTable type argument in UiPath)
    SourceTable = WebScrappedData.Clone (in itself only will copy the headers and not populate any rows to SourceTable)
    So you can use SourceTable = WebScrappedData.AsEnumerable().Take(WebScrappedData.Rows.Count).CopyToDataTable()
    Translation: Hey, use the WebScrappedData as an Enumerable and choose all rows within it and copy to a new datatable named SourceTable.

  2. Get the first column name using a Log Message acitivity SourceTable.Columns(0).ToString
    Translation: Get me the name of the first column (0 index) in the SourceTable. Here max index will be the total number of columns.

----- OR Longer Approach --------

  1. Get all the column names from SourceTable (ColumnNames variable here is an array of strings)
    ColumnNames = (From dt In SourceTable.Columns.Cast(Of DataColumn) Select dt.ColumnName).ToArray()
    Translation: Use a dummy placeholder name dt and refer to columns in SourceTable select the attribute within it named ColumnName. Since there may be many columns in the SourceTable, return all of them as an array of strings.

  2. Inspecting what the first column string is using Log Message activity : ColumnNames(0).ToString
    Translation: Get me the first element (0 index) in the ColumnNames array. Here max index will be the total number of columns.

----- Final check -----
4. If the string which you get in the console from Step 3 does not look like “C ID” and C_ID" you know what the real value of the column name is and can use that in your further flows. If it still looks like it does in your WebScrappedData datatable you might want to check
SourceTable.Columns(0).ToString.Length or for Longer Approach - ColumnNames(0).ToString.Length
Since your string looks like “C ID”, ideally it should be of length 4 (number of characters). If its more, you probably have a Non ASCII character in there.