csv file manipulation (1 Viewer)

[hascons]

Hello

I'm trying to parse the following into an Array by splitting the csv file using a "," comma separator. There should be 63 different data pieces in this File. When I do a count of them from the (ubound array) i only get 54. The last data piece on each row gets concatenated to the first data piece of the next line. Is there a way to stop this from happening? This is causing problems with working with the data.


Date,Open,High,Low,Close,Volume,Adj Close
2013-06-07,1625.27,1644.40,1625.27,1643.38,3371990000,1643.38
2013-06-06,1609.29,1622.56,1598.23,1622.56,3547380000,1622.56
2013-06-05,1629.05,1629.31,1607.09,1608.90,3632350000,1608.90
2013-06-04,1640.73,1646.53,1623.62,1631.38,3653840000,1631.38
2013-06-03,1631.71,1640.42,1622.72,1640.42,3952070000,1640.42
2013-05-31,1652.13,1658.99,1630.74,1630.74,4099600000,1630.74
2013-05-30,1649.14,1661.91,1648.61,1654.41,3498620000,1654.41
2013-05-29,1656.57,1656.57,1640.05,1648.36,3587140000,1648.36

 

[CJ_London]

I think you will find the problem is with trying to split using a comma - there is no comma between the last item on line 1 and the first item on line 2.

Is there any reason you cannot simply link to or import the file as a .csv file?

 

[ChrisO]

Hascons.

Please upload the CSV file so we can see the data.

Chris.

 

[hascons]

Thanks for the response guys. I was able to get the following code to work.

I was trying to get a csv file to an array to an access table but some of the headers from the csv file violated access naming standards. since there could be several thousand folders modifying each csv file, Or using Docmd.Transfertext was a last option. I could accomplish what I needed by ignoring the first line of csv file. This is what this code accomplishes.

Public Sub GetFileToArray(ByVal FileName As String)
On Error GoTo ErrorHandler

Dim oFSO As New FileSystemObject
Dim oFSTR As Scripting.TextStream
Dim i As Long
Dim arrData() As String


If Dir(FileName) = "" Then Exit Sub

Set oFSTR = oFSO.OpenTextFile(FileName)

If Not oFSTR.AtEndOfStream Then oFSTR.SkipLine

Do While Not oFSTR.AtEndOfStream

ReDim Preserve arrData(i) As String
arrData(i) = oFSTR.ReadLine
i = i + 1
DoEvents 'optional but with large file
'program will appear to hang
'without it

Loop

oFSTR.Close


ErrorHandler:
Set oFSTR = Nothing

End Sub