Questions tagged [excel]
Microsoft Excel is a commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and macOS. Most Excel questions are off-topic here but can be asked on Super User; notable exceptions are questions about Excel files connecting to other databases.
167
questions
0
votes
0
answers
33
views
How edit .sql file in windows (excel)
I'm not very expert in SQL, i just want edit some tables on my website's phpmyadmin and it takes a lot of time.so I want to export the sql file and edit it on Microsoft excel or any similar tools, but ...
- 1
0
votes
0
answers
16
views
Data source to extract millions of rows of data out of hundreds of excel files which can be DirectQueried
At the moment, we have powerBI set up to extract data directly out of excel files. However, we are running out of memory to create the dataset and looking to make use of the DirectQuery function of ...
- 1
0
votes
1
answer
57
views
SQL Server export data from synonyms table to excel
I tried to export synonyms table from my server database to excel using SMSS, I choose database, then I choose export, and manage source and dest, then I choose Excel File and I rename sheet 1 to name ...
- 3
6
votes
3
answers
514
views
How to write script to kill MS Office database locks in MSSQL
I've recently inherited a MSSQL database as our DBA has moved on to greener pastures. I'm not a DBA and have limited to moderate SQL knowledge but have had to absorb some of his duties and have an ...
- 61
1
vote
1
answer
46
views
Upgrading from excel to an actual database
Just started my first internship and I could use some guidance on approaching the project given to me. Simply put, all that needs to be done is to make a database and then transfer the data from a ...
- 11
0
votes
0
answers
101
views
Open rowset with ACE.OLEDB provider
I am trying to get a user setup to be able to query excel spreadsheets from sql server. I have installed the 2010 Access Database engine on the server. I am able to run queries against the excel ...
- 676
-2
votes
3
answers
1k
views
Automate SQL Server reports and send them by mail
I work in a large retail business with a decent SQL Server environment.
We have SQL Server replication, Availability Groups, Data warehouse, and some cloud services. In this beautiful environment, we ...
- 45
0
votes
0
answers
9
views
Trying to input table-format data using Access Data Input form
We're trying to format and input some scientific data into an Access database, and unfortunately the source of the data is in Excel files with very inconsistent formatting, so we can't use a blanket ...
-1
votes
1
answer
48
views
Sometimes, not always, when loading an MSSQL table from Excel a new table suffixed with a $ is created by MSSQL. Why?
We are loading MSSQL tables via Microsoft Management Studio Import. Sometimes, not always, a new identical table is created in the load process with a $ at the end of the name. Why does this happen? ...
0
votes
0
answers
304
views
How to transfer data from one table to another with exception handling in Postgresql?
So, I have two tables, one where I import data from an excel file all in string format without considering any sorts of constraints or data type validation. And then the data is transferred to the ...
- 111
0
votes
1
answer
43
views
Consolidate / sort / combine / merge data for dummies
I am looking for a non-scripting solution to combine data from 3 different tables (preferably using spreadsheet software like Microsoft Excel but a database would also work e.g. Microsoft Access). I ...
- 103
1
vote
1
answer
230
views
Running Excel addin for MDS2019 vs MDS2016
We are upgrading master data services (MDS) of SQL2016 to SQL2019.Is there a way to run excel 2019 addin for connecting to SQL 2019 MDS along side(parallely) with excel 2016 addin for connecting to ...
- 83
1
vote
0
answers
13
views
Autofill spreadsheet data from Excel [closed]
So I've got an Excel template I send to clients and they fill in one cell with a type of resource they need, and I check an Access database for available resources to find what we have and fill out ...
- 11
0
votes
1
answer
279
views
Export mutliple query output into one excel sheet using Redshift and SQL Developer
I am connected to Redshift from SQL Developer 19.2.1.
I have a requirement to export multiple query output into a single excel sheet, one query output should be in one Tab. Please advise on how to ...
0
votes
1
answer
68
views
Problem with IF / BEGIN / ELSE
I'm trying to run a select IF there's something inside a folder:
INSERT INTO #tb (SubDirectory, Depth, [File])
EXEC master..xp_dirtree 'C:\folder\', 10, 1
This will populate a table with the ...
- 7,198
0
votes
1
answer
750
views
Windows 10, SQLServer Express (2019), Excel 2016 import to SQLServer error Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine
Environment:
Workstation: 16GB RAM. 500MB SSD
Windows 10 Pro (64-bit)
Microsoft Office 2010? 2016? (Home/Student?) Not sure how to tell exactly. Office reports the version as 18.2005.1191.0 ; Excel ...
- 11
0
votes
1
answer
515
views
Get SSIS to check if excel file is updated?
So I have a SSIS package that reads from an excel file and populates a Sql server database. It assumes that the file will be updated monthly, and consequently, the data pull to update it is also done ...
- 103
-2
votes
1
answer
74
views
Multiple queries in phpmyadmin - Distance using coordinates, Slope, Intercept, Angle, and few more
I having around 500 excel sheets in .csv format with data captured for my experiment having following columns in place.
Now I need to calculate the following parameters using this data. I have done ...
- 47
0
votes
1
answer
1k
views
CopyFromRecordset Not Working
Hi I Inserted A Select MaxID+1 Data and want to retrieve what was that ID I inserted the Following is the codes
Im trying to Insert data from Excel to Ms Access and Automatically Retrieve that Data.
...
- 1
0
votes
0
answers
1k
views
Creating blank/deleting rows form excel using SSIS
I have an .xlsx file I import data from nightly.
Once the import is complete I delete the tables(sheets/tabs) in the excel file using an Execute SQL Task so that the file is blank for the user but ...
- 123
0
votes
0
answers
803
views
How to troublshoot updating to MSOLEDBSQL driver that is breaking macro-enabled Excel book?
I have an Excel macro-enabled file (.xlsm) with VBA that builds various SELECT statements and also executes a stored procedure on a SQL Server DB in order to populate a table. It is from a legacy ...
- 15
0
votes
0
answers
36
views
How do I link two Access Tables so if one Table gets updated both will?
I have an access database with a table that is linked to an excel sheet. I also have a duplicate table with the same data linked to a postgres database. My goal is to either link these two tables in ...
- 101
0
votes
1
answer
66
views
How can I import multiple .xlsx and .xls files into multiple tables in Access?
I have 100 .xslx and .xls files with different formats and I need to import them all separately into Access keeping their original file names intact.
Folder example:
C:\Users\username\Desktop\...
0
votes
2
answers
62
views
Best way to enter data in DB that is spread out date wise
I have an Excel file that, to keep it simple here, has 3 columns: ActivityID, ActivityName and TotalHours.
TotalHours column is basically all the hours allotted to that specific ActivityID and the ...
- 1
0
votes
1
answer
212
views
Calculate sum of N preceding values
I am write a SQL query to replicate below Excel result.
In Column A I have 252 values in ASC order from 10/30/2018 to 10/29/2019
In Column BV I have respective PL Number
I want to calculate TestPL ...
- 93
3
votes
0
answers
730
views
Extremely long import times from Excel File to SQL Server Management Studio using Import Wizard
I have been importing an excel file with approximately 250,000 rows of data and 40 columns to SSMS on a weekly basis through the Import Wizard. Unfortunately, the import times take around 45 minutes, ...
- 31
0
votes
2
answers
451
views
Tools for SQL Deadlock Statistics [closed]
One of my company's SQL database generates a lot of deadlocks per day recently (someday >100)
I'm trying to troubleshoot the causes of these deadlocks, i noticed that many deadlocks occur at the same ...
0
votes
1
answer
1k
views
Import an xlsx file to Oracle SQL Developer and interpret it as a table
I have an xlsx file with content in the form of a database table - first row is column names, and columns contain varchars2, integers etc.
Since VBA and Excel worksheet functions are too slow for it, ...
- 101
0
votes
3
answers
3k
views
Extracting data from extremely large csv files [closed]
I have a 40gb csv file with over 60 million rows for data analysis. Each row has a unique identifier (some numbers). For example, the first row's unique identifier will repeat approximately 150,000 ...
1
vote
0
answers
2k
views
Import Date in proper format from Excel to SQL Server through OPENROWSET query
I am trying to import huge data without any data loss from excel sheet to SQL Server. The excel sheet has few columns with mixed data types and few column with dates. I have prepared a sample file as ...
- 765
0
votes
0
answers
101
views
Adding PostgreSQL data source to excel didn't ask for credentials other than server IP and database
I saw Excel supported using PostgreSQL as a data source. I'm used to using ODBC connections for Access so having direct Postgres support is exciting.
When connecting I entered the Server IP and ...
- 147
0
votes
1
answer
1k
views
I gave 3 large Excel files need to be loaded in Single SQL table
I have 3 large Excel files each sized 56896 KB, I need to load all 3 files into same SQL table. what is better way to handle?
since My excel has more data, my excel hang up when I open and search any ...
2
votes
1
answer
394
views
Excel / MS SQL Server: Exception of type Microsoft.Office.PowerPivot.BackEnd.StorageException
We have an Excel table that gather data from our datawarehouse db on a SQL Server 2016. It queries a relative simple view to find the top customers of the last few days (including some aggregated ...
- 845
1
vote
1
answer
494
views
Excel grab data from MS SQL Server with WHERE clause based on cell contents [closed]
There's a bunch of questions for similar things, but I've been unable to find anything that fits.
We have three columns:
Name | ID | Cost
Thingy 3 25
Other 11 ...
- 133
0
votes
1
answer
69
views
Are iSeries AS/400 (DB2?), Lotus 1-2-3, and Excel date/time formats the same? [closed]
Are the formats for dates/times from AS/400, to Lotus 1-2-3 that carried over into excel...with the date being the number of days since 1900-1-1 when mistaking 1900 for a leap year as an integer, and ...
- 1,692
0
votes
2
answers
685
views
Import data from excel to table in sql
Please I have issue that when I try to import data from excel to table in SQL it fails with below errors even I modified all the tables in the tables to be nvarchar(max) and I tried to enter dummy ...
- 11
0
votes
1
answer
273
views
Postgresql function equivalent to Excel's NORMSINV function?
I need Excel's NORMSINV equivalent function for PostgreSQL, preferably plpgsql function. Can somebody help?
For reference, here is Excel's normsinv function explained:
https://support.microsoft.com/en-...
- 173
0
votes
1
answer
2k
views
Excel Values Uploading as NULL to database [closed]
I have an Excel file in which I have many records. When I upload this file to a table in a database (SQL Server) some values save as NULL even though the Excel file does not have NULL or blank fields.
...
- 103
-2
votes
1
answer
562
views
SQL query for import data [closed]
How can I write a single line SQL for importing 20k rows data from Excel to SQL server ?
Please don't tell me about import export wizard or to write 20 line repeatedly for do the same.
Please tell ...
1
vote
1
answer
868
views
Excel query execution status
I have inherited this huge Excel file that has a connection to the database and it will retrieve data on opening. This file is huge and it takes a long time to update (hours) and I'd like to know ...
- 69
-1
votes
1
answer
3k
views
Foreach Loop Enumerator with multiple File Names
I have a directory like C:\NewExcelFile in which I have lots of different Excel files with different names (but with the same extension .xlsx). I have a "foreach loop enumerator" in my package which I ...
user157749
0
votes
2
answers
1k
views
Different calculations in EXCEL and TERADATA SQL
The calculation select (75/979.86)*979.86 in Teradata SqL gives 78.3888. In excel, it would give 75 simply and that is logically correct. I get it that SQL is rounding off 75/979.86 to 0.08 ie the ...
- 115
2
votes
0
answers
590
views
Export table from MS Access to Excel while retaining Field Name and disregarding Caption
I have a MS Access Database, with each table containing the data of a particular questionnaire. I am building data entry forms for each of these tables, but as the field names may prove to be ...
- 121
1
vote
0
answers
167
views
OPENROWSET error accessing XLSX if not manually saved
Good day,
Apologies if this is in the wrong forum. Please refer me to the correct one if this is the case, thank you.
I have a bunch of excel files that are added to a Dropbox folder on a monthly ...
- 11
0
votes
2
answers
10k
views
The EXECUTE permission was denied on the object 'SPROC', database 'DATABASE', schema 'dbo'
I have a few users that are unexpectedly receiving this error message today when they attempt to refresh a stored procedure via Excel:
The EXECUTE permission was denied on the object 'SPROC', ...
- 203
1
vote
1
answer
4k
views
How to correct SQL Server Import and Export Wizard error that says my data conversion length must be between 0 and 4000?
The message I get from the 2017 SQL Server Import and Export Wizard which I need help with is,
"Error 0xc0204016: SSIS.Pipeline: The 'Data Conversion 0 - 0.Outputs[Data Conversion Output].Columns[...
-3
votes
1
answer
2k
views
Database name 'mydb' ignored, referencing object in tempdb. Bulk Insert
I'm trying to insert data from an xlsx file into my temporal table. I've also tried with a real table, but it throws me the following error:
(0 rows affected)
I'm pretty sure my error is in the ...
1
vote
4
answers
8k
views
Transfer data from Excel to table
How can I transfer information from an Excel file to a table in a SQL server database through SSMS?
Excel file Consists of five columns and a thousand rows.
I followed the steps as explained here,at ...
- 119
0
votes
2
answers
757
views
Concurrent connections to the same table
We are a small firm, with about 10 people using a database server.
I have this application that connects to a database and fills about 40 rows of data in an table. Then calls a stored procedure to ...
- 23
0
votes
1
answer
9k
views
How can I merge data from an Excel file into an access database with multiple tables? [closed]
I have an access database with many internal tables.
Each table is a category of electronic components (resistor, capacitor etc)
In all tables, there is a manufacturer part number and a currently ...
