Excel - Read csv to 2D array

How can I read in a csv (comma delimited) file into a 2D array in VBA?

  Joe replied...

Sub EditInput()
Const ReadFile = "c:\temp\event.txt"

Const ForReading = 1, ForWriting = 2, _
ForAppending = 3

Dim Index As Integer
Dim data() As Variant

Set fs = CreateObject("Scripting.FileSystemObject")
Set fin = fs.OpenTextFile(ReadFile, _
ForReading, TristateFalse)

Index = 0
Do While fin.AtEndOfStream <> True
ReadData = fin.readline
ReDim Preserve data(0 To 1, 0 To Index)
Splitdata = Split(ReadData, ",")
data(0, Index) = Splitdata(0)
data(1, Index) = Splitdata(1)
Index = Index + 1
Loop
fin.Close
End Sub

  Tim Williams replied...

Watch out for quoted values which may contain commas.

Tim

read files created under unix ( so terminating by LF instead if CRLF on Windows) in VBA. It seems that when i call "Line Input" , i get the full file at once . Would it be possible to get it line by line? Thx Excel Programming Discussions Scripting FileSystemObject (1) UNIX (1) VBA (1) Fwrite.OpenAsTextStream (1) Fread.OpenAsTextStream (1) Fswrite.CreateTextFile (1) TristateUseDefault (1) WritePathName (1) Yes ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C: \ temp \ " Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Dim CR As String Dim LF As String CR = Chr(13) LF = Chr(10) Set fsread = CreateObject("Scripting.FileSystemObject") Set fswrite = CreateObject("Scripting.FileSystemObject") ReadFileName = "intext.txt" WriteFileName = "outtext.txt" 'open files ReadPathName = MyPath + ReadFileName Set fread = fsread

and 'Thousands Separator' setting are being ignorred. Excel Programming Discussions QueryTables (1) Application.GetOpenFilename (1) Scripting.FileSystemObject (1) Office 2007 (1) Office 2003 (1) Excel 2007 (1) Office (1) TextFileTrailingMinusNumbers (1) MS data. Sub GetCSVData() Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Set fsread = CreateObject("Scripting.FileSystemObject") 'default folder Folder = "C: \ temp \ test" Newfolder = Application.GetOpenFilename("CSV (*.csv), *.csv") If Not Newfolder the local back to nl-nl) For the record, the bug does not occure in VBA, programmaticly OpenText behaves just as it should. Where it not for the fact that the

with something like this but each method reads the line before termination? - . . . Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.OpenTextFile("C: \ TEST.TXT", ForReading, TristateTrue) currentLine = f.ReadLine . . . and also . . . Open TXT" For Binary As #1 Line Input #1, LineofText . . . Cheers, Deon Murtagh Excel Programming Discussions Scripting.FileSystemObject (1) Excel (1) Macro (1) CHAR (1) VBA (1) VbCrLf (1) TristateFalse (1) CreateObject (1) You have to read one character at a time and check that Const CR = 13 Const ForReading = 1, ForWriting = 2, ForAppending = 3 Dim fs Set fs = CreateObject("Scripting.FileSystemObject") Set readfile = fs.OpenTextFile("c: \ temp \ readfile.txt", _ ForReading, TristateFalse) fs.CreateTextFile "c: \ temp

someone help identify my missing step? - - Richer Excel Miscellaneous Discussions Excel 2003 (1) Worksheet (1) VBA (1) TristateUseDefault (1) WriteFileName (1) WritePathName (1) ReadFileName (1) ReadPathName (1) Use filescripting method for ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C: \ temp \ " Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Set fsread = CreateObject("Scripting.FileSystemObject") Set fswrite = CreateObject("Scripting.FileSystemObject") ReadFileName = "test.csv" WriteFileName = "outtest.csv" 'open files ReadPathName = MyPath + ReadFileName Set fread = fsread.GetFile Here is slightly modified code. The code deosn't use a worksheet, just use the VBA envirnoment to run VBA. the code opens an input and output file. You need to modify the three lines