Is the possible to read Text file to a table (1 Viewer)

[jhartford]

I have a text file which has a header with several lines. Is there any to skip the lines, then read the rest of the file to a table? I have a sample file but don't know how to attached it.

Thank you in advance

 

[jdraw]

I think it would be easier to import the entire text into a table; then process the data in the table. Since you know the pattern (header lines etc), you could code accordingly.

 

[vbaInet]

Or delete the lines you want to skip before importing the text file.

 

[jhartford]

Or delete the lines you want to skip before importing the text file.

Thanks for your reply.

What I'm going to do is to create a produre to read the text files (I have several).

Can you tell me how to attach/post the text file here?

Thanks again

 

[vbaInet]

Look into the Import/Export wizard before thinking of creating a function to read the text file.

Click the Go Advanced button you will see how to attach a file but that won't be necessary yet.

 

[Galaxiom]

One of the easiest ways is to strip the headers off in a DOS command file before importing. Use the FOR command. The /F option has a Skip parameter to skip a specified number of lines.

http://www.computerhope.com/forhlp.htm

The FOR /F command can parse a file line by line on defined delimiters and use ECHO to write the selected parts in any order with any delimiter to another file. I have used it to strip page headers by recognising the strings on those lines.

A single line with two nested FOR commands can selectively parse a whole folder of text files and combine them into one output file for import. The same task in VB wouold take dozens of lines.

 

[jhartford]

One of the easiest ways is to strip the headers off in a DOS command file before importing. Use the FOR command. The /F option has a Skip parameter to skip a specified number of lines.



The FOR /F command can parse a file line by line on defined delimiters and use ECHO to write the selected parts in any order with any delimiter to another file. I have used it to strip page headers by recognising the strings on those lines.

A single line with two nested FOR commands can selectively parse a whole folder of text files and combine them into one output file for import. The same task in VB wouold take dozens of lines.

That is great! I'll look into it. Thanks.

Queation: Can I combine FOR /F command with VBA? My project is to create a sub to read several text files to tables. I need to run this sub every month.

Very appreciate you time.

 

[gemma-the-husky]

you can do this in a couple of main ways (without modifying the file). It's a matter of taste. Method a) is MUCH easier, as long as there is an identifiable structure to the file.

a) import the text file to a table. Identify a column that will be null (say) in header rows, and non-null in other rows. Delete those rows

b) open the fiel as an external file. Read the data in line by line, and process it with code

 

[Galaxiom]

DOS commands and batch files can be run from VBA with the Shell command.

The FOR command is just one of many ways to deal with importing text files that don't completely fit the Access import spec system. As a DOS devotee from the days when that is all we had, I used it until I learnt how to read and parse text files as Dave's option (b). Dave's option (a) is good if the structure of the file allows it.

I have some quite complex imports that include steps like identifying the report version from the header and processing accordingly. Some require reconnecting wrapped lines, adding extra fields, converting values etc. So I read the text file line by line, parse it and manipulate the values in code as they are written to a recordset.

Tables store the conditions which are applied to accept or reject lines with certain strings in particular locations, strings that incidate where to start parsing, when to stop parsing etc. This is a very powerful way to manage converting text to fields.

It was complex system to set up but it supports everything from the simplest to the most complex so I use it for everything in preference to the Access spec system. Moreover it supports imports to SQL server too.

I most cases I don't actually import the values to tables but load them into fabricated ADO recordsets for display in a form or report or export to Excel workbooks. (Where I work, all the reports coming from the Unix system are stored as text files and staff need them more conveniently presented.)

The recordset field specifications and value conversion information is also held in tables.

 

[jhartford]

DOS commands and batch files can be run from VBA with the Shell command.

The FOR command is just one of many ways to deal with importing text files that don't completely fit the Access import spec system. As a DOS devotee from the days when that is all we had, I used it until I learnt how to read and parse text files as Dave's option (b). Dave's option (a) is good if the structure of the file allows it.

I have some quite complex imports that include steps like identifying the report version from the header and processing accordingly. Some require reconnecting wrapped lines, adding extra fields, converting values etc. So I read the text file line by line, parse it and manipulate the values in code as they are written to a recordset.

Tables store the conditions which are applied to accept or reject lines with certain strings in particular locations, strings that incidate where to start parsing, when to stop parsing etc. This is a very powerful way to manage converting text to fields.

It was complex system to set up but it supports everything from the simplest to the most complex so I use it for everything in preference to the Access spec system. Moreover it supports imports to SQL server too.

