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...)

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

[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)

Please Login or Register  to view this content.

[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:

Please Login or Register  to view this content.

Here's how the macro looks at present:

Please Login or Register  to view this content.

[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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

[nick_danger]

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:

Please Login or Register  to view this content.

Great, thanks for the help!!