Database Administrators

Questions tagged [excel]

Ask Question

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.

155 questions
Newest
Active
Bountied
Unanswered
Filter by
Sorted by
Tagged with
0
votes
0answers
4 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. ...
0
votes
0answers
20 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 ...
-3
votes
0answers
8 views

How to quickly scrape/enter data from a website? [migrated]

I am doing a small project for which I have to manually scrape data from a website (students result). Now the issue is, I have a lot of fields (47) and the website is captcha protected. How can I get ...
0
votes
0answers
28 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 ...
0
votes
0answers
14 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 ...
0
votes
0answers
52 views

No Tables or Views Could be Loaded

Could not retrieve the table information for the connection manager 'Excel Connection Manager 3'. Failed to connect to the source using the connection manager 'Excel Connection Manager 3' How to ...
0
votes
1answer
26 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
0answers
58 views

MDS Add-in for Excel Office 365 - Error

I did setup Microsoft SQL Server 2016 Master Data Services (MDS) successfully but hitting an error (bug?) while installing the add-in for Excel - which can download/upload data from/into MDS ...
0
votes
0answers
31 views

OPENROWSET link from Access to Excel

I am trying to use Microsoft Office 12.0 Access Database Engine OLE DB provider to query an XLSX file with OPENROWSET. An attempt to query just spins in SSMS without error, failure, or a result set. ...
0
votes
2answers
26 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 ...
0
votes
1answer
42 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 ...
3
votes
0answers
106 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, ...
0
votes
2answers
152 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
1answer
499 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, ...
0
votes
3answers
933 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
0answers
811 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 ...
0
votes
0answers
26 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 ...
0
votes
1answer
310 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
1answer
165 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 ...
1
vote
1answer
302 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 ...
0
votes
1answer
54 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 ...
0
votes
2answers
163 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 ...
0
votes
1answer
106 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-...
0
votes
1answer
319 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. ...
-2
votes
1answer
382 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
1answer
142 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 ...
-1
votes
1answer
2k 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 ...
0
votes
2answers
624 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 ...
2
votes
0answers
238 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 ...
1
vote
0answers
113 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 ...
1
vote
2answers
5k 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', ...
0
votes
1answer
1k 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
1answer
1k 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 ...
0
votes
3answers
4k 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 ...
0
votes
2answers
88 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 ...
0
votes
1answer
4k 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 ...
1
vote
0answers
586 views

Sqlserver cannot write large text to excelfile with ACE, using IMEX=1, result=object is read-only

I have a table Test with column id (int) and column label (varchar(512)) and an excelfile test.xlsx with sheet test and the two columns. I use the ACE-provider to write from sqlserver to the excelfile....
0
votes
1answer
30 views

Designing a database for Reading Comprehension

So I am building an RC app that will require me to fetch data. I have data in Excel sheet and I would like to load the data in sqlite database. But I am having trouble designing the db. A little ...
0
votes
2answers
79 views

Extracting data from Excel files

How to extract data from spreadsheets or Excel to database using terminal in Ubuntu? I'm using MySQL in Ubuntu and now I have 3 different Excel files using which I need to create a database in SQL. ...
-1
votes
1answer
130 views

Creating Excel Spreadsheet with Database Table information

My Goal: I'm wanting an excel document showing basic information for each table. I'd be happy with the same information shown when you Alt+F1 on the table but ideally I want a separate sheet per table....
0
votes
2answers
3k views

64 bit Machine 32 bit Excel Using SQl Server Management Studion V17

I am using the below sql command to import an excel file (created in excel 2016) into SQL Server Management Studio on a 64bit Machine with 32 Bit Office. SELECT * FROM OPENROWSET('Microsoft.ACE....
0
votes
1answer
171 views

Exporting an Excel Spreadsheet containing a pivot table and charts to MySQL

I am looking at moving a large Excel spreadsheet to an SQL database - most likely MySQL. The excel spreadsheet contains 12 worksheets, with one of them being the Master, and the other 11 set up to ...
1
vote
2answers
475 views

SSIS ACE.OLEDB.12.0 Driver issue

We have a SSIS package which is using a Excel destination. It was brought to my notice that the job was failing with the common error message 'Microsoft.ACE.OLEDB.12.0' is not registered. I checked ...
2
votes
0answers
3k views

Opening a rowset for “Sheet1$” failed

I am using SSIS to export an Excel 2007 file to a SQL table. When I execute my package locally it works; however, on the server I get the following error: Data Flow Task:Error: Excel Source failed ...
1
vote
2answers
327 views

Pulling SQL Server data into Excel

I'm trying to pull data from an SQL Server database into Excel 2016 via ODBC / Microsoft Query (built in to Excel). I'd like to have a dropdown of companies, where after a company is selected from ...
3
votes
1answer
4k views

Excel Query Using MariaDB ODBC Driver

I'm using MariaDB ODBC driver version 3.0.2 for Windows(on Windows 10) and can't get excel (Excel 2013) to query the database. I'm able to connect to my MariaDB server using HeidiSQL and query just ...
2
votes
5answers
10k views

SQL Server: SSMS “Save Results As” creates CSV that Excel mangles

Using the Save Results As option, shown below. CSV looks good! But when Excel opens/imports it: Long strings are mangled as numbers (if the data is largely numeric) Dates are not detected properly ...
0
votes
1answer
252 views

MS-excel not accepting postgresql server certificate

I am trying to connect my MS-excel to postgresql 9.6 Firstly, It gave me error that it does not have the required library. So I downloaded ngpsql. After that, it gave me error that it cannot accept ...
4
votes
2answers
924 views

Why is this SSRS Report rounding a BIGINT Value to the nearest Thousandth when exporting to Excel?

I have a SQL Server Reporting Services (SSRS) 2012 report that is populated via stored procedure (SP). The SP returns various fields, one of which is a BIGINT datatype. When I execute the SSRS ...
0
votes
1answer
703 views

Updating SQL table from Excel sheet (using powershell)

I am using the below code to complete a bulk import into SQL, but I think I have run into a problem with my method. $serverName = "pjim-app-02\tsg"; $databaseName = "User_Data" ; $tableName = "dbo....

1
2 3 4