How to import data with more than 256 fields or columns into Excel

Article translations Article translations
Article ID: 272729 - View products that this article applies to.
This article was previously published under Q272729
Expand all | Collapse all

SUMMARY

Microsoft Excel spreadsheets have 256 columns (column IV). Therefore, Excel cannot accept data with more than 256 fields per record on a worksheet. When you attempt to manually import data from a database or text file that has more than 256 fields per record, the additional fields are truncated from the records.

You can use a Visual Basic for Applications macro to import the data across multiple sheets. This article provides a sample macro that imports a Comma Separated Variable (CSV) formatted database with up to 510 fields into Excel. The macro places the first 256 fields on one sheet in the workbook and the remaining fields (beginning with the 257th field) on a second sheet in the workbook.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. If your database is in a format other than CSV, resave the database in the CSV format before you import it using the following macro.
  1. Start or switch to Excel.
  2. On the Tools menu, point to Macro, and then click Visual Basic Editor.
  3. On the Insert menu, click Module.
  4. In the module, type the following code:
    Sub LargeDatabaseImport()
    
        'In the event of an error, make sure the application is reset to
        'normal.
        On Error GoTo ErrorCheck
    
        'Dimension Variables
        Dim ResultStr As String
        Dim FileName As String
        Dim FileNum As Integer
        Dim Counter As Double
        Dim CommaCount As Integer
        Dim WorkResult As String
    
        'Ask for the name of the file.
        FileName = InputBox("Please type the name of your text file, for example, test.txt")
    
        'Turn off ScreenUpdating and Events so that users can't see what is 
        'happening and can't affect the code while it is running.
        Application.ScreenUpdating = False
        Application.EnableEvents = False
    
        'Check for no entry.
        If FileName = "" Then End
        
        'Get next available file handle number.
        FileNum = FreeFile()
        
        'Open text file for input.
        Open FileName For Input As #FileNum
        
        'Turn ScreenUpdating off.
        Application.ScreenUpdating = False
    
        'Set the counter to 1.
        Counter = 1
    
        'Place the data in the first row of the column.
        Range("A1").Activate
    
        'Loop until the end of file is reached.
        Do While Seek(FileNum) <= LOF(FileNum)
    
            'Show row number being imported 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
    
            'Initialize the CommaCount variable to zero.
            CommaCount = 0
            
            'Store the entire string into a second, temporary string.
            WorkResult = ResultStr
    
            'Parse through the first line of data and separate out records 
            '257 to 510.
            While CommaCount < 255
    
                WorkResult = Right(WorkResult, Len(WorkResult) - InStr(1, WorkResult, ","))
                CommaCount = CommaCount + 1
    
            Wend
    
            'Parse out any leading spaces.
            If Left(WorkResult, 1) = " " Then WorkResult = Right(WorkResult, Len(WorkResult) - 1)
    
            'Ensure that any records that contain an "=" sign are 
            'brought in as text, and set the value of the current
            'cell to the first 256 records.
            If Left(WorkResult, 1) = "=" Then
                ActiveCell.Value = "'" & Left(ResultStr, Len(ResultStr) - Len(WorkResult))
            Else
                ActiveCell.Value = Left(ResultStr, Len(ResultStr) - Len(WorkResult))
            End If
    
            'Ensure that any records that contain an "=" sign are 
            'brought in as text,and set the value of the next cell 
            'to the last 256 records.
            If Left(WorkResult, 1) = "=" Then
                ActiveCell.Offset(0, 1).Value = "'" & WorkResult
            Else
                ActiveCell.Offset(0, 1).Value = WorkResult
            End If
    
            'Move down one cell.
            ActiveCell.Offset(1, 0).Activate
    
            'Increment the Counter by 1.
            Counter = Counter + 1
    
            'Start again at top of 'Do While' statement.
        Loop
    
        'Close the open text file.
        Close
    
        'Take records 257-510 and move them to sheet two.
        Columns("B:B").Select
        Selection.Cut
        Sheets("Sheet2").Select
        Columns("A:A").Select
        ActiveSheet.Paste
    
        'Run the text-to-columns wizard on both sheets.
        Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
                :=Array(Array(1, 1), Array(2, 1), Array(3, 1))
        Sheets("Sheet1").Select
        Columns("A:A").Select
        Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
                :=Array(Array(1, 1), Array(2, 1), Array(3, 1))
    
        'Reset the application to its normal operating environment.
        Application.StatusBar = False
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    
        Exit Sub
    
    ErrorCheck:
    
        'Reset the application to its normal operating environment.
        Application.StatusBar = False
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        MsgBox "An error occured in the code."
    
    End Sub
    						
  5. On the File menu, click Close and Return to Microsoft Excel.
  6. On the Tools menu, point to Macro, and then click Macros.
  7. In the list of macros, select the LargeDatabaseImport macro. Click Run.
  8. In the dialog box that appears, type the path and file name of the CSV file you want to import.

REFERENCES

For more information about how to use the sample code in this article, click the article number below to view the article in the Microsoft Knowledge Base:
212536 OFF2000: How to Run Sample Code from Knowledge Base Articles
For additional information about getting help with Visual Basic forApplications, click the article number below to view the article in the Microsoft Knowledge Base:
226118 OFF2000: Programming Resources for Visual Basic for Applications
For additional information about Excel specifications and limiatons, click the following article number to view the article in the Microsoft Knowledge Base:
264626 Description of Excel 2000 specifications

Properties

Article ID: 272729 - Last Review: January 29, 2007 - Revision: 5.2
APPLIES TO
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
Keywords: 
kbvba kbprogramming kbautomation kbmacro kbhowto KB272729

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com