Stop text import at certain column?

[nick_danger]

Hi, I'm new here and have a problemo. The CSV output from this butt-old mainframe system I use occassionally has rows of data so long that Excel can't import it all. I am unable to stop the mainframe from exporting the columns, so Excel has to stop it during the import. This is my current code. What I'm hoping for is a sort of "blanket" command to SKIP column 68 and beyond. (There can be hundreds beyond...)

ChDir "C:\AX\INPUT"
    Workbooks.OpenText Filename:="C:\AX\INPUT\ax", Origin:=437, StartRow:=1, _
        DataType:=xlDelimited, TextQualifier:=xlSingleQuote, ConsecutiveDelimiter _
        :=False, Tab:=False, Semicolon:=False, Comma:=True, Space:=False, _
        Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 1), _
        Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 2), Array(11, 2), _
        Array(12, 2), Array(13, 2), Array(14, 2), Array(15, 2), Array(16, 2), Array(17, 2), Array( _
        18, 2), Array(19, 1), Array(20, 1), Array(21, 2), Array(22, 2), Array(23, 2), Array(24, 2), _
        Array(25, 2), Array(26, 2), Array(27, 2), Array(28, 2), Array(29, 2), Array(30, 2), Array( _
        31, 2), Array(32, 2), Array(33, 2), Array(36, 2), Array(37, 2), _
        Array(38, 2), Array(39, 2), Array(40, 2), Array(41, 2), Array(42, 1), Array(43, 2), Array( _
        44, 2), Array(45, 2), Array(46, 2), Array(47, 2), Array(48, 2), Array(49, 2), Array(50, 1), _
        Array(51, 2), Array(52, 2), Array(53, 2), Array(54, 2), Array(55, 2), Array(56, 2), Array( _
        57, 2), Array(58, 1), Array(59, 2), Array(60, 2), Array(61, 2), Array(62, 2), Array(63, 2), _
        Array(64, 2), Array(65, 2), Array(66, 1), Array(67, 2), Array(68, 9)), _
        TrailingMinusNumbers:=True

Oops, I just came back to add the CODE tags, but someone beat me to it. That was fast!

[T-J]

What you want should be possible using ADO to get the data into a recordset,
then use the CopyFromRecordset Method with the MaxColumns argument.

Can you show/post a sample of the first few rows of data?

[nick_danger]

What you want should be possible using ADO to get the data into a recordset,
then use the CopyFromRecordset Method with the MaxColumns argument.

Can you show/post a sample of the first few rows of data?

I don't know what ADO is, but I think we can figure it out.

'01','00000032 ','000+00.000','????????','Z','   ','3','5','19','1063','7','03','08','2003','1937','A','00','02','02','45','01','03','01','98','05','00','01','02','01','05','031410150','01','01','06','02','15','01','021','F','01','07','01','01','00','01','053','F','01','01','06','02','15','98','891','F','01','07','01','01','00','98','891','F','01','01','06','02','15','98','891','F','
'01','00000035 ','000+00.536','0000.536','S','201','1','6','24','0000','1','08','03','2003','1651','A','00','02','21','55','01','02','01','98','01','01','00','01','05','01','032720147','02','05','14','03','08','02','023','M','02','05','01','01','00','01','033','M','02','05','01','01','00','98','026','F','02','05','14','03','08','98','028','M','02','05','14','03','08','00','027','M','

[T-J]

This will import the first 40 columns of TestData.csv.

• Change the 2 constants as required
• The csv file is in same folder as the workbook
• Set a reference to the ADO library (Tools>References>Microsoft ActiveX Data Objects 2.8 Library)

Sub ImportColumns()
    Dim sPathToTextFile As String
    'set a reference to ADO library (Tools>References>Microsoft ActiveX Data Objects 2.8 Library)
    Dim rsData As ADODB.Recordset
    Dim ws As Worksheet
    Dim sConnection As String
    Dim sSQL As String
    
    Const CSV_FILE As String = "TestData.csv"
    Const MAX_COLS As Integer = 40
    
    Set ws = ThisWorkbook.Worksheets("sheet1")      'results sheet
    
    sPathToTextFile = ThisWorkbook.Path   'text file is in same folder as this workbook
    If Right(sPathToTextFile, 1) <> "\" Then sPathToTextFile = sPathToTextFile & "\"
    
    sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & sPathToTextFile & ";" & _
              "Extended Properties=""text;HDR=No;FMT=CSVDelimited"""
              
    sSQL = "SELECT * FROM " & CSV_FILE              'query string
    
    Set rsData = New ADODB.Recordset
    
    'get data into Recordset
    rsData.Open sSQL, sConnection, adOpenStatic, adLockReadOnly, adCmdText
    
    If Not rsData.EOF Then                          'check for empty recordset
        ws.UsedRange.Clear                          'clear results sheet
        ws.[A1].CopyFromRecordset rsData, , MAX_COLS
    Else
        MsgBox "No data"
    End If
    
    rsData.Close
    Set rsData = Nothing
End Sub

[nick_danger]

Please forgive my ignorance. I may have incorrectly described the original file. The output file from the mainframe is not a CSV file per se, but rather a comma-seperated text file with no extension. I don't know if this matters or not.

OK, I set the reference and changed my constants to their correct values, but the macro ends in error on this line:

    rsData.Open sSQL, sConnection, adOpenStatic, adLockReadOnly, adCmdText

Here's how the macro looks at present:

Sub ImportColumns()
    Dim sPathToTextFile As String
    'set a reference to ADO library (Tools>References>Microsoft ActiveX Data Objects 2.8 Library)
    Dim rsData As ADODB.Recordset
    Dim ws As Worksheet
    Dim sConnection As String
    Dim sSQL As String
    
    Const CSV_FILE As String = "C:\AX\INPUT\ax"
    Const MAX_COLS As Integer = 67
    
    Set ws = ThisWorkbook.Worksheets("sheet1")      'results sheet
    
    sPathToTextFile = ThisWorkbook.Path   'text file is in same folder as this workbook
    If Right(sPathToTextFile, 1) <> "\" Then sPathToTextFile = sPathToTextFile & "\"
    
    sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & sPathToTextFile & ";" & _
              "Extended Properties=""text;HDR=No;FMT=CSVDelimited"""
              
    sSQL = "SELECT * FROM " & CSV_FILE              'query string
    
    Set rsData = New ADODB.Recordset
    
    'get data into Recordset
    rsData.Open sSQL, sConnection, adOpenStatic, adLockReadOnly, adCmdText
    
    If Not rsData.EOF Then                          'check for empty recordset
        ws.UsedRange.Clear                          'clear results sheet
        ws.[A1].CopyFromRecordset rsData, , MAX_COLS
    Else
        MsgBox "No data"
    End If
    
    rsData.Close
    Set rsData = Nothing
End Sub

[T-J]

Please forgive my ignorance. I may have incorrectly described the original file. The output file from the mainframe is not a CSV file per se, but rather a comma-seperated text file with no extension. I don't know if this matters or not.

OK, I set the reference and changed my constants to their correct values, but the macro ends in error on this line:

    rsData.Open sSQL, sConnection, adOpenStatic, adLockReadOnly, adCmdText

No, that shouldn't matter.
I see what you've got wrong though. The csv/text file and path to the file need to be kept separate, as in the original code.
The csv/text file is used as the database table in the query and the path is part of the connection string.

Just change these lines in the code to:

Const CSV_FILE As String = "ax.txt"

sPathToTextFile = "C:\AX\INPUT"