Jump to content
UBot Underground

Explode Table Rows For Sql Insert


Recommended Posts

I have scraped an html table with as many as 100 rows with 5 columns. Practically everything I have been trying treats the entire table as one variable. For instance, I'm able to write the table to a file.

 

What I want to be able to do is loop through each row, one by one and explode out the row to 5 variables: cell1, cell2, cell3, cell4, cell5.

 

I then want to use these (while still remaining in the loop for this row) in a SQL query:

INSERT INTO db_table (col1, col2, col3, col4, col5) VALUES ('cell1', 'cell2', 'cell3', 'cell4', 'cell5')

 

Seems very simple but I can't quite make this happen in Ubot. How do you break up (or explode) table rows out into variables like that?

 

Thanks.

Link to post
Share on other sites

Here you go,

$table cell is the function you are looking for.

save to file("{$special folder("Desktop")}\\test-data.csv","r1,cell 2,cell 3,cell 4,cell 5
r2,cell 2,cell 3,cell 4,cell 5")
comment("INSERT INTO db_table (col1, col2, col3, col4, col5) VALUES (\'cell1\', \'cell2\', \'cell3\', \'cell4\', \'cell5\')")
clear table(&goes into DB)
create table from file("{$special folder("Desktop")}\\test-data.csv",&goes into DB)
set(#table row index,0,"Global")
loop($table total rows(&goes into DB)) {
    set(#sql command,"INSERT INTO db_table (col1, col2, col3, col4, col5) VALUES (\'{$table cell(&goes into DB,#table row index,0)}\', \'{$table cell(&goes into DB,#table row index,1)}\', \'{$table cell(&goes into DB,#table row index,2)}\', \'{$table cell(&goes into DB,#table row index,3)}\', \'{$table cell(&goes into DB,#table row index,4)}\')","Global")
    alert(#sql command)
    increment(#table row index)
}


Regards,
CD

get table rows.ubot

Link to post
Share on other sites

Thanks so much Code Docta! One question: If I already have the data in a table form from scraping and html table, why would I need to do these steps where I write it to a file just to read it back into a table again?

 

save to file("{$special folder("Desktop")}\\test-data.csv","r1,cell 2,cell 3,cell 4,cell 5
r2,cell 2,cell 3,cell 4,cell 5")
comment("INSERT INTO db_table (col1, col2, col3, col4, col5) VALUES (\'cell1\', \'cell2\', \'cell3\', \'cell4\', \'cell5\')")
clear table(&goes into DB)
create table from file("{$special folder("Desktop")}\\test-data.csv",&goes into DB)



  • Like 1
Link to post
Share on other sites

You are welcome :)


you don't :) "$table cell is the function you are looking for."

Just easier to give entire example, just use what you need*($table cell). The rest is for the sake of the example.

  • Like 1
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...