All Questions
Tagged with sql microsoft-excel
73 questions
0
votes
2
answers
321
views
How to properly and efficiently query Lots of data from SQL Server putting it into specific cells?
Recently I was asked to make a real-time project in Excel which evolves huge amount of data.
I have this data stored in SQL Tables and managed to successfully bring it into Excel.
SQL Table on Excel:
...
- 13
0
votes
2
answers
492
views
Create a double entry accounting table by separating transactions
I have an Excel table with accounting transactions that are stored on a single line:
Date
To Account
From Account
Amount
Type
2023-06-01
2192
2281
$100.00
Transfer
I'm attempting to convert that ...
- 10.5k
0
votes
1
answer
36
views
Is there a way to run a SQL on excel to create another table on the same spreadsheet?
I have a spreadsheet on Microsoft Excel with a formmated table similar to the attached picture. It is a long table (1000 lines or more) with ~30 columns and I would like to know if there is an easy ...
- 227
0
votes
0
answers
475
views
Text to Column has now changed my Excel's pasting preferences permanently
I recently used the text to columns functionality in excel. It was used to split out one singular column that was delimited by commas. After I successfully made the adjustment, my excel now will not ...
0
votes
1
answer
83
views
Excel 365 - Dynamically importing data into excel from a Database
I have a spreadsheet with 40k rows or so which is added onto every week. Each row has an Employee ID, Employee’s Shift Number and Hours worked column (see attached image for sample data). Sample data
...
- 11
0
votes
1
answer
130
views
Why is the excel date cell-format, date and time is so low
Does anyone have a workaround for formatting datetimes copied from sql into excel?
When I copy a date field into excel from sql, it looks like the following 43299.7088541667, I then want to format it ...
- 1
0
votes
3
answers
117
views
How to pull up data written between seperators?
I am a digital marketer with zero experience at coding.
I'd like to pull up only the first data which is written between separators (|||)
[VP/NNP] ||| talk here ||| walk here ||| PPDB2.0Score=6.03040 ...
- 1
0
votes
0
answers
28
views
Getting the result of this SQL query in Excel
I have a table containing the columns for employee name, date, and how late they are that date
I want to fill a cell with the content of the column 'Late By' using the criteria 'Employee Name' and '...
0
votes
0
answers
1k
views
Executing SQL query through VBA
I am new here, but I always check this website whenever I have issues with excel and so far you've always helped me!
Now I have a problem with VBA: I want to "call" an SQL query through a macro, so ...
0
votes
0
answers
31
views
Why is SQL Management Studio Stored Procedure breaking my rows into new lines?
I have a stored procedure that is set up to run once a week. However, it seems to be breaking up my lines if the length of the row is over 224 characters, right in the middle of words. The procedure ...
0
votes
0
answers
32
views
SQL Query Connected Excel Report Hanging on Saving
I have a MS Excel 17 sheet with multiple SQLDB queries connected for reporting. This is called by a python script that opens, puts in the PW, refreshes the queries, saves the report, and closes excel. ...
- 1
1
vote
1
answer
453
views
Syntax for SQL IN when using Excel database connection
I'm using a number of Excel files to connect to an SQL Server database, fetching sales data for a particular product number which is specified in a cell in the Excel sheet.
This was set up by ...
- 23
1
vote
1
answer
2k
views
EXCEL equivalent for SQL select ... order by
Let's suppose I have an excel table:
id title value
1 foo 300
2 bar 100
3 baz 100
4 bam 400
I want to create a separate excel sheet with id and title of 2 items with smallest values in ...
- 11
0
votes
1
answer
349
views
Excel Import SQL - Value not suitable to be converted to a datetime data type
I'm testing an import from mySql where I have an Account_Number that's '1000001' saved as a Varchar(255) (I guess in the event they add Numerical?)... either way.
When I import to Excel I get the ...
2
votes
0
answers
253
views
Error when trying to get data from SQL to Excel
I'm have an issue with Excel reading UniqueIdentifier columns from a SQL Database.
In excel, I go to DATA > GET DATA > FROM DATABASE > FROM SQL DATABASE. Login and connect to the sql database just ...
- 21
1
vote
0
answers
469
views
How do I stop the following error from occuring? "A connection attempt failed" (see full error in body)
I am getting the following error on an excel workbook which has a data connection to a sql database.
A connection attempt failed because the connected party did not properly respond after a period ...
2
votes
0
answers
2k
views
Insert data to database using Excel VBA is not working
I have a macro that will upload data from multiple workbooks to the database. Table will differs based on what type of value/data being inserted. I have there tables. I can insert value in the first ...
- 23
0
votes
0
answers
913
views
Excel, ODBC - desired database not listed
When I attempt to create an ODBC to a server (named DARKHELMET for all you spaceballs fans out there) it doesn't show in the list of available data sources.
..but it does show up under data source ...
- 1
2
votes
2
answers
3k
views
DQL/SQL to Excel - obtain results of multiple SELECT statements in spreadsheet
I wrote T-SQL query that contains few separate SELECT statements.
Each SELECT statement returns different number of columns and represents different properties/features, so that I don't want to join ...
- 165
1
vote
1
answer
270
views
Need to join two excel tables using Id and a time difference
I need to join two databases (Excel tables).
Each one has identification card numbers (unique) and time of admission.
The main key is the id of the patient. I only want to get the ones with the same ...
0
votes
1
answer
1k
views
MS Access unable to set primary key on make table query
I am using MS Access 2016, I've got a table in my database that is linked to an external source through an ODBC connection. Data is come from MS excel. I created Query and linked up to macro. It runs ...
- 114
3
votes
1
answer
1k
views
Excel - Using SQL command on Power Query data
Using Power Query, I pull in two reports from Salesforce using Power Query > Get external Data > From Online Services.
I load them into tables in two different sheets, and then create a third ...
1
vote
0
answers
33
views
Rename database dialog box within Excel-2016
See image here>>> ODBC Rename
My goal is to change "User Selection for ODBC" to "Sage Login Information" or whatever. I have a few worksheets that I've setup and they all have odd names when the ...
- 13
0
votes
1
answer
209
views
Incorrect results in math operations (MS Query Excel)
I have this table (vol_week) with four columns. Example:
date_ent | ped_kg | prd_kg | ref_kg
12/9/2017 | 345,22 | 321,23 | 12,56
I'm trying to calculate (ped_kg - prd_kg + ref_kg), but the query ...
- 15
1
vote
0
answers
102
views
Excel - Why did select * SQL statement omit a field?
I have a question regarding using SQL in Excel. I have an Excel application that obtains data from a Worksheet and import the data to another Worksheet.
Entry form image here. Note the Entry Date ...
- 129
0
votes
0
answers
822
views
How to stop Excel from changing my time format?
I am currently working on an excel program that obtains data from a data entry worksheet to a display worksheet using vba and macros. The majority of the data can transfer properly, except for the ...
- 129
-3
votes
1
answer
50
views
Where can I find web databases for my big data project?
I started a new course at Microsoft to learn more about working with Azure, Visual Studio, SQL and Excel and I want to have a few sites I can use to pull the data from.
It helps if the site keeps the ...
0
votes
0
answers
92
views
Comparing Multiple Sets of Data
I was wondering what is the best way to compare multiple sets of data. I have around 25 tables of data which are Food Product names. I need to see which food products are the same across all these ...
- 9
0
votes
0
answers
288
views
How to Optimize SQL Query to fetch more than 100K rows?
I am writing simple query to Export Test cases with design steps from HP QC. I am getting error for records having more than 70K rows.
1
vote
1
answer
2k
views
Excel - power query (or anything else) - join on partial match between fields?
In power query (or MS query, or really anything else that would create a refresh-able table that gives me the desired results in excel), how can I join on partial matches between two fields?
I want ...
- 525
1
vote
1
answer
1k
views
Using SQL to merge .xlsx file and over 1,000,000 rows?
I'm having trouble with an operation that I believe is limited by the shear size of the data - the operation is a merge and filtration of two Excel workbooks, each with over 1 million rows.
Perhaps ...
- 111
1
vote
1
answer
1k
views
Running Parameterized Stored Proc From Power Query
I have been following the tutorial HERE but anytime I try to refresh the workbook, it gives me an error of:
DataSource.Error: Microsoft SQL: This database function doesn't support the query option '...
- 2,125
2
votes
1
answer
2k
views
Outer join with union in Excel 2010 using Power Query
I've got two tables (two worksheets of the same workbook in Excel 2010):
The result should look like this:
My SQL code works well:
SELECT table_2.ID, table_1.value_1, table_2.value_2 FROM table_1 ...
- 131
0
votes
1
answer
499
views
(SSIS) Data Flow Task – export SQL to excel file
I'm looking for a solution / tutorial following case. In the MS SQL database I have two tables (invoice, invoice item). I need to export data to excel.
My problem is the structure of the export ...
- 11
0
votes
1
answer
825
views
How do you make a read-write data connection between two Excel worksheets? (I am currently using an od.odc file and a connection that uses SQL)
I have the following project started:
Inside of Master.xlsx I have (the worksheet is named MasterList):
I then go into CA_Accts.xlsx and make a data connection, with the following steps:
Next, I ...
- 11
0
votes
0
answers
100
views
Different Sharepoint filepaths in different environments
We have a VBA application in Excel (UserForm) that users use to update another excel workbook via SQL. The two files are side by side in an MS Sharepoint folder. There are two kinds of users – first ...
- 135
1
vote
1
answer
2k
views
Dynamic data sheet combined with one static
I am using a sheet in excel which pulls data out automatically SQL from a database. Fills in maybe 20 or so columns with orderinfo.
In column A i have a unique order no.
What i want to achieve is ...
0
votes
2
answers
2k
views
Import image from MS SQL Database into Excel
I have a MS SQL Database where images are stored as byte[].
Is it possible to import these byte[] into Excel and convert them back into images?
I already have a connection to the database in my Excel ...
- 111
0
votes
1
answer
3k
views
How can I translate SQL to Excel formulas?
I have an excel sheet with raw data and I want make a custom view on it in another table for reporting purposes.
How can I translate the following pseudo code to Excel?
select ColA, ColB, ColC from ...
- 213
4
votes
1
answer
2k
views
Excel VBA : SQL query handles error
I have SQL that I can use in Excel 2013 to get the info from the Access database and it works using the SQL directly in a connection, but now I want to run it from VBA instead so I can use some ...
- 41
0
votes
1
answer
546
views
Import external data connections into Excel 2013 for use in Excel 2003
I have a workbook in Excel 2013 that retrieves data from a SQL database via a query. However, the connection imports data as a table, which is incompatible with Excel 2003. Is there a way to convert ...
- 103
2
votes
1
answer
89k
views
Execute SQL Query with VBA and fill sheet with data
Very new to any form of code. Attempting to run a script in VBA that will pull data from an access database using SQL and put it into an excel spreadsheet. Code is similar to below. Runs clean but I ...
- 23
0
votes
1
answer
1k
views
Microsoft Query in Excel Returning Blank
In this post I was given a solution that relies on the following SQL:
SELECT `S1`.DATES , `S1`.NAMES , MAX(`LS`.DATES)
FROM `C:LOCATION`.`Sheet1$` `S1`
LEFT OUTER JOIN `C:\LOCATION`.`LargerSheet$` `...
- 339
4
votes
3
answers
12k
views
How to export data from Excel to SQL Azure?
I know how to import data from SQL Azure into Excel
From Excel go to:
- Data
- From Other Sources
- From SQL Server
I want to manually edit data and post it back to SQL Azure. What is the ...
- 383
0
votes
2
answers
360
views
How to perform sql query on sheets of excel
I have two tables in excel(sheets) . One has 10,000 entries , another has 1000 entries . Both of them have account number column in common . I want to select only those row in 10,000 entries which ...
- 101
2
votes
1
answer
4k
views
How to import Dates from Excel into Access with text in some cells?
I am attempting to import a date column from Excel into Access. The problem is that over the years, users of the Excel spreadsheet sporadically inserted text notes into this date column a few ...
- 21
0
votes
3
answers
6k
views
Numbers are not displaying properly in excel report
n excel report generation, numbers are displaying 7.70225E+11 but it should display like 770225145579 . Any one please help to solve this issue. My code is,
<?php
include("connection.php");
if(...
user3521999
0
votes
1
answer
3k
views
Is there a function in Excel or Access to reshape a table to First Normal Form, or a way to do it in SQL?
Very often tables are presented in Excel files in a non-normalized form. I have one such table which I wish to take into (somewhat) First Normal Form using either SQL commands in Access or, ...
- 2,129
0
votes
1
answer
753
views
Transpose data at particular row w python,r,sql?
Lets say I've got following data (about 50k of rows in real example)
A
B
C
D
E
X
A
B
C
D
E
F
G
H
X
And I want it to look
A,B,C,D,E,X
A,B,C,D,E,F,G,H,X
So technically I want to transpose data, but ...
- 3
0
votes
0
answers
79
views
Stanford Wrangler ideal, but imposes limits, what are other options?
Stanford Wrangle tool is quite amazing, and I solved a giant problem with it, but it limits me to very small size. My file is about 500MB, and it allows me to process less than 1MB, the remaining data ...
- 3