Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Parsing text file with fixed width Date problem

Status
Not open for further replies.

k4ghg

Technical User
Dec 25, 2001
191
US
I am reading a text file using VBA's string (i.e., Left, Right and Mid) functions. My problem is that when I encounter a string with starting and ending zeros they are truncated. For example 05192010, becomes 519201, or 00001.100 becomes 1.1 and i want the date and the entire field. Is there away to keep all the characters? Thanks... Ronnie
 
because it reads that as numeric, if you format it as text, or custom format then it's ok.

If you use excel then when you import the text file, either you import with delimit or width, select the column that you don't want it change, SET IT TO TEXT.
 



Hi,

Trouble is that, 05192010 is NOT A REAL DATE!!!

Furthermore, the string structure (mmddyyyy) will not even colate properly!!!

Solution:

1. NOT VBA
2. IMPORT your text file using Data > Import External Data...
3. Parse the Date Column using the MDY option to CONVERT your string to a REAL DATE.

If this is a recurring task, turn on you macro recorder and record EDITING the Import process. Post back with your recorded code if you need assistance.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top