Tag: sql

Select on .csv file stops working after columns 1 and 2

I have the following line in a .csv file: 1234;Einkauf;26.08.2015;137873;616005;50308;Hiag;4100000;Place Rd. 8, 9999 Ort, HST;;;Test Place Rd. Ort;2000;VSM80;185.05;12;155.55;0.02 I run the following code: Set cn = CreateObject(“ADODB.Connection”) Set rs = CreateObject(“ADODB.Recordset”) cn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & strPath & “;Extended Properties=””text;HDR=Yes;FMT=Delimited””;” sQuery = vbNullString sQuery = sQuery & “SELECT * FROM ” & strFileName sQuery = sQuery […]

Sumproduct in Excel VBA using function arguments as source

I’m trying to write a VBA function that takes the function arguments as the source and then runs SQL commands on it. The SQL command I’m currently trying to implement as a first step is the equivalent of Excel’s sumproduct() function, which in SQL would be something along the lines of “SELECT SUM(A * B)”. […]

Refer to previous computed column in SQL

I am trying to calculate the val column using id column and the previous val column. On Excel, I simply use the formula “=IF(B2=1,1,C1+B2)”. How can i reference previous value of my column being computed? id val 1 1 2 3 3 6 4 10 5 15 6 21 7 28 8 36 9 45 […]

Excel 2013 OLAP Tools DAX Tabular Data Connection

I have an Excel 2013 pivot table on a tabular model data connection. I essentially want to slice the facttable.sales field by a calculated field on a product dimension that doesn’t exist in the model. Using my TSQL background to explain the field I want, the only way I know how: Case when productdimension.category = […]

Update SQL Server table from Excel data

Currently I am trying to import data from an Excel 2007 workbook into a SQL Server 2008 R2 table ‘test’. The import works perfectly, however, the data on the workbook is changed constantly and needs to be imported every ten mins or so. When I re-run the import, SQL Server just copies all the data […]

Pulling Data From .MDF or .LDF into Excel without a server

I’ve been at this for over a week straight now. I am trying to set up an excel file to pull data from a live .MDF and/or .LDF file on a server. Because of the sensitivity of the files, and updating live, I want to work with just dummy back ups of the files. But […]

How to get similar amount value in SQL as getting in Excel

When in excel (262.41*1011)/4 gives 66323.16227( cell format is accounting with 2 decimal places). Whereas in SQL it gives 66324.127500. Why and how can I get the same result as Excel.

VB.NET load Excel document into SQL Server database table via code

I want to load an Excel document into my VB.NET project and then load all the data from the Excel document into my SQL Server database. The table name in SQL Server TCustomers and it has the following columns: CustomerID, FirstName, LastName, OrderID This is how my data looks in the datagridview: CustomerID FirstName LastName […]

VB2010 export SQL dataset to Excel

As a very newbie to any form of coding following a long break, I have started a little project at work for which i’d appreciate a bit of help. I bit of background – When items are submitted to our office they’re input into a bespoke software which stores them in a MS SQL 2012 […]

Downloading .csv from SSRS need to avoid scientific notation format

Have an issue where when a report is downloaded, a field that has over 15 digits is converted to scientific notation instead of displaying the full digits (i.e. 672000000000000 = 6.72×10 to the 14th degree). I know this is an excel feature, but is there any workaround for it on the SSRS end? Thanks!

MS Excel Spreadsheet is the best Office Software, Excel VBA and Excel Formulas make Spreadsheet work faster.