Importing 256+ colum text file into Excel (Excel 2000)

Topic

New Reply

Wild uneducated guess here:
Could the 256 limit be inherent within the wizard? If I record a macro of an import of a csv file, I note a line

     TextFileColumnDataTypes = Array(1, 1, 1, 1)

with the number of “1’s” being related to the number of columns imported.
Would a macro with an awful lot of 1’s in it work?

Reply | Quote

Replies

As you might suspect, this question is connected to my post 252109. If one had a tab(or comma) delimited file with more than 256 columns, what would be the most efficient way to bring it into Excel?
When I fake up a sample .txt file, I find that the wizard, brings in only the first row of data (the full 256 columns) and drops everything else.
If I copy/paste I get the rows of data, but it drops everything over 256 columns. Oh and there is an error message for each row.
Suggestions?

Reply | Quote

another wild uneducated stab in the dark (no offence meant, Leif!) – can you deal with this using the spreadsheet component of IE? I seem to recall posts of about a year ago indicating that the number of columns in that was unlimited. I remember checking said posts at the time and seeing columns beyond IV, but have no idea how to get back there now. – or how you would go about manipulating it anyway.

Just a thought. Legare – why do you spring to mind?

Reply | Quote

Here is a macro to create a macro to import columns from a tab-delimited text file. It is rather rough – there is no error checking. To be able to create macros in code, you must set a reference to Microsoft Visual Basic For Applications Extensibility 5.3 (the version number is the one on my XP system, might be different for 2000). In XP, I also needed to set an option within Excel itself – I don’t know if this applies to 2000: in the Security tab of Tools | Options…, click Macro Security, and uncheck ‘Trust access to Visual Basic project’. You may want to check this option again when you’re finished with this.

The macro will prompt for:

1. The name of the module the code should be written to. This module must already exist.
2. The name of the text file (using the standard open dialog).
3. The total number of columns in the text file (the code could be expanded to determine this automatically).
4. The first column to be imported, e.g. 257.
5. The last column to be imported, e.g. 512.

Columns to be imported are all imported as “general”, i.e. Excel determines the type (text, numeric, date/time)

Here is the code:

Sub CreateImportSub()
Dim m As VBIDE.CodeModule
Dim sModule As String
Dim i As Integer
Dim s As String
Dim sFile As String
Dim iCount As Integer
Dim iFirst As Integer
Dim iLast As Integer
Dim varResult As Variant

‘ Get name of module
varResult = Application.InputBox(“Name of module”, , “Module1”, , , , , 2)
If varResult = False Then Exit Sub
sModule = varResult
‘ Get name of text file
varResult = Application.GetOpenFilename
If varResult = False Then Exit Sub
sFile = varResult
‘ Get total number of columns
varResult = Application.InputBox(“Total number of columns”, , , , , , , 1)
If varResult = False Then Exit Sub
iCount = varResult
‘ Get first column to be imported
varResult = Application.InputBox(“First column to be imported”, , , , , , , 1)
If varResult = False Then Exit Sub
iFirst = varResult
‘ Get last column to be imported
varResult = Application.InputBox(“Last column to be imported”, , , , , , , 1)
If varResult = False Then Exit Sub
iLast = varResult
‘ Set reference to module
Set m = Application.VBE.ActiveVBProject.VBComponents(sModule).CodeModule

‘ Create procedure
m.InsertLines m.CountOfLines, “Sub ImportTextColumns()”
m.InsertLines m.CountOfLines, vbTab & “Workbooks.OpenText Filename:=””” & sFile & “””, _”
m.InsertLines m.CountOfLines, vbTab & vbTab & “Origin:=xlWindows, StartRow:=1, ” & _
“DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Tab:=True, _”
m.InsertLines m.CountOfLines, vbTab & vbTab & “FieldInfo:=Array( _”

For i = 1 To iCount – 1
s = s & “Array(” & i
Select Case i
Case iFirst To iLast
s = s & “, 1), ”
Case Else
s = s & “, 9), ”
End Select
If i Mod 32 = 0 Then
s = vbTab & vbTab & s & “_”
m.InsertLines m.CountOfLines, s
s = “”
End If
Next i
s = vbTab & vbTab & s & “Array(” & iCount
Select Case iCount
Case iFirst To iLast
s = s & “, 1))”
Case Else
s = s & “, 9))”
End Select
m.InsertLines m.CountOfLines, s
m.InsertLines m.CountOfLines, “End Sub”
End Sub

Reply | Quote

Oh, WOW. Thanks Hans and Jim. You’ve both given me something to think about. Ideally, I want to fill up multiple sheets with the information.
You know, sheet one will contain columns 1 – 256, sheet two will contain columns 257 – 512; and so on.

Cheers

ps
the following is from the MSDN site
“Set a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 library in file Vbe6ext.olb.
Note If the object library does not appear in the list of available references, you can browse for it in C:Program FilesCommon FilesMicrosoft SharedVBAVBA6, the default installation directory. The name of the library as it appears in the Object Browser is VBIDE.“

Reply | Quote

Catherine,

I’ve modified the code to place the portion of the string exceeding 256 columns on a second worksheet.
I think I will quit now…
‘——————————–
‘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 before:=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) 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
‘———————————————————

Regards,

Jim Cone

Reply | Quote

See post 255988

Reply | Quote

Catharine,

Here is another way to do it. The code is even worse than “rough”. But it did work on a few tests I ran…

‘—————————————————————————————–
‘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 in half if
‘there are more than 255 delimiters. Each half is then put in adjacent
‘rows, one below the other. Each split row is noted with the word
‘”continued” at the beginning of the adjacent row.
‘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.

‘Code modified by Jim Cone on May 10, 2003.
Sub LargeFileImport()
Dim ResultStr As String
Dim ResultStr2 As String
Dim FileName As Variant
Dim FileNum As Integer
Dim Counter As Long
Dim i As Long
Dim N As Long
Dim TooLong As Boolean
Dim ChunkCount() As Byte

‘Ask User for File’s Name
FileName = Application.GetOpenFilename
‘Check for no entry
If Len(FileName) = 0 Or FileName = False Then End
‘Get Next Available File Handle Number
FileNum = FreeFile()
‘Open Text File For Input
Open FileName For Input As #FileNum
‘Turn Screen Updating Off
Application.ScreenUpdating = False
Worksheets.Add before:=Sheets(1), Count:=1

‘Set The Counter to 1
Counter = 1
‘Loop Until the End Of File Is Reached
Do While Seek(FileNum) 255 Then
TooLong = True
Exit For
End If
End If
Next ‘i
N = 0
‘If more than 256 chunks (columns)
If TooLong Then
ResultStr2 = Right$(ResultStr, (Len(ResultStr) 2) – 1)
ResultStr = Left$(ResultStr, Len(ResultStr) 2)
If Left(ResultStr, 1) = “=” Then
ActiveCell.Value = “‘” & ResultStr
Else
ActiveCell.Value = ResultStr
End If
ActiveCell(2, 1).Value = “CONTINUED ” & ResultStr2
ActiveCell(2, 1).Select
TooLong = False
Else
‘Store Variable Data Into Active Cell
If Left(ResultStr, 1) = “=” Then
ActiveCell.Value = “‘” & ResultStr
Else
ActiveCell.Value = ResultStr
End If
End If

If ActiveCell.Row = Rows.Count Then
‘If On The Last Row Then Add A New Sheet
Worksheets.Add before:=Sheets(1), Count:=1
Else
‘If Not The Last Row Then Go One Cell Down
ActiveCell.Offset(1, 0).Select
End If
Counter = Counter + 1
‘Start Again At Top Of ‘Do While’ Statement
Loop
‘Close The Open Text File
Close
Application.StatusBar = False
End Sub
‘——————————————————–

Regards,

Jim Cone
San Francisco, CA
jim.coneZZZ@rcn.comXXX

Reply | Quote