Hot answers tagged

11 votes

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

I have just had the same problem yesterday, and here is how I was able solve it: Although the "Disallow adhoc access" is not enabled at the Provider Options page (SQL Server Management Studio), the ...
user avatar
  • 111
8 votes

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

Determine if "Disallow adhoc access" is enabled for your provider. This can be found in SQL Management Studio via the following navigation path: Server Objects/Linked Servers/Providers/Microsoft.ACE....
user avatar
7 votes

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

I see this question is tagged both Excel and SQL Server. In Excel you could use the approach from here The formula in cell F2 above is =IF(SUM(IF(FREQUENCY(IF(LEN(B2:E2)>0,MATCH(B2:E2,B2:E2,0),""...
user avatar
  • 78.4k
6 votes

How to handle big data update based on excel spreadsheet

A Fast Method A probably faster way to do this is the following: Provide the data in a database table Execute a MERGE statement that merges the data from this table into the source table To ...
user avatar
  • 7,471
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 ...
user avatar
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/...
user avatar
  • 61.3k
5 votes
Accepted

Correct 'lines terminated by' for query

See the mydata.csv file at bottom of the answer. I created a table xy CREATE TABLE `xy` ( `fred` int(11) DEFAULT NULL, `mary` int(11) DEFAULT NULL, `billy` varchar(50) DEFAULT NULL ); From ...
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 ...
user avatar
  • 11.4k
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 ...
user avatar
  • 27.4k
4 votes
Accepted

How do I go from .xlsx to sql Server

You can use Excel to save the file as a .CSV (comma-separated-values), and use SQL Server Management Studio to import the file either into an existing table, or potentially a new table. In SQL Server ...
user avatar
  • 67.5k
4 votes

is automated data movement via Excel considered ETL?

The process you describe is very extremely techinically ETL, though if I were reviewing qualifications with a candidate during an interview for a position that required what I usually think of as ETL ...
user avatar
4 votes

How to read an excel file from a network machine using SQL?

The SQL Server Import and Export Wizard offers the simplest method to create a Integration Services package that copies data from a source to a destination. This will create a package for you, which ...
user avatar
  • 6,995
4 votes

How to read an excel file from a network machine using SQL?

While you could schedule an JET OLEDB type query as a SQL Agent job with something like the code below: SELECT * FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0', 'Data ...
user avatar
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)
user avatar
  • 22k
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 ...
user avatar
  • 27.4k
3 votes

01/01/1753 date is corrupt in Excel after export from SSRS

Excel specifically provides a message: Dates and times that are negative or too large are displayed as ######. So, that is a Excel limit. Further, you can type in strings that look like dates, but ...
user avatar
  • 13.8k
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; ...
user avatar
3 votes

Is there a free software that can import excel into mysql on mac?

Full disclosure.. I am the author of the following program. Give it a try and let me know what you think. I have tried many applications including the excel add-in from MySQL, but was not satisfied ...
user avatar
  • 131
3 votes
Accepted

One time data conversion in Oracle tables with excel data

If you do not have access to create Directory, then you can use SQL Loader to get data into the database (provided you have Oracle client installed on your system). An outline of the steps is provided ...
user avatar
  • 214
3 votes

Best way to export excel table into MySQL db

For 1-time: Save as text (csv) file. CREATE TABLE ... with the columns approximating the Excel columns. LOAD DATA LOCAL FILE ... into that table in MySQL. (If you need 'automation', there may be ...
user avatar
  • 69.5k
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 ...
user avatar
  • 29.1k
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 ...
user avatar
  • 15.4k
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 ...
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 ...
user avatar
  • 2,223
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 ...
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 ...
user avatar
  • 15.4k
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 ...
user avatar
  • 67.5k
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 ...
user avatar
  • 15.4k

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