Opening text files with SQL using Access front end 3

[Eutychus]

My search did not produce a thread with the scenario I'm facing. I want to develop a new application that will enable the user to open and view (read only) text files that already are laid out with data in columns on some lines and data in sentences on other lines. There will be thousands of text files with the data in them to be viewed without loosing the columns, etc. The user will select a particular text file based on account number or customer name, and the system will go to that file and open it for viewing. I have an index file that points to the path of the text files. Some text files are in one folder (quantity is over 800,000) and others are in another folder (quantity is over 1,600,000). There are too many files to simply use Access which has a 2 GB limit. I have 12 GB of text files and can't split them up into multiple Access databases. I am fairly proficient with Access and am somewhat familiar with SQL Server, but do not have experience integrating the two.

I plan to use Access (2013 or 2016) as the front end and SQL Server standard (probably 2016) as the back end. Here are my questions:
1. How do I open a selected text file and retain the layout (columns, etc.) There are two basic text file layouts. One has 132 characters across and the other has 83 characters across. I can open these text files with Word, Wordpad, and Notepad++. For the 132 character files I can open with Word and Wordpad but have to change the document to legal size and landscape view to retain the column formatting. Notepad++ seems to retain the column layout or formatting automatically.
2. How do I use Access as the front end to accomplish the above. I just need to be able to pull the files up and view (not edit) them. What is the best connection type to use? Do I use an ODBC connection, an ADODB connection, or what? Can the tables in SQL simply be linked. With so many files to search for, I want a fairly decent response time and be able to pull up a document (text file) within, say, 5-10 seconds or faster).

I hope that gives enough information for you. I appreciate any help you can give. Thank you!

 

[gmmastros]

From a high level overview, it seems a bit ridiculous to store data in files when you have a database. When you were using Access (with the 2GB limit), it may have made sense, but if your using SQL Server Standard, the only limit to the size of the database is your limit on hard drive space. If you were to use the FREE version of SQL Server (SQL Express), there is a 10Gb limit, but with standard, there is no limit.

I recommend that you import all that data in to SQL Server, then just query the data like you would with any other data.

12 GB of data can be problematic for performance, but it's certainly possible. For example, I have a database that is approximately 60 GB. One table has 93,545,558 rows, and I can look up a single row in less than 10 milliseconds. This is only possible because of the various indexes I have on the table, but the important point is that it's possible. I should also mention that this database is hosted on a virtual machine, has 8GB ram, 3 virtual processors, AMD Phenom II 3.4 GHz processor. This hardware is approximately 8 years old.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Eutychus]

Thank you for your reply. As you can tell, this is new territory for me. I like your suggestion of importing the text files into SQL Server tables. If I understand you correctly, there would be one table for Folder A that currently has the 800,000 text files and one for Folder B with the 1,600,000 text files. Each text file would be stored in a separate record and field in the table.
1. When importing the text file data, can it be done in such a way that the file path/name is in an indexed field in the table? Or, is there a better way?
2. How is the column formatting/layout of the data in each text file retained? I would not want to pull up a field with the text file data in it and have it display in a different way.
3. How would I use the index file that I have? The index file is a text file that has a record for each data text file and that looks as follows:
For Folder A (800,000 records): REPORT NAME, SUBTITLE, DATE@@PATH
Example: ABC REPORT,Intro to ABC,31oct16@@C:\DOCS\123.txt

For Folder B (1,600,000 records): Customer Number,Customer Name,SSN,Document Type,Date@@PATH
Examples: 12345,John Doe,000-00-1000,STMT,31dec13@@C:\STATEMENTS\1234.txt
98765,Susan Smith,123-45-6789,STMT,31oct16@@C:\STATEMENTS\99.txt

The @@ delimiter means that the path following these characters is the path to the relative file and is not necessarily associated with the date field. If I import the text files into a SQL table field, the path would no longer be relevant except as maybe a way to identify each record, as I see it.

4. Can I then use Access on the front end for the user to pull up and display the data with proper report layout or formatting without having to somehow rebuild the layout?

