i have large data file i need to load into excel
15000 rows by 300 columns
i know there is limitation on 256 columns, however i would like to know how
to split the colums at will, say 150 in 1st sheet and 150 in 2nd sheet

i know there is a macro for that on ms support, i got it, but it's not user
friendly,
there are no inputs for rows to be split etc...

so the question is: is there a user friendly add on or a macro that i can
install that allows splitting one file by rows into different sheets, for
easy loading into excel ?

thanks!

It's been three years and I don't even remember writing this.
But I must have saved it for some reason. <g
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


'The following code allows the importation of delimited text files,
'that exceed 256 columns, directly into a Excel spreadsheet.

'The code was written using the MSKB article # 120596
'"XL: Importing Text Files Larger Than 16384 Rows" as a base.
'It was modified by using a Byte array to check the number of
'delimiters in each file string and to split the string at the
'256the column if there are more than 255 delimiters.
'The second portion of the string is added to a second worksheet.
'Each row that is split is noted by bold font.
'If the string exceeds 512 chunks (columns), then the code will have to be modified.
'The Excel "Text to Columns"'utility can be used to parse all rows on both sheets.

'Code modified by Jim Cone on May 11, 2003.
Sub LargeFileImport_revised()
Dim ResultStr2 As String
Dim ResultStr As String
Dim GetUserData As Variant
Dim FileNum As Integer
Dim Counter As Long
Dim i As Long
Dim N As Long
Dim TooLong As Boolean
Dim strSeparator As Byte
Dim StringHolder() As Byte

'Ask user for the character that separates the data.
GetUserData = InputBox(vbCr & "Enter the separator character. " & vbCr & _
"One character only.", " Large Text File Import", _
" A space will work, ""tab"" will not")
If Len(GetUserData) = 0 Or Len(GetUserData) 1 Then
Exit Sub
Else
strSeparator = Asc(GetUserData)
End If

'Ask User for File's Name
GetUserData = Application.GetOpenFilename(Title:=" Large Text File Import")
'Check for no entry
If Len(GetUserData) = 0 Or GetUserData = False Then Exit Sub
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open GetUserData For Input As #FileNum

Application.ScreenUpdating = False
Worksheets.Add befo=Sheets(1), Count:=2
On Error Resume Next 'Duplicate sheet names are not allowed.
Worksheets(1).Name = "Columns 1 to 256"
Worksheets(2).Name = "Columns 257 and up"
On Error GoTo 0
Worksheets(1).Activate

Counter = 1
'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & GetUserData
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Use a Byte array to hold the string
StringHolder() = ResultStr
For i = 0 To UBound(StringHolder) Step 2
If StringHolder(i) = strSeparator Then
N = N + 1
If N 255 Then
TooLong = True
Exit For
End If
End If
Next 'i

'If more than 256 chunks (columns)
If TooLong Then
i = i \ 2
ResultStr2 = Right$(ResultStr, Len(ResultStr) - InStr(i, ResultStr, Chr$(strSeparator), vbTextCompare))
ResultStr = Left$(ResultStr, WorksheetFunction.Max(InStr(i, ResultStr, Chr$(strSeparator), vbTextCompare) - 1, 0))
'Add first portion of string to the first worksheet.
If Left(ResultStr, 1) = "=" Then
Cells(Counter, 1).Value = "'" & ResultStr
Else
Cells(Counter, 1).Value = ResultStr
End If
Cells(Counter, 1).Font.Bold = True

'Add balance of string to the second worksheet.
If Left(ResultStr2, 1) = "=" Then
Worksheets(2).Cells(Counter, 1).Value = "'" & ResultStr2
Else
Worksheets(2).Cells(Counter, 1).Value = ResultStr2
End If
TooLong = False
Else
'Store entire string on the first worksheet.
If Left(ResultStr, 1) = "=" Then
Cells(Counter, 1).Value = "'" & ResultStr
Else
Cells(Counter, 1).Value = ResultStr
End If
End If

'Refresh variables
N = 0
Erase StringHolder()
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
Loop

'Close The Open Text File
Close
Application.StatusBar = False
End Sub
'------------------------------------------


"bluelagoon_HP"

wrote in message
i have large data file i need to load into excel
15000 rows by 300 columns
i know there is limitation on 256 columns, however i would like to know how
to split the colums at will, say 150 in 1st sheet and 150 in 2nd sheet

i know there is a macro for that on ms support, i got it, but it's not user
friendly,
there are no inputs for rows to be split etc...

so the question is: is there a user friendly add on or a macro that i can
install that allows splitting one file by rows into different sheets, for
easy loading into excel ?
thanks!

Jim,
i ran your macro code on 300x100 standard csv file comma delimited,
except last value in the record row which simply has cr/lf
got out of memory error 7 and ***it only imported 5 rows***
i did text to column conversion...
i got 1 gig and plenty of spare ram and disk space

same thing ran on scintific format data tab delimited
same error but i don't expect it to work on that data...

"How to import data with more than 256 fields or columns into Excel"
article 272729 microsoft code is not working
on standard csv file 300x100... comes up with "error occured in the code"
error

this is crazy... excel and only 256 columns and bugs everywhere...

somebody, help debug this stuff, i need clean macro or addon that can load
stardard comman delimited csv files with cr/lf at end of line record... that
can load
data with 300columns by splitting them between sheets...
links, pointers, code is much appreciated.
Mike

"Jim Cone" wrote:

It's been three years and I don't even remember writing this.
But I must have saved it for some reason. <g
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


'The following code allows the importation of delimited text files,
'that exceed 256 columns, directly into a Excel spreadsheet.

'The code was written using the MSKB article # 120596
'"XL: Importing Text Files Larger Than 16384 Rows" as a base.
'It was modified by using a Byte array to check the number of
'delimiters in each file string and to split the string at the
'256the column if there are more than 255 delimiters.
'The second portion of the string is added to a second worksheet.
'Each row that is split is noted by bold font.
'If the string exceeds 512 chunks (columns), then the code will have to be modified.
'The Excel "Text to Columns"'utility can be used to parse all rows on both sheets.

'Code modified by Jim Cone on May 11, 2003.
Sub LargeFileImport_revised()
Dim ResultStr2 As String
Dim ResultStr As String
Dim GetUserData As Variant
Dim FileNum As Integer
Dim Counter As Long
Dim i As Long
Dim N As Long
Dim TooLong As Boolean
Dim strSeparator As Byte
Dim StringHolder() As Byte

'Ask user for the character that separates the data.
GetUserData = InputBox(vbCr & "Enter the separator character. " & vbCr & _
"One character only.", " Large Text File Import", _
" A space will work, ""tab"" will not")
If Len(GetUserData) = 0 Or Len(GetUserData) 1 Then
Exit Sub
Else
strSeparator = Asc(GetUserData)
End If

'Ask User for File's Name
GetUserData = Application.GetOpenFilename(Title:=" Large Text File Import")
'Check for no entry
If Len(GetUserData) = 0 Or GetUserData = False Then Exit Sub
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open GetUserData For Input As #FileNum

Application.ScreenUpdating = False
Worksheets.Add befo=Sheets(1), Count:=2
On Error Resume Next 'Duplicate sheet names are not allowed.
Worksheets(1).Name = "Columns 1 to 256"
Worksheets(2).Name = "Columns 257 and up"
On Error GoTo 0
Worksheets(1).Activate

Counter = 1
'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & GetUserData
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Use a Byte array to hold the string
StringHolder() = ResultStr
For i = 0 To UBound(StringHolder) Step 2
If StringHolder(i) = strSeparator Then
N = N + 1
If N 255 Then
TooLong = True
Exit For
End If
End If
Next 'i

'If more than 256 chunks (columns)
If TooLong Then
i = i \ 2
ResultStr2 = Right$(ResultStr, Len(ResultStr) - InStr(i, ResultStr, Chr$(strSeparator), vbTextCompare))
ResultStr = Left$(ResultStr, WorksheetFunction.Max(InStr(i, ResultStr, Chr$(strSeparator), vbTextCompare) - 1, 0))
'Add first portion of string to the first worksheet.
If Left(ResultStr, 1) = "=" Then
Cells(Counter, 1).Value = "'" & ResultStr
Else
Cells(Counter, 1).Value = ResultStr
End If
Cells(Counter, 1).Font.Bold = True

'Add balance of string to the second worksheet.
If Left(ResultStr2, 1) = "=" Then
Worksheets(2).Cells(Counter, 1).Value = "'" & ResultStr2
Else
Worksheets(2).Cells(Counter, 1).Value = ResultStr2
End If
TooLong = False
Else
'Store entire string on the first worksheet.
If Left(ResultStr, 1) = "=" Then
Cells(Counter, 1).Value = "'" & ResultStr
Else
Cells(Counter, 1).Value = ResultStr
End If
End If

