ExcelBanter - Read 500 columns of CSV data into a spreadsheet

Bill
The following macro should do the job as requested. I'm sure it can be
tidied up but might give you something to work with. Apart from this
the easy way on a occasional basis is to open the CSV file directly in
Excel Select and Copy the data Insert a new Worksheet Select cell
A1 on the new worksheet Paste Special selecting Values & Transpose
from the paste special options.

Option Explicit
Sub GetData() ' to get data from csv file
Dim Fnam As String
Dim ColNdx As Integer
Dim RowNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim Sep As String

Fnam = "c:\testdata.csv" ' data file name as required
Application.ScreenUpdating = False ' prevent screen flicker
On Error GoTo EndMacro:
Sep = "," ' input data csv file field seperator
Open Fnam For Input Access Read As #1 ' open csv file
ColNdx = 0 ' column number

While Not EOF(1)
Line Input #1, WholeLine
' add comma to last field of data line if not present
If Right(WholeLine, 1) < Sep Then WholeLine = WholeLine & Sep
Pos = 1 ' reset field number
RowNdx = 1 ' reset row index
ColNdx = ColNdx + 1 ' advance column number
NextPos = InStr(Pos, WholeLine, Sep) 'locate first field seperator
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos) ' extract the
field
Cells(RowNdx, ColNdx).Value = TempVal 'put field value in
worksheet
RowNdx = RowNdx + 1 ' advance row number
Pos = NextPos + 1 ' advance field position
NextPos = InStr(Pos, WholeLine, Sep) ' is there another
field
Wend ' if yes then loop and extract it
Wend
Sep = Chr(34) ' remove the quotes if required
Range("a1").CurrentRegion.Replace What:=Sep, Replacement:=""

EndMacro:
On Error GoTo 0
Close #1
Application.ScreenUpdating = True
End Sub

HTH
Chris Bruce

Bill Anderson wrote:
I have a csv file
apx 500 fields in double quotes, separated by commas. Each "value" mwill
vary in length
There may be many rows of data like this in the file, each with the same
number of values
Blank values are represented by 2 double quotes
Example:
"Name","Address","Phone","Gender",etc.etc.etc. etc
"Mary","123 Any Street","555-1212","F"
"Bill","PO Box 1","","M"
"Darth","","",""

What I want to do is bring in each field from row 1 and put it in a
spreadsheet going down say 500 rows in column A.
Then move to the next data row and insert each of those fields into column B.
Like this:
"Mary"
"123 Any Street"
"555-1212"
"F"
etc etc etc etc

I know READLINE will read a whole line, but I haven't found a way to read a
"value".
Any ideas?