I most cases I don't actually import the values to tables but load them into fabricated ADO recordsets for display in a form or report or export to Excel workbooks. (Where I work, all the reports coming from the Unix system are stored as text files and staff need them more conveniently presented.)

The recordset field specifications and value conversion information is also held in tables.

Thanks you very much for your time. This is exactly what I need. The book I have, Access 2007 Inside Out, doesn't talk about DOS command and Shell command. Do you have recommendation on the book that I can read?

Again, this is exactly what I need. Thank you very very much.

 

[Galaxiom]

The description I gave was for the way I do it like Dave's plan (b) after I learnt to read text files and parse them. Sorry I didn't make that clear.

I would use Dave's (Gemma-the-Husky) plan (a) if you can work it on your data. Reading files and parsing requires moderately complex coding knowledge. If you are not familar with DOS that could be trickty too.

Books about Access/VBA are unlikely to talk about DOS because they are interested in what can be done inside the program. No book about Access could ever cover everything about what can be done in Access (at least not in a book you could carry ).

I learn everything with Google. The main skill needed is how to formulate the question and track down what you need to know.

 

[jhartford]

Galaxiom,

I'm gong to give DOS a try after using For /F to skip the header lines. Now I have strugled on the following issue.

I have several the .txt file with some account info. First, I need to send info in one .txt file to deferent files, then parse lines with code to save info to diferent tables. e.g.

line 1:
009018582030000071524 2030000694003 ATL AAN2 300000000005 000000.0840 00000000.42AU WATS DISC

line 2:
009018582030000071524 2030000694003 ATL AAN3
400000424416225888 6941769400 5391176 00000005 000000.42

If the 5th token of the line starts with 40, this line of info will be sent to file1. f the 5th token of the line starts with 30, this line of info will be sent to file2.

Can I use nested FOR commands to make it happen and how?

Thank you very very much.

 

[vbaInet]

To be honest if you're going to start using DOS to read files you want to speak to your Systems Administrator as to the best method to use because this is what they do everyday. As Galaxiom mentioned it can get quite complex. He mentions the FOR command and there's also the Find command. Most Sys Admins would use a scripting language like Perl (for example) which handles file parsing much more effeciently. Now I didn't say you should stop pursuing your current method, just keep your options open and keep searching for optimised methods.

That said, re your last question, I would read the entire file to a temp table and split it in a query.

 

[Galaxiom]

DOS is great at parsing on delimiters but has nothing to split at a character position.

Edit: Actually, now i think about it, it does. See next page.

Import the file to Access with space delimiters and test the Left(fieldname,2) on the fifth field in insert queries. You can probably dump the headers at the same time because they won't have those numbers in that field.

 

[Galaxiom]

Most Sys Admins would use a scripting language like Perl (for example) which handles file parsing much more effeciently.

Efficiency isn't the issue with the FOR /F command. It is very, very fast.

It just isn't as versatile as other parsing techniques.

 

[vbaInet]

Just keeping the options open Galaxiom And I bet it won't beat Perl's parsing techniques.

 

[Galaxiom]

Just keeping the options open Galaxiom And I bet it won't beat Perl's parsing techniques.

In the same spirit.
DOS can use a regular expression to parse on position.

This command will extract lines where 40 appears at the 46th position.

findstr /X ^.............................................40.* input.txt >> output.txt

DOS is marvellous at doing stuff with one line commands. And I wouldn't bet my life on Perl winning a race against it, especially if you count the time taken to write the script.

 

[vbaInet]

In the same spirit.
DOS can use a regular expression to parse on position.

It tries... some regex.

DOS is marvellous at doing stuff with one line commands.

I'm sure it is.

And I wouldn't bet my life on Perl winning a race against it, especially if you count the time taken to write the script.

I wouldn't want you losing a life now would I. Unless you have 9 lives.

 

[Galaxiom]

Yes it tries ... and succeeds. I do admit that regex for DOS is rather primitive but it is effective.

I'm guessing you are somewhat younger than me. I started on computers when DOS was all we had. It is often looked down upon and considered obsolete by later generations who don't appreciate its power. I still use it for simple tasks because it is very quick to write and blazingly fast.

Would you like to write a Perl script to do the same task and have a race?

 

[vbaInet]

I must admit, DOS does do the job. My last post was just a bit of banter

I still use DOS when it's necessary but not as much as I do with Perl. Besides I'm not a a Sys Admin so I don't dabble in scripting much.

Now that would be interesting to see. If I've got some time I will PM you a script.