Tag: sql

Database needs to be regularly updated using excel spreadsheets, but formatting is NOT uniform

Thus far I’ve been manually importing/updating new records into our Access DB. Of course that’s time consuming and a hassle. The reason for this is that we send our customers a template, but they don’t always send us back the template with their information. Sometimes they change headers, include data in the wrong fields or […]

How to assign data to excel sheets in alphabetical order in PHPExcel

I am trying to create excel generated file. I want to get usernames from database and then print names according to each letter in alphabet. I am able to create sheets in alphabetical order but cant print names on each sheet. Here is what i have so far: <?php require_once (‘PHPExcel/Classes/PHPExcel.php’); include(‘inc/database_connection.php’); $conn = mysqli_connect(DATABASE_HOST, […]

formatting phone number into a 10 digit number (mysql)

Most phone numbers are formatted like this as your standard 10 digit north american phone number: 9759542606. However, some are formatted like this: (914) 552-7479 or 13105675417 (11 digits) How do I write a query that converts these numbers into a 10 digit number with has no spaces or non-numeric characters? And if it’s not […]

Excel VBA ODBC Type Mismatch in expression

I have 2 excel spreadsheets: GUI.xlsm Database.xlsm Database.xlsm is used as a database and GUI.xslm contains forms etc and queries Database.xlsm using ADO/SQL to get data. I am executing the following SQL from GUI.xlsm to query the tables in Database.xlsm: SELECT * FROM (([Repair_Items$] ri INNER JOIN [RefData_Job_Groups$] jg ON ri.RI_JG_ID = jg.JG_ID) INNER JOIN […]

online sql client

I have an excel file that i would like to perform sql queries on. Is there an online client where i can upload my file and perform queries on there? Cheers

Selecting Through only visilble Excel rows through vb/asp.net

is there a way to select visible columns in excel using vb.net/asp.net. I have this project that needs the certain rows of excel to be selected without selecting the hidden one. i have this query Select [BONUS],[PROGRAM], [COST/30s],[TARPs],[SPOTS – 30s ],[TOTAL COST – 30s ],[TOTAL TARPs – 30s ],[COST/15s],[SPOTS – 15s],[TOTAL COST – 15s ],[TOTAL […]

Data type changing after multiplying two columns in MS Access

I want to multiply two columns with each other and use the following code CDbl(amount) * CDbl(weighting) AS [amount_weighted], “Amount” is a regular number while “weighting” is a value between 0 and 1. The resulting column is formatted as “short text” in Access, although it should be a number. Occasionally the column “weighting” or “amount” […]

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 […]

Python or SQL: Populating an excel form (multiple times and saving outputs) from another table

Problem: Customer has requested we fill out a form (excel) for each item we provide them. Due to us providing them a large amount of parts, I would like to figure out a way to automate it as much as possible. Idea: Create a table (‘Data’) with each part number and relevant information in the […]

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