PDA

View Full Version : Solved: (How to) Transpose one long list of numbers in CSV file to a number of rows



sfmatt13
10-25-2012, 07:53 PM
Hi Everyone,

I originally started with a Microsoft Access Table of data. It is in the shape of one column with 3,500,000 rows of data. I then created a query and exported it as a csv. I'd like to program a macro that pulls data from this csv file. I'd like it to take entries #1-256 and transpose them into row 1 of the spreadsheet, entries #701-756 and transpose them into row 2 of the spreadsheet, entries #1401-1656 and transpose them into row 3 of the spreadsheet, and so on for 5,000 rows.

Since I am very novice at writing macros, run time isn't too big of a deal to me as the simpler/easier to understand macro, the better.

I really appreciate your help in advance! =)

p45cal
10-27-2012, 11:06 AM
Could you attach a copy of you csv file (clipped to say 6k entries to save space)?
Additionally, I see no obvious sequence:
#1-256 is 256 items
#701-756 is only 56 items
#1401-1656 is 256 items again,
so what would be the next items?

sfmatt13
10-30-2012, 04:56 PM
I apologize, I made a mistake. The second group was meant to be 701-956 with 256 items. I was able to use the following code to accomplish my goal

Sub getDataFromCSV()
Dim fso As Object, csvFile As Object, ary(5000, 256)
Dim i As Long, rCount As Long, rw As Long, cl As Long
rCount = 700: i = 0: rw = 0: cl = 0
Application.ScreenUpdating = False
Set fso = CreateObject("Scripting.FileSystemObject")
Set csvFile = fso.OpenTextFile("your_file_here.csv", 1)
Do Until csvFile.atendofstream
If i = 0 Then
cl = 0: rw = rw + 1
End If
ary(rw - 1, cl) = csvFile.readline
If csvFile.atendofstream Then
Cells(1).Resize(5000, 256) = ary
Erase ary
End If
If cl < 256 Then
cl = cl + 1
End If
i = (i + 1) Mod rCount
Loop
csvFile.Close
Application.ScreenUpdating = True
MsgBox "Data has been extracted", vbInformation
End Sub

p45cal
10-30-2012, 05:09 PM
Aaaagghh.. cross-post:
http://www.excelforum.com/excel-programming-vba-macros/870763-how-to-transpose-one-long-list-of-numbers-in-csv-file-to-a-number-of-rows.html

Why aaaaagghh? See http://www.vbaexpress.com/forum/showthread.php?t=18537

sfmatt13
11-01-2012, 05:25 PM
I apologize for any inconvenience I caused as I didn't realize that posting the same question in multiple forums was not allowed.

I will make sure remember that in the future.