Import Text File

[Guest]

I need to write a macro to import a text file. The file is space delimited,
but has the last column on line 2. The file will look like this:

column1 column2 column3 column4 column5
column6
column1 column2 column3 column4 column5
column6

That 6th column will run almost the entire length of the previous 5. Is
there a way to import this as the 6th column instead of having it split from
the first line of data? Any help with this would be appreciated. Thank you!

 

[Dave Peterson]

If the total number of lines (including the column6 line) fit in a worksheet,
I'd just import the data and fix it after the import.

I'd put this in F1 (and drag down):
=IF(MOD(ROW(),2)=1,A2,NA())

Then select column F
edit|copy
Edit|paste special|Values

with column F still selected
edit|goto|special|Constants and uncheck Numbers, Text, Logicals--but leave
Errors checked)

Then rightclick on one of the selected N/A's and choose delete|entire row.

 

[Dave Peterson]

And to be totally honest, deleting the rows that have #n/a in them using this
technique may not work (if you have over 16k rows of data (8k separate areas).

You could sort the data by that column, then delete the #n/a rows pretty quickly
though.

And if the original sort order is important....

Add another helper column (column G???) and put:
=row()
and drag down
select column G
edit|copy
edit|paste special|values

Then sort your data by column F, delete the N/A's and resort by column G. (And
delete column G when you're done.)