Guys,

I have a problem in that I need to link a text file (csv) to access, however
the text file exceeds 2gb.

Is there a way that I can breakup the file into to parts by using some vba
code?? I am not able to
have the text file re-generated into smaller tables, and am therefore stuck
as to how to split the csv file.

Anybody have any ideas?, anything would be appreciated

Cheers

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200607/1

Ozzie,

You could try a macro like the one below - a little tweaking of the upper limits on the loops may
be needed....

HTH,
Bernie
MS Excel MVP

Sub SplitFiles()
Dim ReadStr As String
Dim FileName1 As String
Dim OrigFNum As Integer
Dim FileNumOut As Integer
Dim Counter As Double
Dim i As Integer
Dim j As Long

Counter = 1

FileName1 = Application.GetOpenFilename( _
"CSV Files (*.csv),*.csv", _
, "Pick the Original File")
If FileName1 = "" Then End

OrigFNum = FreeFile()
Open FileName1 For Input As #OrigFNum

For i = 1 To 10

FileNumOut = FreeFile()
Open "SplitFile" & i & ".csv" For Output Access Write As #FileNumOut

For j = 1 To 50000
Application.StatusBar = "Processing line " & Counter
Line Input #OrigFNum, ReadStr
Print #FileNumOut, ReadStr
Counter = Counter + 1
Next j

Close #FileNumOut

Next i

Close #OrigFNum

Application.StatusBar = False

End Sub



"Ozzie via OfficeKB.com" <u18021@uwe wrote in message news:6308b801e0978@uwe...
Guys,

I have a problem in that I need to link a text file (csv) to access, however
the text file exceeds 2gb.

Is there a way that I can breakup the file into to parts by using some vba
code?? I am not able to
have the text file re-generated into smaller tables, and am therefore stuck
as to how to split the csv file.

Anybody have any ideas?, anything would be appreciated

Cheers

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200607/1

Bernie,

Thanks, that was a big help

Cheers

Bernie Deitrick wrote:
Ozzie,

You could try a macro like the one below - a little tweaking of the upper limits on the loops may
be needed....

HTH,
Bernie
MS Excel MVP

Sub SplitFiles()
Dim ReadStr As String
Dim FileName1 As String
Dim OrigFNum As Integer
Dim FileNumOut As Integer
Dim Counter As Double
Dim i As Integer
Dim j As Long

Counter = 1

FileName1 = Application.GetOpenFilename( _
"CSV Files (*.csv),*.csv", _
, "Pick the Original File")
If FileName1 = "" Then End

OrigFNum = FreeFile()
Open FileName1 For Input As #OrigFNum

For i = 1 To 10

FileNumOut = FreeFile()
Open "SplitFile" & i & ".csv" For Output Access Write As #FileNumOut

For j = 1 To 50000
Application.StatusBar = "Processing line " & Counter
Line Input #OrigFNum, ReadStr
Print #FileNumOut, ReadStr
Counter = Counter + 1
Next j

Close #FileNumOut

Next i

Close #OrigFNum

Application.StatusBar = False

End Sub

Guys,

[quoted text clipped - 9 lines]

Cheers

--
Message posted via http://www.officekb.com