Separate Columns into Rows 1

[Moebius01]

Ok, first off I'm starting this in the Access forum as I'd just assume be able to do it locally rather than having to go through our DBA to create something on the SQL Server to work with. However, given the nature of what I'm trying to achieve (which I'm not even sure is remotely possible at this point), it may end up there instead. Here's the basic rundown:

I'm in the process of conducting a survey for our company using one of the online sites (SurveyGizmo in this case) for the process. It's more of a 360 Rating than survey, but in effect, the participant is asked to select from a list of 130 people (minimum 8, but someone could pick all 130) to rate. They will then be asked the same 4 questions on each person they selected.

This part is fine, the trouble comes in building reports out. SurveyGizmo's built in reporting is very respondent-centric, where I need to generate more target specific data. The best I can get from them is a CSV style export. The challenge that their system exports 1 row per respondent, so the table has every person/question as the fields (130 people * 4 questions = 520 fields, plus the respondent data). What I ultimately need is a row per person rated with the respondent ID, the ratee ID, and the ratings on the 4 questions (6 fields).

So, if I have a table that looks something like this:

RespID   EmpX-Q1   EmpX-Q2   EmpX-Q3   EmpX-Q4   EmpY-Q1   EmpY-Q2   EmpY-Q3   EmpY-Q4   etc...
1234     2         3           3         2         3         3         3         3

Could I use some manner of fun and exciting query to convert it something more like this:

RespID    RateeID    Q1    Q2    Q3    Q4
1234      X          2     3     3     2
1234      Y          3     3     3     3

I'm expecting somewhere around 1500+ responses, so I'm desperately hoping I can do this programatically rather than by hand.

Thanks in advance to anyone who has an idea how this could be achieved.

 

[PWise]

Select RespID, "x" as Emp EmpX-Q1 as q1, EmpX-Q2 as q2, EmpX-Q3 as q3, EmpX-Q4 as q4
from csv
union
Select RespID, "y" as Emp Empy-Q1 as q1, Empy-Q2 as q2, Empy-Q3 as q3, Empy-Q4 as q4

......

 

[dhookom]

Access can only handle 255 fields. If you have more, you will need code to open the csv file and loop through records and fields to append to an Access table. Roger Carlson has a small sample database CSVtoFixed that might help with reading the file.

I assume the number of values across will vary by line in the CSV file.

I would also suggest you might look at creating a table with a structure like:

RespID   RateeID  Q   Rating
1234     X        1   2
1234     X        2   3
1234     X        3   3

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

There are probably several different ways to do this, programatically. One issue with using access is that you can not import this directly due to the field limit of 255 fields.

So if it was me, I think I would just write vba code in Access to read the CSV and then write to a table in the desired format. Or I would import into Excel first and possibly write vba code in Excel to format as desired. Then you can import the new worksheet into Access as a table. Neither should be very hard. Or I gues you could import the CSV into Excel and then split it into two tables less than 255 columns. Then you could import these two tables. I still think you would have a hard time writing pure SQL query to do this, but looping recordsets would be again rather easy.

Any chance you could post some real data? Maybe 20 rows, and then I will see if I can process the data into Access. You can use 4shared or another file sharing site.

 

[Moebius01]

Here's a basic CSV dump with most of the columns (think there are about 340 ish in this one - thought it'd be helpful to go over the 255 to test with). Going to try to copy and paste, but if it's too big, I'll find a place to upload.

Apologies for the ugly, I just don't have an online file storage account right now.