'Refresh variables
N = 0
Erase StringHolder()
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
Loop

'Close The Open Text File
Close
Application.StatusBar = False
End Sub
'------------------------------------------


"bluelagoon_HP"

wrote in message
i have large data file i need to load into excel
15000 rows by 300 columns
i know there is limitation on 256 columns, however i would like to know how
to split the colums at will, say 150 in 1st sheet and 150 in 2nd sheet

i know there is a macro for that on ms support, i got it, but it's not user
friendly,
there are no inputs for rows to be split etc...

so the question is: is there a user friendly add on or a macro that i can
install that allows splitting one file by rows into different sheets, for
easy loading into excel ?
thanks!

Mike,
An Excel cell is limited to 32,767 characters.
If any of your text rows exceed that then things will go bad.

The beta of XL2007 is available (for free) from Microsoft.
It has 16,000 columns on each worksheet.
The cell character limit has also been increased. ( i believe).
Regards,
Jim Cone
http://www.officeletter.com/blink/specialsort.html


"bluelagoon_HP"

wrote in message
Jim,
i ran your macro code on 300x100 standard csv file comma delimited,
except last value in the record row which simply has cr/lf
got out of memory error 7 and ***it only imported 5 rows***
i did text to column conversion...
i got 1 gig and plenty of spare ram and disk space

same thing ran on scintific format data tab delimited
same error but i don't expect it to work on that data...

"How to import data with more than 256 fields or columns into Excel"
article 272729 microsoft code is not working
on standard csv file 300x100... comes up with "error occured in the code"
error

this is crazy... excel and only 256 columns and bugs everywhere...

somebody, help debug this stuff, i need clean macro or addon that can load
stardard comman delimited csv files with cr/lf at end of line record... that
can load
data with 300columns by splitting them between sheets...
links, pointers, code is much appreciated.
Mike

-snip-

Jim,
each *cell* contains a number, 8 digits
the delimiter is comma
at the end of each record line there is CR/LF but no comma there
this per csv file specification

300 rows by 10K columns, but i bombs even on 300 by 100 size

as i understand the excel limit is 256 columns by 65,536 rows

why your macro and microsoft macro bombs i am not sure since i am
using pretty straight csv data
microsoft macro ( solution 272729 ) handles csv files only
your macro handles delimited files, but i am not sure if it recognized CR/LF
at the end of the row of data ?

i am upset at microsoft: why the 256 limit, why the bugs at such simple
function ?

Mike.


"bluelagoon_HP" wrote:

Jim,
i ran your macro code on 300x100 standard csv file comma delimited,
except last value in the record row which simply has cr/lf
got out of memory error 7 and ***it only imported 5 rows***
i did text to column conversion...
i got 1 gig and plenty of spare ram and disk space

same thing ran on scintific format data tab delimited
same error but i don't expect it to work on that data...

"How to import data with more than 256 fields or columns into Excel"
article 272729 microsoft code is not working
on standard csv file 300x100... comes up with "error occured in the code"
error

this is crazy... excel and only 256 columns and bugs everywhere...

somebody, help debug this stuff, i need clean macro or addon that can load
stardard comman delimited csv files with cr/lf at end of line record... that
can load
data with 300columns by splitting them between sheets...
links, pointers, code is much appreciated.
Mike

"Jim Cone" wrote:

It's been three years and I don't even remember writing this.
But I must have saved it for some reason. <g
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


'The following code allows the importation of delimited text files,
'that exceed 256 columns, directly into a Excel spreadsheet.

'The code was written using the MSKB article # 120596
'"XL: Importing Text Files Larger Than 16384 Rows" as a base.
'It was modified by using a Byte array to check the number of
'delimiters in each file string and to split the string at the
'256the column if there are more than 255 delimiters.
'The second portion of the string is added to a second worksheet.
'Each row that is split is noted by bold font.
'If the string exceeds 512 chunks (columns), then the code will have to be modified.
'The Excel "Text to Columns"'utility can be used to parse all rows on both sheets.

'Code modified by Jim Cone on May 11, 2003.
Sub LargeFileImport_revised()
Dim ResultStr2 As String
Dim ResultStr As String
Dim GetUserData As Variant
Dim FileNum As Integer
Dim Counter As Long
Dim i As Long
Dim N As Long
Dim TooLong As Boolean
Dim strSeparator As Byte
Dim StringHolder() As Byte

