Thank you so much. This is helpful! Can you assist me with something similar, I’m trying to do the same process using VBA on excel to upload 1000+ rows by 30 columns of data to sql. Because I work remotely, it tends to take a long time when I use recordset with batch update in vba. Is there another option? If you DM me, I can share my code with you.
1000+ rows over 30 columns = 30,000 values, which is a relatively small dataset for a recordset and SQL Server. The only thing I can think of causing your slow insertion is probably the efficient use of code blocks.
@@jiejenn I use the following code to store the date from the spreadsheet in new recordset then "UpdateBatch" the recordset to SQL. It takes around 7 minutes for those 30,000 pieces of data to upload. To test it, I looked at the table in SQL as this code is running refreshing every couple seconds the query to see how many rows of data are being added while the code is running. I am uncertain if the hang up is on the SQL side (remotely hosted) because it's taking a while to update there then send the response to my VBA Script to say the job is done or if the method I am using is the reason for why it's taking SQL so long to process the data. Set rs = New ADODB.Recordset Set Cn = New ADODB.Connection For RowCounter = StartRow To EndRow rs.AddNew For ColCounter = 1 To NoOfFields rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter) Next ColCounter Debug.Print RowCounter Next RowCounter rs.UpdateBatch
Maybe this post might help social.technet.microsoft.com/Forums/lync/en-US/16023eaa-5c93-4398-a85d-4c77726faee4/error-hyt00-microsoftodbc-sql-server-driver-query-timeout-expired?forum=sqldatabaseengine
I searched for this video for months i couldnt find 😭 , thank you very much for sharing such info . Pls continue what u doing
Glad my video helped.
Thanks for the videos, a Suggestion: if you had a discord server, that would be great to help other subscribers and also get help from them. :)
can i also use this method with mysql workbench
You should be able to. Just makes sure you download mysql driver first.
Is there a way to have the Google Sheets data automatically be sent to a remote SQL server's database?
Thank you so much. This is helpful!
Can you assist me with something similar, I’m trying to do the same process using VBA on excel to upload 1000+ rows by 30 columns of data to sql.
Because I work remotely, it tends to take a long time when I use recordset with batch update in vba.
Is there another option?
If you DM me, I can share my code with you.
1000+ rows over 30 columns = 30,000 values, which is a relatively small dataset for a recordset and SQL Server. The only thing I can think of causing your slow insertion is probably the efficient use of code blocks.
@@jiejenn I use the following code to store the date from the spreadsheet in new recordset then "UpdateBatch" the recordset to SQL. It takes around 7 minutes for those 30,000 pieces of data to upload. To test it, I looked at the table in SQL as this code is running refreshing every couple seconds the query to see how many rows of data are being added while the code is running. I am uncertain if the hang up is on the SQL side (remotely hosted) because it's taking a while to update there then send the response to my VBA Script to say the job is done or if the method I am using is the reason for why it's taking SQL so long to process the data.
Set rs = New ADODB.Recordset
Set Cn = New ADODB.Connection
For RowCounter = StartRow To EndRow
rs.AddNew
For ColCounter = 1 To NoOfFields
rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter)
Next ColCounter
Debug.Print RowCounter
Next RowCounter
rs.UpdateBatch
I have a problem that some of my last values in a row are null, and them it returns less columns to be assigned via this ? method.
EDIT:
for lst in response['values']:
while len(lst) < YOUR_LEN_SIZE:
lst.append('')
Im having an error when no such file directory client _secret.json
can we use this script under the button?so the end user can run it if there is any new file in the google drive?
Sure. Why not.
@@jiejenn do you have the tutorial for import files from google drive using pythin tkinter button?
I don't use tkinter unfortunately.
Please make a video on Connect Google Sheets with sql server using app script.
How can I download the json file?
huhu your video to use google cloud platform had been removed 😭
How can i solve this?
hyt00 microsoft odbc sql server driver query timeout expired
Maybe this post might help social.technet.microsoft.com/Forums/lync/en-US/16023eaa-5c93-4398-a85d-4c77726faee4/error-hyt00-microsoftodbc-sql-server-driver-query-timeout-expired?forum=sqldatabaseengine
@@jiejenn the proclem could be the type of SQL SERVER? cause i use Developer edition
i dont have pandas in my phython when trie dto install i get error saying ModuleNotFoundError: No module named 'pandas', please can anyone guide
You need to install pandas library first. pip install pandas.