Date Submitted,UserID,Q2-100010132,Q2-100010610,Q2-100010162,Q2-100010118,Q2-100011199,Q2-100010372,Q2-100011143,Q2-100010113,Q2-100010090,Q2-100010248,Q2-100010053,Q2-100011490,Q2-100010616,Q2-100010295,Q2-100010011,Q2-100010940,Q2-100010775,Q2-100011141,Q2-100011692,Q2-100010499,Q2-100010127,Q2-100012144,Q2-100010498,Q2-100011037,Q2-100011709,Q2-100011452,Q2-100010171,Q2-100010375,Q2-100010168,Q2-100010163,Q2-100010525,Q2-100012071,Q2-100010470,Q2-100012118,Q2-100010121,Q2-100010073,Q2-100011361,Q2-100010318,Q2-100011967,Q2-100010298,Q2-100010834,Q2-100010074,Q2-100010387,Q2-100010122,Q2-100010147,Q2-99999,Q2-100010091,Q2-100011088,Q2-100010463,Q2-100011033,Q2-100010192,Q2-100010825,Q2-100010304,Q2-100010292,Q2-100010737,Q2-100011177,Q2-100010302,Q2-100012183,Q2-100010138,Q2-100012137,Q2-100011801,Q2-100010552,Q2-100011071,Q2-100011459,Q2-100011378,Q2-100010085,Q2-100010089,Q2-100010023,Q2-100011198,Q2-100010154,Q2-100010915,Q2-100010994,Q2-100011146,Q2-100011042,Q2-100011175,Q2-100011952,Q2-100010846,Q2-100010790,Q2-100010437,Q2-100012116,Q2-100010054,Q2-100010309,Q2-100010866,Q2-100010514,Q2-100010458,Q2-100010798,Q3-100010132,Q3-100010610,Q3-100010162,Q3-100010118,Q3-100011199,Q3-100010372,Q3-100011143,Q3-100010113,Q3-100010090,Q3-100010248,Q3-100010053,Q3-100011490,Q3-100010616,Q3-100010295,Q3-100010011,Q3-100010940,Q3-100010775,Q3-100011141,Q3-100011692,Q3-100010499,Q3-100010127,Q3-100012144,Q3-100010498,Q3-100011037,Q3-100011709,Q3-100011452,Q3-100010171,Q3-100010375,Q3-100010168,Q3-100010163,Q3-100010525,Q3-100012071,Q3-100010470,Q3-100012118,Q3-100010121,Q3-100010073,Q3-100011361,Q3-100010318,Q3-100011967,Q3-100010298,Q3-100010834,Q3-100010074,Q3-100010387,Q3-100010122,Q3-100010147,Q3-99999,Q3-100010091,Q3-100011088,Q3-100010463,Q3-100011033,Q3-100010192,Q3-100010825,Q3-100010304,Q3-100010292,Q3-100010737,Q3-100011177,Q3-100010302,Q3-100012183,Q3-100010138,Q3-100012137,Q3-100011801,Q3-100010552,Q3-100011071,Q3-100011459,Q3-100011378,Q3-100010085,Q3-100010089,Q3-100010023,Q3-100011198,Q3-100010154,Q3-100010915,Q3-100010994,Q3-100011146,Q3-100011042,Q3-100011175,Q3-100011952,Q3-100010846,Q3-100010790,Q3-100010437,Q3-100012116,Q3-100010054,Q3-100010309,Q3-100010866,Q3-100010514,Q3-100010458,Q3-100010798,Q4-100010132,Q4-100010610,Q4-100010162,Q4-100010118,Q4-100011199,Q4-100010372,Q4-100011143,Q4-100010113,Q4-100010090,Q4-100010248,Q4-100010053,Q4-100011490,Q4-100010616,Q4-100010295,Q4-100010011,Q4-100010940,Q4-100010775,Q4-100011141,Q4-100011692,Q4-100010499,Q4-100010127,Q4-100012144,Q4-100010498,Q4-100011037,Q4-100011709,Q4-100011452,Q4-100010171,Q4-100010375,Q4-100010168,Q4-100010163,Q4-100010525,Q4-100012071,Q4-100010470,Q4-100012118,Q4-100010121,Q4-100010073,Q4-100011361,Q4-100010318,Q4-100011967,Q4-100010298,Q4-100010834,Q4-100010074,Q4-100010387,Q4-100010122,Q4-100010147,Q4-100010091,Q4-100011088,Q4-100010463,Q4-100011033,Q4-100010192,Q4-100010825,Q4-100010304,Q4-100010292,Q4-100010737,Q4-100011177,Q4-100010302,Q4-100012183,Q4-100010138,Q4-100012137,Q4-100011801,Q4-100010552,Q4-100011071,Q4-100011459,Q4-100011378,Q4-100010085,Q4-100010089,Q4-100010023,Q4-100011198,Q4-100010154,Q4-100010915,Q4-100010994,Q4-100011146,Q4-100011042,Q4-100011175,Q4-100011952,Q4-100010846,Q4-100010790,Q4-100010437,Q4-100012116,Q4-100010054,Q4-100010309,Q4-100010866,Q4-100010514,Q4-100010458,Q4-100010798,Q1-100010132,Q1-100010610,Q1-100010162,Q1-100010118,Q1-100011199,Q1-100010372,Q1-100011143,Q1-100010113,Q1-100010090,Q1-100010248,Q1-100010053,Q1-100011490,Q1-100010616,Q1-100010295,Q1-100010011,Q1-100010940,Q1-100010775,Q1-100011141,Q1-100011692,Q1-100010499,Q1-100010127,Q1-100012144,Q1-100010498,Q1-100011037,Q1-100011709,Q1-100011452,Q1-100010171,Q1-100010375,Q1-100010168,Q1-100010163,Q1-100010525,Q1-100012071,Q1-100010470,Q1-100012118,Q1-100010121,Q1-100010073,Q1-100011361,Q1-100010318,Q1-100011967,Q1-100010298,Q1-100010834,Q1-100010074,Q1-100010387,Q1-100010122,Q1-100010147,Q1-99999,Q1-100010091,Q1-100011088,Q1-100010463,Q1-100011033,Q1-100010192,Q1-100010825,Q1-100010304,Q1-100010292,Q1-100010737,Q1-100011177,Q1-100010302,Q1-100012183,Q1-100010138,Q1-100012137,Q1-100011801,Q1-100010552,Q1-100011071,Q1-100011459,Q1-100011378,Q1-100010085,Q1-100010089,Q1-100010023,Q1-100011198,Q1-100010154,Q1-100010915,Q1-100010994,Q1-100011146,Q1-100011042,Q1-100011175,Q1-100011952,Q1-100010846,Q1-100010790,Q1-100010437,Q1-100012116,Q1-100010054,Q1-100010309,Q1-100010866,Q1-100010514,Q1-100010458,Q1-100010798
7/6/2010 10:04,100012065,,,,3,,,,,,,,,0,,,,,,,4,,,,,,,2,,,,,,,,,3,,3,,3,,,,,1,,,,,,,,,,,,,4,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,2,,,,,,,,,2,,,,,,,4,,,,,,,3,,,,,,,,,2,,2,,2,,,,,2,,,,,,,,,,,,,3,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,1,,,,,,,4,,,,,,,3,,,,,,,,,3,,3,,2,,,,,1,,,,,,,,,,,,1,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,4,,,,,,,4,,,,,,,4,,,,,,,,,2,,3,,4,,,,,4,,,,,,,,,,,,,4,,,,,2,,,,,,,,,,,,,,,,,,,,,,,
7/8/2010 14:23,100011360,,,,,,,,,,,,,,,,,4,,,0,,,,,,,,,,,,2,,,3,,,,,,,2,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,2,,,,,,,,,,,,,,,,,,,,,,4,,,1,,,,,,,,,,,,2,,,1,,,,,,,0,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,1,,,,,,,,,,,,,,,,,,,,,,3,,,0,,,,,,,,,,,,0,,,2,,,,,,,0,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,2,,,,,,,,,,,,,,,,,,,,,,3,,,1,,,,,,,,,,,,2,,,1,,,,,,,2,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,0,,1,,,,,
7/6/2010 11:18,100011081,,,,,,,,,,,,,,,,,,,,,,,1,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1,,,,,,,,,0,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1,,,,,,,,,0,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1,,,,,,,,,0,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1,,,,,,,,,0,,,,4,,,,,,,,,,
7/6/2010 11:57,123456789,4,,,3,4,,4,,,4,,,,,,,,,,,4,,,,4,,,,4,,,,,,,4,,,,,,,,,,,4,,,4,4,4,,,,4,,,4,,4,,,,,,4,4,,4,,,,,,,,,4,,,4,,,,,3,,,3,4,,4,,,4,,,,,,,,,,,4,,,,4,,,,4,,,,,,,4,,,,,,,,,,,4,,,4,4,4,,,,4,,,4,,4,,,,,,4,4,,4,,,,,,,,,4,,,4,,,,,4,,,3,4,,4,,,4,,,,,,,,,,,4,,,,4,,,,4,,,,,,,4,,,,,,,,,,4,,,4,4,4,,,,4,,,4,,4,,,,,,4,4,,4,,,,,,,,,4,,,4,,,,,3,,,3,4,,4,,,4,,,,,,,,,,,4,,,,4,,,,4,,,,,,,4,,,,,,,,,,,4,,,4,4,4,,,,4,,,4,,4,,,,,,4,4,,4,,,,,,,,,4,,,4,,,,
7/6/2010 12:54,100011023,,,,,,,,,4,,4,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,4,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,4,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,4,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
7/6/2010 13:01,100010861,,,,,,,,,,,,,,,,,,,,,,,0,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2,1,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,0,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2,2,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,0,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2,1,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,0,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2,1,,,,,,,,,
7/6/2010 17:28,987654321,,,,,,,,,,,,,,,,0,,,,,,,,,4,,,,,4,,,,,2,,,,,,,,,,,,,0,,,,,,,,4,,,,,,,,,,,,,,,,,,,0,,4,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,,,3,,,,,4,,,,,0,,,,,,,,,,,,,3,,,,,,,,4,,,,,,,,,,,,,,,,,,,0,,2,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,,,3,,,,,4,,,,,2,,,,,,,,,,,,2,,,,,,,,4,,,,,,,,,,,,,,,,,,,1,,3,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,,,4,,,,,4,,,,,1,,,,,,,,,,,,,2,,,,,,,,4,,,,,,,,,,,,,,,,,,,2,,3,,,,,,,,,
7/6/2010 17:49,100011081,,,,,,,,,,,3,,,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,
7/6/2010 17:50,100012071,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,4,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,4,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,4,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,4,,4,,,,
7/6/2010 17:46,100010616,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,1,,,,,,,,,,,,,,,,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,2,,,,,,,,,,,,,,,,,,,,,,,0,,,,,,,,,,,,,,,,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,3,,,,,
7/6/2010 18:23,100010871,,,,,,,,,,,,,,,,,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,2,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2,,,,3,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,3,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,2,4,,,,,,,
7/7/2010 6:05,100010866,,,,,,3,,,,,,,3,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,3,,,,,2,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,3,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,3,,,,,1,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,
7/7/2010 9:26,100010871,,3,,,,,,,,,,,,,3,,,,,3,,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,,,,,,,3,,,,,3,,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,,,,,,,3,,,,,3,,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,,,,,,,3,,,,,3,,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
7/7/2010 9:25,100010871,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,,3,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,,3,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,2,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,4,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,,,,
7/7/2010 14:47,100012065,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,
7/8/2010 19:50,100010463,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,4,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,0,,,,,,,,,,,,,,,,,4,,,,,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,4,,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,,,,,,,,,,,4,,,,,4,,,,,,,,,,,,,,,,,,,,,,,,,,
7/9/2010 8:55,100010304,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

 

[MajP]

The following will read the data and put into a table that looks something like this

respID	        rateeID	    uestionID	rating
100010463	100010318	Q1	3
100010463	100010318	Q2	4
100010463	100010318	Q3	0
100010463	100010318	Q4	4
100010463	100010616	Q1	4
100010463	100010616	Q2	4
100010463	100010616	Q3	4
100010463	100010616	Q4	4
100010463	100010737	Q1	4
100010463	100010737	Q2	4
100010463	100010737	Q3	4
100010463	100010737	Q4	4

Here is the basic. There are some additional code that allows you to dynamically add to an array

Public Sub readCSV(CSVName As String)
 Const numQuestions = 4
 Dim nFileNum As Integer
 Dim sText As String
 Dim LineCount As Long
 Dim sNextLine As String
 Dim i As Integer
 Dim dateSubmitted As Variant
 Dim aCol() As String
 Dim aEmpQ() As String
 Dim aQuestions() As Variant
 Dim aEmps() As Variant
 Dim empID As Variant
 Dim questRating As Variant
 Dim respID As Variant
 Dim fieldName As String
 Dim questionID As String

'clear out table
CurrentDb.Execute "delQryRatings"
' Get a free file number
nFileNum = FreeFile

' Open a text file for input. inputbox returns the path to read the file
Open CSVName For Input As nFileNum
LineCount = 1
' Read the contents of the file
Do While Not EOF(nFileNum)
   Line Input #nFileNum, sNextLine
   aCol = Split(sNextLine, ",")
   'First line has information on Employees and Questions
   'The questions could be hard coded if they are always 4 per employee
   'But this allows a variable amount
   
   If LineCount = 1 Then
      For i = LBound(aCol) To UBound(aCol)
         'this splits the EMPX-Q1 into EmpX and Q1
         If i > 1 Then
           aEmpQ = Split(aCol(i), "-")
           empID = aEmpQ(1)
           'Now save just the emp IDs into a new array
           aEmps = AddElement(aEmps, empID)
           questionID = aEmpQ(0)
           'Now save just the Question IDs into a new array
           'Coul just be hard coded 1 to 4
           aQuestions = AddElement(aQuestions, questionID)
           'Debug.Print "Employee ID = " & empID
           'Debug.Print "Question Name = " & questionID
         End If
      Next i
      'PrintArray aEmps
      'PrintArray aQuestions
   Else
       'Subsequent lines have respondent id and rating value information
       'The respondent ID is
       dateSubmitted = "#" & aCol(0) & "#"
       respID = aCol(1)
       For i = 2 To UBound(aCol)
           questRating = aCol(i)
           If questRating = "" Then questRating = "Null"
           empID = aEmps(i - 2)
           questionID = aQuestions(i - 2)
           If Not questRating = "Null" Then
             Call insertToTable(dateSubmitted, respID, empID, questionID, questRating)
           End If
       Next i
   End If
   
   LineCount = LineCount + 1
