Best options for importing text file

[Greg]

Can you please assist.

I have a text file that can incldue tabs, CR, and LF in the one field,
so I have delimeted the fields using a ^.

eg a field can be any combination of
^abcd....CR..abcd.TAB..abcd..CR..^

I wish to import the txt without the tabs, CR and LF. The file
contains about 24 columns, by about 200 rows.

What are my bext options?

 

[Dick Kusleika]

Greg

Option 1: Write a VBA procedure that reads in the text file and cleans out
the characters you don't want, then writes to cells. You would use VBA's
file operation keywords like Open, Input, FreeFile.

Option 2: Import the text with all the characters you want, then do a find
and replace to get rid of the stuff you don't want.

Post back if you need help with either of those options.

 

[Dave Peterson]

I think if it were possible, try to change that program that creates the text
file. Instead of embedding the cr/lf's in a field, use some unique set of
characters ($$$$ maybe).

Then life would be a lot simpler.

I'm not sure how you would know if the field is on the next line(s) of the text
file or if it's just a new record.

 

[Greg]

Greg

Option 1: Write a VBA procedure that reads in the text file and cleans out
the characters you don't want, then writes to cells. You would use VBA's
file operation keywords like Open, Input, FreeFile.

Option 2: Import the text with all the characters you want, then do a find
and replace to get rid of the stuff you don't want.

Post back if you need help with either of those options.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Thank you Dick. Can you please help with option 1. I am currently
using word to do this. I would a preciate code for an alternative
solution.

 

[Dick Kusleika]

Greg

Look at the code here

http://www.dicks-blog.com/archives/2005/02/08/import-text-in-vba/

I couldn't figure what to do with the carriage returns. If Line Input
encounters a Chr$(13), it figures it's at the end of the line. You would
need some way to determine if you really were at the end of the line. Maybe
you could keep a running count of delimeters and only icrease lRow when you
know you've had 50 columns (or whatever). Anyway, this should be a good
place to start and we can take it from there.

 

[Tim Williams]

Since all of your fields are ^-delimited you could just read in the whole
file as a single string and then loop through one character at a time.
Each time you hit a "^", toggle an "Infield" boolean. If you hit a newline
while bInfield is false then start a new row in Excel.

Tim.