Database Administrators Stack Exchange
Database Administrators Stack Exchange

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.

Microsoft Office Excel is a spreadsheet application written and distributed by Microsoft for Microsoft Windows and macOS. It features calculation, graphing tools, pivot tables and a macro programming language called VBA (Visual Basic for Applications).

The latest versions are Excel 2016 for Windows and macOS. Features include calculation, graphing tools, pivot tables and a macro programming language called VBA (Visual Basic for Applications). A new user-interface design, dubbed the "ribbon", was introduced with Excel 2007 for Windows. A similar UI was introduced to Excel 2011 for macOS.

Excel is the among most widely used spreadsheet programs, especially in business environments. The first version was introduced in 1993, and new versions have been released every 2-3 years since then.

Excel is not a database, but can interact with databases and does have a table layout that can hold data in a way similar a database table.

161 questions
11
votes
3 answers

SSIS keeps force changing excel source string to float

There is a column in Excel that is supposed to be text, but SSIS sees numeric text there and automatically makes it double-precision float [DT_R8]. I can change it manually in the Output branch's External and Output columns, but the Error Output…
Juan Velez
  • 3,165
  • 13
  • 48
  • 67
9
votes
3 answers

Help with SQL Server Error "ad Hoc access to OLE DB Provider 'Microsoft.ACE.OLEDB.12.0'"

So we have an interesting issue where users are receiving the following error when attempting to query a view in SQL Server 2008 R2: Msg 7415, Level 16, State 1, Line 1 Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied.…
Johnny-boy
  • 91
  • 1
  • 1
  • 3
8
votes
1 answer

Can someone explain the magic of Opendatasource/Openrowset?

When pulling information from an excel file (or ms-access DB) I commonly use something like this: SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\test.xls;Extended Properties=''EXCEL 12.0;HDR=NO;IMEX=1''…
SqlSandwiches
  • 1,597
  • 1
  • 14
  • 20
7
votes
2 answers

Importing a large column (8000 chars) from Excel using SSIS

I'm trying to set up a regular import of an excel spreadsheet that we get from a vendor. I'm using SQL 2008 R2 SSIS to import it into a table. The problem connection manager is an OLE DB connection to the spreadsheet. The spreadsheet is Excel…
Kenneth Fisher
  • 23,537
  • 9
  • 54
  • 106
6
votes
1 answer

SSAS Cube in Excel - Show only applicable rows with KPI?

I have a cube built in SSAS 2008r2. I'm exposing it for the moment through an excel PivotTable. I recently added a KPI to the cube, and when I show the KPI in my PivotTable interface it causes non-applicable dimension values to be shown at the…
JNK
  • 17,718
  • 5
  • 56
  • 97
5
votes
1 answer

Schedule Query and Export

Is it possible, to schedule a simple Query to run Daily and export the received data into an excel file?
TheJoeIaut
  • 205
  • 3
  • 5
5
votes
4 answers

Compare each column in a row and return an error if any of them is different

I'm attempting to find any identifiers with any values that are different in the same row. +------------+--------+--------+--------+--------+ | Identifier | Value1 | Value2 | Value3 | Value4 | | f001 | a1 | a2 | a2 | a2 | |…
John Nguyen
  • 51
  • 3
5
votes
3 answers

is automated data movement via Excel considered ETL?

I am wondering if informal methods of automatically extracting, transforming, and loaded data constitute "ETL." For example, I code Excel VBA to extract from databases onto a spreadsheet, transform the data into the desired format, and load by…
brietsparks
  • 151
  • 4
4
votes
2 answers

SELECT permission denied trying to import data into Excel

We recently migrated to a newer version of a program and migrated its database from SQL Server 2000 to SQL Server 2008 R2. We had some Excel files which ran a few custom queries to pull data. I changed the values in the connection string to reflect…
jrob007
  • 41
  • 1
  • 3
4
votes
2 answers

Slow Performance of Parametrized Query in Excel due to Parameter Datatype

I'm pulling Data from a SQL Server 2008 into Excel using a Parametrized ODBC Query created by the Excel Wizard. (Data-->getExternalData/FromOtherSources-->FromDataConnectionWizard-->ODBC DSN) My query faces a huge Performance Problem as soon as I'm…
Hellvetia
4
votes
1 answer

Exporting SSRS report to Excel

We have a number of SSRS reports and want to show the report in Excel format to the users. SSRS has provided an option to save the report in Excel format (.xls), however when the report is saved as Excel, all the cell borders are gone and the spread…
Sky
  • 3,634
  • 15
  • 45
  • 67
4
votes
2 answers

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 report directly the value is properly displayed, but…
John Eisbrener
  • 8,970
  • 5
  • 23
  • 55
4
votes
3 answers

Microsoft Access Export to Excel

I have an Excel spreadsheet that is filled with a ton of data which is organized by column (a column for names, a column for occupation etc.). I have recently moved all of this data into Microsoft Access 2010 to work with, but I do require to move…
CDZ
  • 41
  • 1
3
votes
1 answer

Wich is the most efficient/easy way for end-user to get data from Analysis database?

I know that you can quey a cube by connecting from excel to Analysis database and using formulas like cubevalue() or cubemember(). I also know that after converting the power pivot to formulas you can access the attribute and the value related only…
Pirvu Georgian
  • 131
  • 5
3
votes
1 answer

OPENROWSET can't import an excel spreadsheet into SQL Server 2005 Express

I am trying to run the following query to select data from an excel spreadsheet: SELECT FT_ID_SOCIETE_FF2C,FT_NOM,FT_ADR1,FT_ADR2,FT_ADR3,FT_CP,FT_VILLE,FT_TEL,FT_FAX,F10 FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel…
Craig Efrein
  • 9,418
  • 11
  • 53
  • 94
1
2 3
…
10 11