January 13, 2017 at 11:42 am
Is there an efficient way to update backend SQL table through Excel?
I tried in Excel using a SQL connection and able to open the table, but when edit the data in excel, I noticed it is not changed in backend sql table.
Thanks
January 13, 2017 at 2:25 pm
sqlfriends - Friday, January 13, 2017 11:42 AMIs there an efficient way to update backend SQL table through Excel?
I tried in Excel using a SQL connection and able to open the table, but when edit the data in excel, I noticed it is not changed in backend sql table.Thanks
Caution: if other folks/processes are using the same table concurrently, you're going to create locking and blocking issues by doing this.
With respect to what you're asking for, I believe you can only make this work via either PowerPivot or custom built macros in Excel using VBA.
K. Brian Kelley
@kbriankelley
January 19, 2017 at 9:08 am
sqlfriends - Friday, January 13, 2017 11:42 AMIs there an efficient way to update backend SQL table through Excel?
I tried in Excel using a SQL connection and able to open the table, but when edit the data in excel, I noticed it is not changed in backend sql table.Thanks
You can use an Excel AddIn such as SQL Spreads (http://www.sqlspreads.com) to update the backend SQL table through Excel.
You can download a full functional free trial here.
Disclaimer: I am the author of this tool.
January 19, 2017 at 9:13 am
johannes.akesson 34965 - Thursday, January 19, 2017 9:08 AMYou can use an Excel AddIn such as SQL Spreads (http://www.sqlspreads.com) to update the backend SQL table through Excel.You can download a full functional free trial here.
Disclaimer: I am the author of this tool.
Why use a third party tool when SSIS comes with SQL server?
@OP, SSIS will likely be your solution here. You can read the Excel spreadsheet, do any transformations you need to do, table lookups, and then upsert (Update/Insert), etc as needed.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 19, 2017 at 9:31 am
Thom A - Thursday, January 19, 2017 9:13 AMjohannes.akesson 34965 - Thursday, January 19, 2017 9:08 AMYou can use an Excel AddIn such as SQL Spreads (http://www.sqlspreads.com) to update the backend SQL table through Excel.You can download a full functional free trial here.
Disclaimer: I am the author of this tool.
Why use a third party tool when SSIS comes with SQL server?
@OP, SSIS will likely be your solution here. You can read the Excel spreadsheet, do any transformations you need to do, table lookups, and then upsert (Update/Insert), etc as needed.
Yes, of course you can use SSIS, or just the Import Wizard in SSMS to import the Excel sheet.
The benefits of SQL Spreads is that end users can update the SQL Server table data directly from Excel, and also get an instant confirmation that the data is valid and does not conflicts with any constrains.
January 19, 2017 at 10:22 am
Thom A - Thursday, January 19, 2017 9:13 AMjohannes.akesson 34965 - Thursday, January 19, 2017 9:08 AMYou can use an Excel AddIn such as SQL Spreads (http://www.sqlspreads.com) to update the backend SQL table through Excel.You can download a full functional free trial here.
Disclaimer: I am the author of this tool.
Why use a third party tool when SSIS comes with SQL server?
@OP, SSIS will likely be your solution here. You can read the Excel spreadsheet, do any transformations you need to do, table lookups, and then upsert (Update/Insert), etc as needed.
Did you really just advise someone to load from Excel to SQL Server using SSIS, Thom? 😛 SMH
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 19, 2017 at 10:27 am
Oh wow, removing a quote on Firefox mobile just causes it crash.
And yes... Yes I did... What I didn't say is use the SSIS Excel source and take it as is. I do still feel dirty for suggesting SSIS and Excel, but it's not really SSIS' fault, it's the ACE drivers.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 19, 2017 at 10:46 am
You don't need SSIS. The ACE drivers work just fine. I install them as a matter of rote on every system.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 19, 2017 at 10:55 am
Jeff Moden - Thursday, January 19, 2017 10:46 AMYou don't need SSIS. The ACE drivers work just fine. I install them as a matter of rote on every system.
IMO, the ACE drivers are a pile of horse**** and not fit for purpose.
Not only that, what they return depends on how data was formatted. How many people have been tripped up by getting a '-' returned after a column has been formatted as 'Accounting'? (Rhetorical)
How about a column that contains numeric data until row 1000, when there's an 'x' in there? Can you tell the ACE driver to treat that column as text? Good luck with that, unless you're into registry hacks.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 19, 2017 at 11:35 am
Jeff Moden - Thursday, January 19, 2017 10:46 AMYou don't need SSIS. The ACE drivers work just fine. I install them as a matter of rote on every system.
Completely agree with Phil here, ACE is awful. However there are ways of dealing with problem children 😛 Just a shame that Microsoft doesn't tell it to say in the corner and reveals that it had a child with a proper data engine 😉 (and I don't mean SQL).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 19, 2017 at 12:12 pm
Phil Parkin - Thursday, January 19, 2017 10:55 AMJeff Moden - Thursday, January 19, 2017 10:46 AMYou don't need SSIS. The ACE drivers work just fine. I install them as a matter of rote on every system.IMO, the ACE drivers are a pile of horse**** and not fit for purpose.
Not only that, what they return depends on how data was formatted. How many people have been tripped up by getting a '-' returned after a column has been formatted as 'Accounting'? (Rhetorical)
How about a column that contains numeric data until row 1000, when there's an 'x' in there? Can you tell the ACE driver to treat that column as text? Good luck with that, unless you're into registry hacks.
Haven't run into the "formatted as Accounting" problem before. I'll have to give that a try.
Agreed on the second part about mixed columns but I've beat that particular problem without a registry hack by reading in the column names as data along with the rest of the rows. It's necessary to figure out how to auto-magically unpivot like-data across, say, multiple months and to also auto-magically capture the column names without duplication for the final result. If they add another month or even a set of totals for a quarter/year, the code auto-magically figures all that out with no prior knowledge on the part of humans. It's totally "PFM2". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 19, 2017 at 12:31 pm
Jeff Moden - Thursday, January 19, 2017 12:12 PMPhil Parkin - Thursday, January 19, 2017 10:55 AMJeff Moden - Thursday, January 19, 2017 10:46 AMYou don't need SSIS. The ACE drivers work just fine. I install them as a matter of rote on every system.IMO, the ACE drivers are a pile of horse**** and not fit for purpose.
Not only that, what they return depends on how data was formatted. How many people have been tripped up by getting a '-' returned after a column has been formatted as 'Accounting'? (Rhetorical)
How about a column that contains numeric data until row 1000, when there's an 'x' in there? Can you tell the ACE driver to treat that column as text? Good luck with that, unless you're into registry hacks.Haven't run into the "formatted as Accounting" problem before. I'll have to give that a try.
Agreed on the second part about mixed columns but I've beat that particular problem without a registry hack by reading in the column names as data along with the rest of the rows. It's necessary to figure out how to auto-magically unpivot like-data across, say, multiple months and to also auto-magically capture the column names without duplication for the final result. If they add another month or even a set of totals for a quarter/year, the code auto-magically figures all that out with no prior knowledge on the part of humans. It's totally "PFM2". 😉
I use the 'read the column names to force everything to text' trick too, but I shouldn't have to. I'm looking forward to the day I get a file with no column names ...
Another thing I remembered. If my spreadsheet was 60k rows long, but has subsequently been edited to contain only 50 rows, the damned ACE drivers return 59,950 empty rows which I have to direct into the trash.
And only last week we found that someone had reformatted a column of rates (which can have up to 4 decimal places) to display as 2 decimal places. From then on, the ACE drivers return the data with decimal places 3 and 4 truncated. Unless you have a ton of validation in place, it's really easy to miss stuff like this.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 19, 2017 at 12:45 pm
Phil Parkin - Thursday, January 19, 2017 12:31 PMJeff Moden - Thursday, January 19, 2017 12:12 PMPhil Parkin - Thursday, January 19, 2017 10:55 AMJeff Moden - Thursday, January 19, 2017 10:46 AMYou don't need SSIS. The ACE drivers work just fine. I install them as a matter of rote on every system.IMO, the ACE drivers are a pile of horse**** and not fit for purpose.
Not only that, what they return depends on how data was formatted. How many people have been tripped up by getting a '-' returned after a column has been formatted as 'Accounting'? (Rhetorical)
How about a column that contains numeric data until row 1000, when there's an 'x' in there? Can you tell the ACE driver to treat that column as text? Good luck with that, unless you're into registry hacks.Haven't run into the "formatted as Accounting" problem before. I'll have to give that a try.
Agreed on the second part about mixed columns but I've beat that particular problem without a registry hack by reading in the column names as data along with the rest of the rows. It's necessary to figure out how to auto-magically unpivot like-data across, say, multiple months and to also auto-magically capture the column names without duplication for the final result. If they add another month or even a set of totals for a quarter/year, the code auto-magically figures all that out with no prior knowledge on the part of humans. It's totally "PFM2". 😉
I use the 'read the column names to force everything to text' trick too, but I shouldn't have to. I'm looking forward to the day I get a file with no column names ...
Another thing I remembered. If my spreadsheet was 60k rows long, but has subsequently been edited to contain only 50 rows, the damned ACE drivers return 59,950 empty rows which I have to direct into the trash.
And only last week we found that someone had reformatted a column of rates (which can have up to 4 decimal places) to display as 2 decimal places. From then on, the ACE drivers return the data with decimal places 3 and 4 truncated. Unless you have a ton of validation in place, it's really easy to miss stuff like this.
Is it bad that I can only laugh at these problems now? :hehe:
I really hate the deleted columns/rows issue. I get a monthly Bordereau file that is 12MB is size (which is then promptly rejected by our Exchange server and I have to get from our Email Archive) and the only reason it's so big is that every row and column has "data". Yeah, if data involves rows 320ish to 65536 being blank, and Column T to IV being empty as well...Of course, I then need to tell ACE that I really don't care about any of those rows or columns...
A bit more on topic, but are we effectively telling the OP stay away from SSIS, or, more likely, stay away from SSIS and ACE? >_<
Can I has good Excel ETL plx? ^_^
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 19, 2017 at 2:35 pm
Phil Parkin - Thursday, January 19, 2017 12:31 PMJeff Moden - Thursday, January 19, 2017 12:12 PMPhil Parkin - Thursday, January 19, 2017 10:55 AMJeff Moden - Thursday, January 19, 2017 10:46 AMYou don't need SSIS. The ACE drivers work just fine. I install them as a matter of rote on every system.IMO, the ACE drivers are a pile of horse**** and not fit for purpose.
Not only that, what they return depends on how data was formatted. How many people have been tripped up by getting a '-' returned after a column has been formatted as 'Accounting'? (Rhetorical)
How about a column that contains numeric data until row 1000, when there's an 'x' in there? Can you tell the ACE driver to treat that column as text? Good luck with that, unless you're into registry hacks.Haven't run into the "formatted as Accounting" problem before. I'll have to give that a try.
Agreed on the second part about mixed columns but I've beat that particular problem without a registry hack by reading in the column names as data along with the rest of the rows. It's necessary to figure out how to auto-magically unpivot like-data across, say, multiple months and to also auto-magically capture the column names without duplication for the final result. If they add another month or even a set of totals for a quarter/year, the code auto-magically figures all that out with no prior knowledge on the part of humans. It's totally "PFM2". 😉
I use the 'read the column names to force everything to text' trick too, but I shouldn't have to. I'm looking forward to the day I get a file with no column names ...
Another thing I remembered. If my spreadsheet was 60k rows long, but has subsequently been edited to contain only 50 rows, the damned ACE drivers return 59,950 empty rows which I have to direct into the trash.
And only last week we found that someone had reformatted a column of rates (which can have up to 4 decimal places) to display as 2 decimal places. From then on, the ACE drivers return the data with decimal places 3 and 4 truncated. Unless you have a ton of validation in place, it's really easy to miss stuff like this.
Thanks for the heads up on all this, Phil. You sound like the perfect candidate to do a technical review on the article I'm writing for all this. Any interest there?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 19, 2017 at 5:13 pm
Jeff Moden - Thursday, January 19, 2017 2:34 PMPhil Parkin - Thursday, January 19, 2017 12:31 PMJeff Moden - Thursday, January 19, 2017 12:12 PMPhil Parkin - Thursday, January 19, 2017 10:55 AMJeff Moden - Thursday, January 19, 2017 10:46 AMYou don't need SSIS. The ACE drivers work just fine. I install them as a matter of rote on every system.IMO, the ACE drivers are a pile of horse**** and not fit for purpose.
Not only that, what they return depends on how data was formatted. How many people have been tripped up by getting a '-' returned after a column has been formatted as 'Accounting'? (Rhetorical)
How about a column that contains numeric data until row 1000, when there's an 'x' in there? Can you tell the ACE driver to treat that column as text? Good luck with that, unless you're into registry hacks.Haven't run into the "formatted as Accounting" problem before. I'll have to give that a try.
Agreed on the second part about mixed columns but I've beat that particular problem without a registry hack by reading in the column names as data along with the rest of the rows. It's necessary to figure out how to auto-magically unpivot like-data across, say, multiple months and to also auto-magically capture the column names without duplication for the final result. If they add another month or even a set of totals for a quarter/year, the code auto-magically figures all that out with no prior knowledge on the part of humans. It's totally "PFM2". 😉
I use the 'read the column names to force everything to text' trick too, but I shouldn't have to. I'm looking forward to the day I get a file with no column names ...
Another thing I remembered. If my spreadsheet was 60k rows long, but has subsequently been edited to contain only 50 rows, the damned ACE drivers return 59,950 empty rows which I have to direct into the trash.
And only last week we found that someone had reformatted a column of rates (which can have up to 4 decimal places) to display as 2 decimal places. From then on, the ACE drivers return the data with decimal places 3 and 4 truncated. Unless you have a ton of validation in place, it's really easy to miss stuff like this.
Thanks for the heads up on all this, Phil. You sound like the perfect candidate to do a technical review on the article I'm writing for all this. Any interest there?
You bet. Bring it on!
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply