Cleaning and formating txt files.

[biggiewilly]

Hello all

I'm trying to format a .txt file for linking and reporting.

its FTPed with coloum headers info and I need to remove that before I link the table: is there a way to do this in access or before using VBA.

##########
##################

########
#####
#########


CALL LIST ACCOUNTS CALLED
------------------- --------- ---------
NB_358CENT 2614 1313

 

[payback]

Do you need to link it as .txt or can you import it into a table and report from there? If the latter I have posted code elsewhere in Tek-Tips in thread702-951988 that lets you read a text file line by line and do any appropriate action (you will need some analytical vba skills).

 

[biggiewilly]

Actually I would like to link the .txt file and copy over it as needed. The thing is I would like clean the header part before I do update query on it. My VBA skills are limited as of yet I do most of my work in crystal and this report requires access, My thinking was once I get the header info removed I can then do a create table on it and then update?

 

[biggiewilly]

Thread702-951988: thats a "UBER" statment to me

Do you have any simple macro's that I could practice with.

Thanks
Jason.

 

[payback]

Did you click on the link to that thread?

Not a macro but it is VBA code that bypasses the need for an update query. It basically reads through each line of a text file (in this case fixed format) and depending on what it finds on the line you tell it what to do. In your case, you would probably tell it to miss the first 10 or so lines, and then process the rest of the lines the same by writing those fields to a record in a file.

Look at the last examples in that post.

 

[biggiewilly]

Thanks Payback, but it looks like its a little above my head.

Do you have a "simple vba statment" that I could try to wrap my brain around.

 

[payback]

My first question would have to be if you have done any VBA coding at all so I can judge how much detail I would have to put into the sample.

If you have some experience, I would then need more information from you about the tables etc. but let's leave that until you reply to the above.

There is no "simple vba statement" to address your request and it will require you to think about the process rather than me to write it all for you. Assistance with ideas and concepts is the main aim of this forum, not actually doing the work for you.

 

[biggiewilly]

Thanks Payback: But you answered your own question. No VBA coding

I'm going to cut it in excel then import it until I can get some skills with VBA or .Net.

Thanks for help with this issue.

Jason.

 

[payback]

Would you like me to assist you further with this so you can start to develop these skills?