6 votes
Accepted

Create a monthly job to export from SQL Server 2014 to Excel

PowerShell is the way to go: # Original Author : Bill Fellows (http://billfellows.blogspot.com/2011/03/powershell-export-query-to-csv.html) # Modified by : Kin Shah (http://dba.stackexchange.com/...
Kin Shah's user avatar
  • 62.2k
6 votes
Accepted

Can we export data from PostgreSQL to xlsx file?

PostgreSQL can't do this natively, but you can convert the CSV output to an XLSX file with a simple perl script. csv2xlsx: For this script to work, install Excel::Writer::XLSX and Text::CSV perl ...
Ezequiel Tolnay's user avatar
6 votes

How to write script to kill MS Office database locks in MSSQL

You can look at select session_id, program_name, host_name from sys.dm_exec_sessions But those program names can be chosen by the client, so there's no guarantee that you can identify the Excel ...
David Browne - Microsoft's user avatar
5 votes

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

First, make sure you've got the query options set up the way you want. Go to the Query menu, and choose Query Options: The first of the two highlighted options tells SSMS to include the column ...
RDFozz's user avatar
  • 11.6k
5 votes

Tools for SQL Deadlock Statistics

You should use the open source stored procedure sp_BlitzLock to analyze these deadlocks. It will give you what you're looking for in terms of timing, which tables / objects are involved in deadlocks ...
Josh Darnell's user avatar
  • 29.2k
4 votes
Accepted

Calculate sum of N preceding values

Use a window function ...sum(PL) over(order by [Date] range between 3 preceding and current row)
mustaccio's user avatar
  • 26k
4 votes

Problem with IF / BEGIN / ELSE

I don't have an easy way to test this theory right now, but I'd bet that there are some validation checks of the OPENROWSET parameters going on when the batch compiles (the whole batch, regardless of ...
Josh Darnell's user avatar
  • 29.2k
3 votes

PostgreSQL: alternative ways to connect to database

A poor man's solution might be simply installing pgAdminIII, setting up the connection to the database and using it's built in filtering and data view/modification functionality. I admit this does ...
András Váczi's user avatar
3 votes

SSIS keeps force changing excel source string to float

Step 1: Add IMEX=1; MAXROWSTOSCAN=0 in the connections string (e.g. Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Source\filename.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES;IMEX=1; ...
user1968485's user avatar
3 votes

Altering SSAS writeback behavior?

Writeback is always written to the leaf level of your dimension, and has to be because of the structure of your writeback table (where the difference between the original value and the new value is ...
Tom V's user avatar
  • 15.7k
3 votes
Accepted

High ASYNC_NETWORK_IO when user locks computer with Excel open

ASYNC_NETWORK_IO can cause database contention issues if the results being sent to the client never spool anywhere (e.g., via an ORDER BY clause forcing a sort in tempdb). If this issue is happening ...
John Zabroski's user avatar
3 votes

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

Like MguerraTorres suggests, SSRS has probably got confused somewhere, and the value suggests it's getting cast to some kind of floating point datatype somewhere along the line. Try checking the ...
Jerb's user avatar
  • 606
3 votes
Accepted

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

OHHH Man I completely misread your initial question. This is 100% an excel issue. You NEED to convert that value to text/string before you export it to Excel. You will get the same exact issue if ...
SQLDevDBA's user avatar
  • 2,254
3 votes

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

I was having same trouble with field and limiting the length of the output didn't help at all because it has to do with the carriage returns. Before you run the data you want output to Excel, change ...
Briana Finney's user avatar
3 votes

SSIS ACE.OLEDB.12.0 Driver issue

A common cause of this problem can be the difference between 32 bit and 64 bit installations. If your SSIS package is running in 64 bit the Access driver needs to be installed in 32 bit too. My ...
Tom V's user avatar
  • 15.7k
3 votes
Accepted

Database name 'mydb' ignored, referencing object in tempdb. Bulk Insert

You cannot specify a database name, or schema name, when using a #temporary table. Remove the # from the name of the stats_ddl table, and you'll see it works. If you want to insert the rows into a ...
Hannah Vernon's user avatar
  • 70k
3 votes
Accepted

Foreach Loop Enumerator with multiple File Names

If I understand correctly what you are trying to do I don't think you should put your expression in the Directory property. I think you need to put: Your directory name (C:\NewExcelFile\) in as the ...
Tom V's user avatar
  • 15.7k
3 votes

Extracting data from extremely large csv files

Powershell to the rescue! A CSV file is just a text file structured in a certain way. It doesn't have to open up in Excel, that's just what your default settings are configured to do at the moment. ...
Peter Vandivier's user avatar
3 votes

Extracting data from extremely large csv files

Bulk insert to SQL Server Who's that nutjob telling you to use Powershell?! Off topic! This is a site for DBAs, not developer nutjobs! Use the developer edition of SQL Server you've got installed on ...
Peter Vandivier's user avatar
3 votes

Import an xlsx file to Oracle SQL Developer and interpret it as a table

No. SQL Developer is just a client that interacts with an Oracle database. It is not a database in and of itself so it has no way to store and manipulate data other than via an Oracle database. You ...
Justin Cave's user avatar
  • 20.3k
2 votes

Microsoft Access Export to Excel

To export all the tables on Linux try this command: mdb-tables -d ',' database.mdb | xargs -L1 -d',' -I{} bash -c 'mdb-export database.mdb "$1" >"$1".csv' -- {} You can use mdbtools also into ...
Eduard Florinescu's user avatar
2 votes
Accepted

What are the minimum requirements for running a dtsx package on a PC?

You have delivered a solution that uses SSIS. SSIS requires an installation of SQL Server Standard, BI, or Enterprise Edition (depending on components used). At this point, your client will need to ...
billinkc's user avatar
  • 15.9k
2 votes

How do you export a database schema from SQL Server Management Studio to Excel?

I needed a similar solution, but with tables and column information too. We needed to translate a database to another language, including table and column names. This job was done by a translator that ...
Luiz Vaz's user avatar
  • 121
2 votes

Create a monthly job to export from SQL Server 2014 to Excel

I'd probably take a short solution to this: Create a SQL Agent Job Create a step using sqlcmd to BCP out the query and save it as a .csv file for relevant years Send to developer If he wants each ...
LowlyDBA - John M's user avatar
2 votes
Accepted

How to import excel to MS Access table design?

If you want to create a table using DDL (data definition language) you can use a create table statement You don't need the start or end position. But you do need a data type and field length. With ...
Sir Swears-a-lot's user avatar
2 votes

Problem with MS Excel MDS addon - unable to connect to the web app

One of the business user in my organization ran into the same issue. The MDS 2016 URL worked from his laptop browser but the same URL from Excel throwing the same exact error message. One of my ...
Saravanan Radhakrishnan's user avatar
2 votes

Updating SQL table from Excel sheet (using powershell)

Bulk load processes do just that - load data. They are not intended for updating existing data, or even for adding rows that don't already exist. As you surmised, the best method for those things is ...
RDFozz's user avatar
  • 11.6k
2 votes

Designing a database for Reading Comprehension

1st things first: Use TEXT for all character fields including small ones! In SQLite, VARCHAR is just TEXT behind the scenes anyway (same is true for PostgreSQL if ever you want to upgrade to a multi-...
Vérace's user avatar
  • 29.9k

Only top scored, non community-wiki answers of a minimum length are eligible