Reading from text file, export to DB

[QuackHead]

Alright, can someone draft up a quick example for me, I've never done this before...

I want to read a bunch of data from a text file (which came from a database). Then, I want to shove that data into an Access DB...

The text file is separated into fields by the | character (some call it the pipe for you unix hounds).

How would a read the file as a database?

Thanks for all your help

~Quack

*Just so you know, I am quite fine with regular databases. (3+ years of experience)

[Roy Sinclair]

Try setting up a ODBC DSN using the TEXT FILE driver, use the options to designate a custom delimiter and then read it like a database.

[QuackHead]

Thank you for your VAGUE answer.... you always answer like this.

I'd like some actual advice on how to do this. The text file will be dynamic, I wont be reading from the same file all the time. Therefore, I do not believe that ODBC is the way to go.

I know there's a way to do this, so can someone please give me some complete answers instead of just an open-ended one sentance shot?

Thanks

[oracleguy]

That was kind of rude. You say it was opened yet later you request advice, and thats what he gave you.

Roy already said everything you needed to know on how to solve the problem. Simply use the text file driver with ODBC. You can set it up to use | as the text delimiter and it will load it up into a recordset, just like reading from any other db. You then have your script, load the data into your access db.

If you can't get that to work, you'll have to use the FileSystemObject to read the files in blocks and load them into your db.

What is the difference if the text file keeps changing? That shouldn't matter. (Assuming of course the number of fields don't change.)

[Roy Sinclair]

You distinctly asked how to read the text file as a database and also stated you were quite fine with databases, I assumed that meant you'd also know what terms like ADO, ODBC and DSN meant.

There is a standard database driver available for use which will allow you to read that file into an ADO recordset object as if it were a database. If you use the ODBC Applet (formerly on the Control Panel but now hidden under Administrative Tools) you can create a System DSN to point to a text file. As a part of setting up such a DSN you can also define a custom character which is used to delimit the text file into individual data fields (the standard characters the driver expects are comma or tab).

Since you say the name of the file may vary a fixed DSN defined using the ODBC applet probably wouldn't be usable though it's quite possible you could make a DSN-less connection for that driver. Create a DSN though would allow you to hunt down the settings for the DSN which you could then use in creating a DSN-Less connection where you could set the file name to whatever file you're wanting to read.

Does that explain it any better? If you don't understand, feel free to ask for more detail in any area, but do so nicely next time or I may not answer you again.

[whammy]

If you just want to read a pipe-delimited text file, you can always use FileSystemObject.:

http://www.w3schools.com/asp/asp_ref_filesystem.asp

That link (with a lot of self-teaching!) has EVERYTHING you need to know about it. ;)

[whammy]

Quick example:

Open Access

Click on "new database"

go ahead and make it "db1.mdb" as the default

then click on "tables" in the standard view

right click on the "WHITE SPACE" there, and choose "import"

browse to your text file

specify "|" as the delimiter

import it

[QuackHead]

Just to clear some stuff up... sorry for the rudeness, but I keep getting answers like this from Roy... always vague, never completely answers the question... makes one wonder how he got so high on the post count... anyways, enough with that.

Secondly, I never said the text file was opened, and just for your information, I did ask for an EXAMPLE of how to do this, not just suggestions. All it would take is about 5-10 minutes to write the code. I'm not asking you to do my stuff for me, I'm just asking for an example of HOW to do what I want to do.

Also... I was assuming that I'd have to use the FileSystemObject to do this. The text file is dynamic in that it does not always have the same FILE NAME. Therefore, setting up a DSN wouldn't be a good solution. I know what all the terms mean (ODBC, DSN, ADO, etc.).

I never asked how to read the text file as a database, I simply asked how to read it (which leaves this open as a database, FSO, or any other method.)

All I wanted was specific answers... I think that if my post was read correctly, (and what I asked for was answered correctly), I wouldn't have been a little frustrated with the response I got.

If anyone can post an example of how to read a pipe-delimited text file, please do so.

Thank you to Whammy for the link, I'll be checking that out as well.

~Quack

[Roy Sinclair]

Yep, you certainly did ask for an example so you are definitely right on that count. I could go on with the "You said/I said" nonsense but that's not going to be productive.

I did a quick Google and found this excellent example you could easily alter to meet your needs:

https://www.microsoft.com/technet/treeview/default.asp?url=/technet/scriptcenter/scrguide/sas_log_ldpe.asp