August 5, 2019 at 5:16 pm
Hi Folks,
My first question here. This may be lengthy, sorry in advance:-)
I am looking to read an excel file into a SQL Server table. The issue I am having is the data entry in the excel file was done in such a way that a person can have multiple pieces of software listed but the list was created with values added to subsequent cells.
I will try to add an image example here if I can. Say there are 2 columns, AssignedTo (A1), UserSoftware (B1), the person who entered the data must have written the first user's name 'Jane Doe' in A2 then inserted multiple rows, say 5 in all, under Jane Doe to add 4 pieces of software in UserSoftware column B2, B3, B4, and so on... S/he skipped a row and typed Max Doe, and again inserted multiple rows to add software for Max.
Now, if Jane Doe was listed in all subsequent cells A3, A4, A5... I could have queried for Jane Doe and grab all values in UserSoftware column for her but I can't figure out a good way to do this, results in StackOverFlow and others, including here turned up nothing (please point me to a page that has this, if you do). I hope this all makes sense and you can guide me to write it so the process knows to keep searching for a value for Jane Doe until Max Doe comes up.
I tried importing the Sheet into SQL Server, but it seems messy. And I don't want to make more work for the team who manages the workbook containing that sheet.
Any input you have on this will be appreciated.
Thanks,
KoellPrit
August 5, 2019 at 6:29 pm
Add a formula in C2, =IF(A2="",A2,A1), copy down the whole list, then import using that new column as your user.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
August 5, 2019 at 6:48 pm
Solving this in T-SQL requires a column to order by. You don't have such a column in your data.
Drew
PS: "Assigned" is misspelled.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 6, 2019 at 2:56 pm
Thanks for your response Drew, the current image is not the actual sheet in question, added quickly as an example … Couldn't we order by AssignedTo? thus SELECT AssignedTo, UserSoftware FROM OurUserTable ORDER BY AssignedTo;
August 6, 2019 at 3:01 pm
Thanks much Jonathan, I will consider that as an another option for sure...
August 6, 2019 at 3:50 pm
Thanks for your response Drew, the current image is not the actual sheet in question, added quickly as an example … Couldn't we order by AssignedTo? thus SELECT AssignedTo, UserSoftware FROM OurUserTable ORDER BY AssignedTo;
Ordering by AssignedTo won't give you the results you want, because it destroys the visual ordering that you are currently using to determine who is assigned which software license(s). You need a field that preserves that visual ordering.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 6, 2019 at 10:17 pm
I got it to work, but I flat out cheated. I used Access (which most everyone here hates), but it was the easiest solution for me.
I created a table in Access with the following columns
RecordID Autonumber (like INT IDENTITY)
Username TEXT(50) allow nulls
SoftwareTitle TEXT(50) allow nulls
I imported the Excel file into the table above, which gave me the unique ID that Drew was talking about. Then I used this code to "fill down" the missing usernames.
Public Sub fillUsernames()
Dim rsSW As DAO.Recordset
Set rsSW = CurrentDb.OpenRecordset("FixedUserSoftware", dbOpenTable)
Dim strUserName As String
rsSW.MoveFirst
Do Until rsSW.EOF
If Not IsNull(rsSW.Fields("Username")) Then
strUserName = rsSW.Fields("Username")
Else
rsSW.Edit
rsSW.Fields("Username") = strUserName
rsSW.Update
End If
rsSW.MoveNext
Loop
rsSW.Close
Set rsSW = Nothing
End Sub
Then I can just create a query like this:
SELECT FixedUserSoftware.Username, FixedUserSoftware.SoftwareTitle
FROM FixedUserSoftware
WHERE (((FixedUserSoftware.SoftwareTitle) Is Not Null));
and export that back to wherever I want.
Hideous, but it works.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply