Best options for importing text file
 

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?

Best options for importing text file
 

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

Greg wrote:
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?

Best options for importing text file
 

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

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?

--

Dave Peterson

Best options for importing text file
 

"Dick Kusleika" wrote in message ...
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

Greg wrote:
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?


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.

Best options for importing text file
 

Greg

Look at the code here

http://www.dicks-blog.com/archives/2...t-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.

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


Greg wrote:
"Dick Kusleika" wrote in message
...
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

Greg wrote:
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?


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.

Best options for importing text file
 

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.



"Dick Kusleika" wrote in message
...
Greg

Look at the code here

http://www.dicks-blog.com/archives/2...t-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.

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


Greg wrote:
"Dick Kusleika" wrote in message
...
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

Greg wrote:
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?


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.