'Ask user for the character that separates the data.
GetUserData = InputBox(vbCr & "Enter the separator character. " & vbCr & _
"One character only.", " Large Text File Import", _
" A space will work, ""tab"" will not")
If Len(GetUserData) = 0 Or Len(GetUserData) 1 Then
Exit Sub
Else
strSeparator = Asc(GetUserData)
End If

'Ask User for File's Name
GetUserData = Application.GetOpenFilename(Title:=" Large Text File Import")
'Check for no entry
If Len(GetUserData) = 0 Or GetUserData = False Then Exit Sub
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open GetUserData For Input As #FileNum

Application.ScreenUpdating = False
Worksheets.Add befo=Sheets(1), Count:=2
On Error Resume Next 'Duplicate sheet names are not allowed.
Worksheets(1).Name = "Columns 1 to 256"
Worksheets(2).Name = "Columns 257 and up"
On Error GoTo 0
Worksheets(1).Activate

Counter = 1
'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & GetUserData
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Use a Byte array to hold the string
StringHolder() = ResultStr
For i = 0 To UBound(StringHolder) Step 2
If StringHolder(i) = strSeparator Then
N = N + 1
If N 255 Then
TooLong = True
Exit For
End If
End If
Next 'i

'If more than 256 chunks (columns)
If TooLong Then
i = i \ 2
ResultStr2 = Right$(ResultStr, Len(ResultStr) - InStr(i, ResultStr, Chr$(strSeparator), vbTextCompare))
ResultStr = Left$(ResultStr, WorksheetFunction.Max(InStr(i, ResultStr, Chr$(strSeparator), vbTextCompare) - 1, 0))
'Add first portion of string to the first worksheet.
If Left(ResultStr, 1) = "=" Then
Cells(Counter, 1).Value = "'" & ResultStr
Else
Cells(Counter, 1).Value = ResultStr
End If
Cells(Counter, 1).Font.Bold = True

'Add balance of string to the second worksheet.
If Left(ResultStr2, 1) = "=" Then
Worksheets(2).Cells(Counter, 1).Value = "'" & ResultStr2
Else
Worksheets(2).Cells(Counter, 1).Value = ResultStr2
End If
TooLong = False
Else
'Store entire string on the first worksheet.
If Left(ResultStr, 1) = "=" Then
Cells(Counter, 1).Value = "'" & ResultStr
Else
Cells(Counter, 1).Value = ResultStr
End If
End If

'Refresh variables
N = 0
Erase StringHolder()
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
Loop

'Close The Open Text File
Close
Application.StatusBar = False
End Sub
'------------------------------------------


"bluelagoon_HP"

wrote in message
i have large data file i need to load into excel
15000 rows by 300 columns
i know there is limitation on 256 columns, however i would like to know how
to split the colums at will, say 150 in 1st sheet and 150 in 2nd sheet

i know there is a macro for that on ms support, i got it, but it's not user
friendly,
there are no inputs for rows to be split etc...

so the question is: is there a user friendly add on or a macro that i can
install that allows splitting one file by rows into different sheets, for
easy loading into excel ?
thanks!

May be you can try split method

after split
for i = 0 to ubound(v)
if i < your desired number then
sht1.cells(row, i+1)=v(i)
else
sht2.cells(row, i+1 - your desired number)=v(i)
end if
next i



"bluelagoon_HP" wrote in message
...
Jim,
each *cell* contains a number, 8 digits
the delimiter is comma
at the end of each record line there is CR/LF but no comma there
this per csv file specification

300 rows by 10K columns, but i bombs even on 300 by 100 size

as i understand the excel limit is 256 columns by 65,536 rows

why your macro and microsoft macro bombs i am not sure since i am
using pretty straight csv data
microsoft macro ( solution 272729 ) handles csv files only
your macro handles delimited files, but i am not sure if it recognized
CR/LF
at the end of the row of data ?

i am upset at microsoft: why the 256 limit, why the bugs at such simple
function ?

Mike.


"bluelagoon_HP" wrote:

Jim,
i ran your macro code on 300x100 standard csv file comma delimited,
except last value in the record row which simply has cr/lf
got out of memory error 7 and ***it only imported 5 rows***
i did text to column conversion...
i got 1 gig and plenty of spare ram and disk space

same thing ran on scintific format data tab delimited
same error but i don't expect it to work on that data...

"How to import data with more than 256 fields or columns into Excel"
article 272729 microsoft code is not working
on standard csv file 300x100... comes up with "error occured in the
code"
error

this is crazy... excel and only 256 columns and bugs everywhere...

