Avatar of nexsyis
nexsyisFlag for United States of America asked on

Use SQL query to make data easier to manage in Excel

I am using a program called WinAudit to scan client PCs.  It is able to dump the audit into a SQL database, but I have not been able to pull this data into excel and have it display how I want it to as a report.

The attached screenshot shows the structure of the data in SQL
 SQL Query
And this image shows how I would like the data to be pulled into Excel
 Excel view
I basically need the values of the column "ItemName" to end up each being their own column name under which it displays the values of "ItemValue1"

Is this even possible?
Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Norie

8/22/2022 - Mon
Patrick Matthews

nexsyis,Absolutely possible.  However, to speed things along, please prepare and upload a small Excel sample file, with data for 3 or 4 computers, and for which you substitute dummy values for any sensitive data.Patrick
Patrick Matthews

Question: for any given computer name, might you have >1 user name?
ASKER
nexsyis

Trying to make this spreadsheet for you now.  To answer your question, if you look at the SQL table there is a column called "auditID."  I would like it to display all unique AuditID's whether it was a duplicate scan from the same computer or not.  The login script will usually prevent more than one record from each PC being created, but it is not the end of the world if there are duplicates.  Again, lets just display all unique AuditIDs.  Thanks for your assistance.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
nexsyis

Ok here is the sample spreadsheet I would like to create based on teh SQL data, again each line in this spreadsheet should be from the unique Audit ID.
Audits.xls
Patrick Matthews

OK, but your sample output has no Audit ID column :)
Patrick Matthews

nexsyis,Sorry if I was not clear: what I need most is the sample *input* (sanitized, of course).Patrick
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
nexsyis

I don't actually need the Audit Id to be in the results, I just need that to be the unique identifier, but if it is easier to do in excel by all means add that column.  I dumped the database backup to a file, is that ok as far as sample input data?

Here is the link to the db backup:

http://rfgd.com/backup.zip

ASKER CERTIFIED SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
nexsyis

The raw data currently only exists in SQL not excel, so I'm not sure how I'm supposed to run that vba app?
Patrick Matthews

Dump the raw data into Excel, and then run my code from there.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Norie

The first thing you should do, if you can is to get the data into Excel using Data>Get external data...
You don't need to import all the data - just the columns of interest, one of which should probably be a unique identifier for each record.
Then attach a sample of that data - the zip file contains a .bak file but what is it exactly?
A backup of the data from the query?
If you can't get the data from SQL into Excel then try outputing the query to a file or text.
Patrick Matthews

>>Then attach a sample of that data - the zip file contains a .bak file but what is it exactly?It's a backup of the SQL Server database.  If you have a SQL Server instance, you could restore the backup there.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Norie

matthew
I tried the code but I got a type mismatch at arr(counter, "h").
I changed "h" to 8, and in the next line "i" to 9 - is that what's needed?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
nexsyis

Thanks for the help Imnorie.  Matthew, I'm not too excel saavy, can you give me the steps involved in running that code you gave me against the spreadsheet that Imnorie came up with?
Patrick Matthews

Sorry, change these lines in clsAuditIDs:            ItemName = arr(Counter, "h")            ItemValue = arr(Counter, "i")to:            ItemName = arr(Counter, 8)            ItemValue = arr(Counter, 9)Once I did that, the code ran without incident.
ASKER
nexsyis

Thanks guys, totally worked!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
nexsyis

I think there are quite a few people out there using Winaudit as an inventory tool.  You have both helped immensely.  I appreciate it.
Norie

Doesn't WinAudit 'do' other formats? eg CSV, HTML...
ASKER
nexsyis

Yeah, it lets you export individual audits to single CSV files, but the SQL method is a little cleaner
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
nexsyis

Matthew or Imnorie, can you look at this spreadsheet that I generated myself out of the SQL database and see why it is giving me an error when I run the macro against it?  It looks like the one that Imnorie created did not contain the "username" field but mine does.
Bosdasdsdok2.xlsx
ASKER
nexsyis

Nevermind, I found where the macro showed "User Name" not "User Account"  Works great now!
Norie

Strange - I restored the bak file and then exported it to Excel, that's how I created the workbook I attached.
Didn't change anything in the data.
Anyway, you've sorted it.:)
Your help has saved me hundreds of hours of internet surfing.
fblack61
Patrick Matthews

Glad to help, and special thanks to imnorie for providing that sample data set :)
Norie

nexsyis
How are you getting the data into SQL Server?
I can possibly see that it might seem 'nicer' but it might actually be harder to work with than with say a CSV file.
Plus a CSV file is far more accessible than a backup of an SQL table, and you could actually create a CSV file from within SQL.
(Well I can anyway - it might not be an option in some versions, or it might need a little more work)
matthewspatrick
Thank you, that's another thing I never knew about SQL server.
I thought you could only backup a whole database and that to restore it would involve all sorts of nonsense.:)