Loop
' Close the file
Close nFileNum

End Sub

Public Sub insertToTable(dateSubmitted As Variant, respID As Variant, rateeID As Variant, questionID As Variant, rating As Variant)
  Dim strSql As String
  strSql = "Insert into tblRatings (dateSubmitted,respID,rateeID,questionID,rating) values ("
  strSql = strSql & dateSubmitted & ", " & respID & ", '" & rateeID & "', '" & questionID & "', " & rating & ")"
  Debug.Print strSql
  CurrentDb.Execute strSql
End Sub

This demo will do the conversion

http://www.4shared.com/document/ZbjyTosM/FixCSV.html

 

[Moebius01]

Great, thanks muchly!

It's painful that the longer I'm in a "non-IT" role (I'm now more of an HRIS Admin), the more I can feel any hint of coding skills draining away. A couple years ago, I might have come up with something close to what you have there. Today, I'm having to stare for half an hour just to break down the function.

 

[MajP]

Here is a simpler way, but takes a couple of steps.

1)import your table into Excel
2)Copy the table to a new worksheet
3)On sheet 1 delete everything after the 254th column
4)On sheet 2 keep the Time and UserID fields and only the other fields not in sheet 1
5)Import the two tables into Access

Lets just call them table1 and table2. The only reason to do this is the 255 field limitation in Access

Now run this code on both tables

Public Sub insertFromTable(tblName As String)
  
  On Error GoTo errlbl
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim i As Integer
  Dim dateSubmitted As Variant
  Dim UserID As Variant
  Dim questRating As Variant
  Dim rateeID As Variant
  Dim fieldName As String
  Dim questionID As String
  
  'CurrentDb.Execute "delQryRatings"
  Set rs = CurrentDb.OpenRecordset(tblName)
  Do While Not rs.EOF
    dateSubmitted = "#" & rs![Date Submitted] & "#"
    UserID = rs!UserID
    For i = 2 To rs.Fields.Count - 1
      questRating = rs.Fields(i).Value
      If Not Trim(questRating & " ") = "" Then
        questionID = Left(rs.Fields(i).Name, 2)
        rateeID = Mid(rs.Fields(i).Name, 4)
        Call insertToTable(dateSubmitted, UserID, rateeID, questionID, questRating)
       End If
    Next i
    rs.MoveNext
  Loop
  Exit Sub

errlbl:
  If Err.Number = 3021 Then
    Resume Next
  Else
    MsgBox Err.Number & " " & Err.Description
  End If
End Sub

Takes a little prep, but the code is extremely short.