Does this all seem doable without weeks and weeks of work? Again, I'm in new territory and don't want to mess this up.

Thank you once again!

 

[simian336]

The importing of the data in SQL Server is pretty easy but there are several ways to do it. Folder A would just be TableA and Folder B would just be Table B.

Those would just become linked tables in Access and would pretty much just look like normal tables.

The interesting thing is your text files. You would need to look at them and determine if the data in them lends themselves to being tables. It looks like they are statements so they may do better to remain documents. Then you can just have Access open them in the text file read of your choice using the filename and path in the table.

You might also chose to store that data in a table but that would be and additional step and would probably require a program to read all those files. But you can do that with Access/VBA as well.

Simi

 

[Eutychus]

Thank you, Simi! What is still confusing me is the text files. I have attached a sample text file with a sample statement to help you evaluate what I am trying to do. You said I would need to determine if the data lend themselves to being tables. Do you mean that each text file would become its own table? I would not think so, since that would mean 1,600,000 tables for Folder B. Or, do you mean all those text files could be imported into a single SQL Statement table, each text file having its own record in that table and each text file being stored in a single field. If I open the text file statement in Notepad++ it opens with the proper formatting. If I open it in Word and change the Orientation to landscape, it opens it with proper formatting.

You also mentioned this option: "It looks like they are statements so they may do better to remain documents. Then you can just have Access open them in the text file read of your choice using the filename and path in the table." Yes they are statements. If I go this route, would I just have the path to the file stored in the SQL Server table as a hyperlink? So, Table B, for example, would have the 1,600,000 links to the text files and those text files would be stored on the same machine in the path that is in the link in Table B. Then, through an Access front end, the user could click the link and it would open the file in say, Notepad++ or Word or some other app.

After looking at the sample file, and remembering I have to retain the formatting, what would you recommend?

 

[Andrzejek]

Your text file(s) – looks like they are some sort of financial/bank statements. And since you want to preserve their layout / formatting, I think you have a few options.

One, create an elaborate data base where you can keep all transactions, deposits, withdrawals, etc. pretty much ‘reverse engineer’ the data base used by your financial institution. Move all the data from your text files into that data base, and then (also reverse engineer, or would that be a ‘forward’ engineer?) re-create the statements that you have in your text files. A lot of work.

Two, have one table where you can keep the meta data about your text files (folder, name, dates, etc.) and for each record in that table you have another table where you can keep your data from your text files, one record per line of text (including all spacing) so you can easily recreate your data.

Three, you have just one table with fields like folder, name, dates, etc. plus a Memo field(?) or one long text field(?) where you can keep the entire text file. This way you can also recreate your data from your text files easily.

Four, keep what you have the way you have it now, in separate text files.

To show what you have you can simply do something like:

Dim lngRetVal As Long
Dim strFile As String

strFile = "C:\SomeFolder\MyTextFile.txt"
lngRetVal = Shell("NOTEPAD.EXE " & strFile, 1)

Show it in Notepad advising users: "For best viewing experience set your Notepad’s font to [tt]Courier New[/tt] or any other fixed-length font."


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Eutychus]

Thank you all for your input. I gleaned helpful info from each post, so I give you each credit for a great post. I am waiting till I can get some more test data and files and then I will try a couple of different options to see which works. The options that seem most feasible are options 3 and 4 in Andy's post and also suggested by Simi and one of them by George. It would be great if someone out there has done this sort of thing and could let me know what worked for them. In the meantime, I will try to let you know what worked for me after I can experiment. Thanks again!

 

[Eutychus]

Sorry for the delay, but here is what I ended up doing: The ID Info and the text file paths are in a SQL Server table linked/attached to the Access front end. The path points to where the text files are located including the text file name. The path field can be searched for and found by the ID Info and, when found, the path clicked. The OnClick event of the field is coded to use Shell to open Notepad++ and open the link (path). It works great!

Thanks everyone for the help!

 

[gbaughma]

Ummm.... and how are you making it read-only?

It seems a very insecure way to store files, especially those that contain Social Security Numbers and so forth.

I can only say that I hope mine isn't in there somewhere.


Just my $.02

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg