Results 1 to 8 of 8
  1. #1
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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<pre> TextFileColumnDataTypes = Array(1, 1, 1, 1)</pre>

    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?

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

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

    As you might suspect, this question is connected to my <post#=252109>post 252109</post#>. 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?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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. <LI>The name of the module the code should be written to. This module must already exist.
      <LI>The name of the text file (using the standard open dialog).
      <LI>The total number of columns in the text file (the code could be expanded to determine this automatically).
      <LI>The first column to be imported, e.g. 257.
      <LI>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(sModu le).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

  5. #5
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

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

    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) <= LOF(FileNum)
    'Display Importing Row Number On Status Bar
    Application.StatusBar = "Importing Row " & _
    Counter & " of text file " & FileName
    'Store One Line Of Text From File To Variable
    Line Input #FileNum, ResultStr
    'Use a Byte array to hold the string
    ChunkCount() = ResultStr
    For i = 0 To UBound(ChunkCount) Step 2
    ' 44 is a comma, adjust as necessary
    If ChunkCount(i) = 44 Then
    N = N + 1
    If N > 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

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

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

    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.
    "
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  7. #7
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

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

    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) <= 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
    '---------------------------------------------------------

    Regards,

    Jim Cone

  8. #8
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    See <post#=255988>post 255988</post#>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •