Tag: sql-server

SSIS Script task to delete 2nd row of xls (excel)

When exporting data from DB to excel all of my data became string(text) so I created excel(xls) with header and dummy row (2nd row) to follow the format of my second row when exporting the data. My problem now is how to delete the second row or the dummy row after exporting the date in […]

Insert value beside imported data from Excel into SQL Server via vb.net

I am facing an issue as below: I’m creating a daily performance management tool, so I need to import Excel files into a SQL server table and I need to import these values with a specific date to be picked from a textbox control. I can import normally from Excel into the database but can’t […]

Hosting Data for Excel Users

researching options here… I have a C# (winforms) application which allows users to enter some data into a SQL database. There are two SQL server database locations Americas(2012 v10)/Europe(2014 v11) and the tables are merge replicated. The structure is relatively simple, one table with some relational selector tables. However a flat view can be created, […]

How to reduce pivot size and get only the required data using view?

I have created a pivot excel file with filters in SQL views connected to the excel, its fine but the data in the pivot excel is too large to send in email (or) it has more unneccarsy data. I need only the filtered data from the pivot excel file, so I tried copy and past […]

SQL query inside a query

Allow me to share my query in an informal way (not following the proper syntax) as I’m a newbie – my apologies: select * from table where ( (category = Clothes) OR (category = games) ) AND ( (Payment Method = Cash) OR (Credit Card) ) This is one part from my query. The other […]

VBA ADODB recordset is closed

I am trying to query a database using Excel VBA and write to a sheet. The VBA code I have works fine for one server/database, but when I try to pull from a different database it messes up. When I run this on this database, I get an error stating “Operation is not allowed when […]

Create group ID based on description

I had a table (Excel format) as follows: **Company name** 2016 Microsoft company 2015 ANZ (north) Co.ltd 2015(south) Microsoft Co.Ltd 2016 ANZ Co.ltd COMPANY OF FRIESLAND CAMPINA (milk) Australia CAMPINA (Food) Co.Ltd 2015 CAMPINA (Food) Co.Ltd 2015COMPANY OF FRIESLAND CAMPINA (milk) Indo Co.FRIESLAND CAMPINA (milk) Thai land I would like to generate the ID based […]

Creating Hyperlink exporting to Excel from SQL Insert

I’ve tried what I thought might work but no joy… DECLARE @QRY NVARCHAR(MAX) SET @QRY= ‘INSERT INTO OPENROWSET(”Microsoft.ACE.OLEDB.12.0”, ”Excel 12.0;HDR=No;IMEX=0;Database=E:\Doc.xlsx;”, ”SELECT * FROM [Sheet1$]”) SELECT [DocNo.],[DocName], CONCAT(“=HYPERLINK(\”http://InternalSite/case_id=’+[OBJID]+'”,[CaseRef],”\”)”), [Activity],[Date & Time],[User] FROM List ORDER BY [Date & Time] ASC’ EXEC (@QRY) The hyperlink would be compiled & completed with [OBJID] from the same table & the […]

SQL Server 2014 Management Studio and excel 2013, some rows have misplaced columns

When I copy results with headers in SQL Server 2014 and paste into Excel 2013, save results as, or even just ctrl+c and ctrl+v into Excel, some rows will be split into two rows, when they should only be one. Database Entry colA colB colC ———————————— 1 hello ‘return’ay test 2 test1 test Excel paste […]

Load Excel 2007 file with columns more than IV into SQL Server table using SQL Server Import Export Wizard

I have an Excel .xlsx file that has data columns extending to the NZ column (total 390 columns). Each column has data specific to that column. I want to load these data into a SQL Server table. I tried to use the SQL Import Export wizard for loading this Excel file into the SQL Server […]

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