somebody, help debug this stuff, i need clean macro or addon that can
load
stardard comman delimited csv files with cr/lf at end of line record...
that
can load
data with 300columns by splitting them between sheets...
links, pointers, code is much appreciated.
Mike

"Jim Cone" wrote:

It's been three years and I don't even remember writing this.
But I must have saved it for some reason. <g
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


'The following code allows the importation of delimited text files,
'that exceed 256 columns, directly into a Excel spreadsheet.

'The code was written using the MSKB article # 120596
'"XL: Importing Text Files Larger Than 16384 Rows" as a base.
'It was modified by using a Byte array to check the number of
'delimiters in each file string and to split the string at the
'256the column if there are more than 255 delimiters.
'The second portion of the string is added to a second worksheet.
'Each row that is split is noted by bold font.
'If the string exceeds 512 chunks (columns), then the code will have
to be modified.
'The Excel "Text to Columns"'utility can be used to parse all rows on
both sheets.

'Code modified by Jim Cone on May 11, 2003.
Sub LargeFileImport_revised()
Dim ResultStr2 As String
Dim ResultStr As String
Dim GetUserData As Variant
Dim FileNum As Integer
Dim Counter As Long
Dim i As Long
Dim N As Long
Dim TooLong As Boolean
Dim strSeparator As Byte
Dim StringHolder() As Byte

'Ask user for the character that separates the data.
GetUserData = InputBox(vbCr & "Enter the separator character. " &
vbCr & _
"One character only.", " Large Text File Import", _
" A space will work, ""tab"" will not")
If Len(GetUserData) = 0 Or Len(GetUserData) 1 Then
Exit Sub
Else
strSeparator = Asc(GetUserData)
End If

'Ask User for File's Name
GetUserData = Application.GetOpenFilename(Title:=" Large Text File
Import")
'Check for no entry
If Len(GetUserData) = 0 Or GetUserData = False Then Exit Sub
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open GetUserData For Input As #FileNum

Application.ScreenUpdating = False
Worksheets.Add befo=Sheets(1), Count:=2
On Error Resume Next 'Duplicate sheet names are not allowed.
Worksheets(1).Name = "Columns 1 to 256"
Worksheets(2).Name = "Columns 257 and up"
On Error GoTo 0
Worksheets(1).Activate

Counter = 1
'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & GetUserData
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Use a Byte array to hold the string
StringHolder() = ResultStr
For i = 0 To UBound(StringHolder) Step 2
If StringHolder(i) = strSeparator Then
N = N + 1
If N 255 Then
TooLong = True
Exit For
End If
End If
Next 'i

'If more than 256 chunks (columns)
If TooLong Then
i = i \ 2
ResultStr2 = Right$(ResultStr, Len(ResultStr) - InStr(i,
ResultStr, Chr$(strSeparator), vbTextCompare))
ResultStr = Left$(ResultStr, WorksheetFunction.Max(InStr(i,
ResultStr, Chr$(strSeparator), vbTextCompare) - 1, 0))
'Add first portion of string to the first worksheet.
If Left(ResultStr, 1) = "=" Then
Cells(Counter, 1).Value = "'" & ResultStr
Else
Cells(Counter, 1).Value = ResultStr
End If
Cells(Counter, 1).Font.Bold = True

'Add balance of string to the second worksheet.
If Left(ResultStr2, 1) = "=" Then
Worksheets(2).Cells(Counter, 1).Value = "'" & ResultStr2
Else
Worksheets(2).Cells(Counter, 1).Value = ResultStr2
End If
TooLong = False
Else
'Store entire string on the first worksheet.
If Left(ResultStr, 1) = "=" Then
Cells(Counter, 1).Value = "'" & ResultStr
Else
Cells(Counter, 1).Value = ResultStr
End If
End If

'Refresh variables
N = 0
Erase StringHolder()
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
Loop

'Close The Open Text File
Close
Application.StatusBar = False
End Sub
'------------------------------------------


"bluelagoon_HP"

wrote in message
i have large data file i need to load into excel
15000 rows by 300 columns
i know there is limitation on 256 columns, however i would like to
know how
to split the colums at will, say 150 in 1st sheet and 150 in 2nd sheet

i know there is a macro for that on ms support, i got it, but it's not
user
friendly,
there are no inputs for rows to be split etc...

so the question is: is there a user friendly add on or a macro that i
can
install that allows splitting one file by rows into different sheets,
for
easy loading into excel ?
thanks!