Link to home
Start Free TrialLog in
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
 User generated image
And this image shows how I would like the data to be pulled into Excel
 User generated image
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?
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

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
Question: for any given computer name, might you have >1 user name?
Avatar of nexsyis

ASKER

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.
Avatar of nexsyis

ASKER

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
OK, but your sample output has no Audit ID column :)
nexsyis,Sorry if I was not clear: what I need most is the sample *input* (sanitized, of course).Patrick
Avatar of nexsyis

ASKER

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
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of nexsyis

ASKER

The raw data currently only exists in SQL not excel, so I'm not sure how I'm supposed to run that vba app?
Dump the raw data into Excel, and then run my code from there.
Avatar of Norie
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.
>>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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
Avatar of nexsyis

ASKER

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?
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.
Avatar of nexsyis

ASKER

Thanks guys, totally worked!
Avatar of nexsyis

ASKER

I think there are quite a few people out there using Winaudit as an inventory tool.  You have both helped immensely.  I appreciate it.
Doesn't WinAudit 'do' other formats? eg CSV, HTML...
Avatar of nexsyis

ASKER

Yeah, it lets you export individual audits to single CSV files, but the SQL method is a little cleaner
Avatar of nexsyis

ASKER

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
Avatar of nexsyis

ASKER

Nevermind, I found where the macro showed "User Name" not "User Account"  Works great now!
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.:)
Glad to help, and special thanks to imnorie for providing that sample data set :)
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.:)