How do I import a text file into Microsoft Access 2000... into different tables?

[diasdaman]

I know how to import text files, but in this case I need to import a text file line by line on an on-the-fly basis, such that the Access will look at the first two digits of a line, and then import the rest of the line based on a given specification into an appropriate table.

I'm not that familiar with VBA, but I can pick it up if I need to. But first I want to know if what I want to do is even possible.

[janders468]

I've had to do this before and there are certainly many ways to do it so this may not be the best. There are really two routes I see with this both will require some coding however. One is to parse the lines of text yourself using code, and two is to separate out the file(s) (based on the two digit code that indicates the record structure) into temporary text files and use predefined import specifications. It probably depends on the volume of data you have. The import specs are going to be faster than writing your own parsing mechanism but it also requires a lot more overhead to maintain separate files and then delete them. It also depends on how automated it needs to be, for instance it would be very easy to write code that takes a text file or set of text files and separates them out into text files with like records and then go back and manually load those, it will be much more elaborate to say, do everything from a click of a button. Let me know what you think of those ideas and we can start walking through code to do it. If you would like.

[diasdaman]

I've had to do this before and there are certainly many ways to do it so this may not be the best. There are really two routes I see with this both will require some coding however. One is to parse the lines of text yourself using code, and two is to separate out the file(s) (based on the two digit code that indicates the record structure) into temporary text files and use predefined import specifications. It probably depends on the volume of data you have. The import specs are going to be faster than writing your own parsing mechanism but it also requires a lot more overhead to maintain separate files and then delete them. It also depends on how automated it needs to be, for instance it would be very easy to write code that takes a text file or set of text files and separates them out into text files with like records and then go back and manually load those, it will be much more elaborate to say, do everything from a click of a button. Let me know what you think of those ideas and we can start walking through code to do it. If you would like.

I suppose I'd prefer the click of a button approach, but I'm not dealing with massive amounts of information. I think a typical file might have 120 records, but they go into maybe 10 different tables at the most. So as long as everything can be contained in a folder and deleted promptly, I think my client would be ok with manually loading them.

Just a word about my skill level: I know how to create specifications and macros to use them, and I know how to write the necessary code in other languages (PHP being my most proficient), but not in VBA.

Thanks so much for any help.

[NeoPa]

Import the file into a single temporary table (after clearing any previous data from the table of course).

Depending on your specific requirements (unspecified for some reason) run queries that select specific records to be appended to each table where you want the data to end up.

This can be as sophisticated or simple as you're comfortable with.

[diasdaman]

Import the file into a single temporary table (after clearing any previous data from the table of course).

Depending on your specific requirements (unspecified for some reason) run queries that select specific records to be appended to each table where you want the data to end up.

This can be as sophisticated or simple as you're comfortable with.

Well, as I mentioned, different records have different specifications (field structures). If they all had the same fields and could therefore be imported into one table, then I don't see what the point of moving them to different tables would be.

Perhaps the only thing I may have left out is that I'm dealing with fixed width data, not delimited. However, each row / record in the text file has a different structure.

[NeoPa]

Well, as I mentioned, different records have different specifications (field structures). If they all had the same fields and could therefore be imported into one table, then I don't see what the point of moving them to different tables would be.

It might be interesting for you to show where that was mentioned. It's certainly not visible in this thread.

Regardless of that blatant inaccuracy, if I approached each problem by trying to guess WHY a poster asked a question every time, I would have little more sanity left than ... well I won't go on.

Perhaps the only thing I may have left out is that I'm dealing with fixed width data, not delimited. However, each row / record in the text file has a different structure.

Yes - that was another of the (quite important) points you forget to mention.

Anyway, to clarify things somewhat I'll explain in long-hand.

You cannot do a bulk-import into separate tables depending on the contents of the data.

You have two choices as far as I can see (and which you decide to choose depends heavily on information you have, but which has not yet been shared in this thread) :

1. Parse the files outside of Access (possibly using Access VBA but there's nothing to recommend that above any other means of processing raw files), somehow sorting them into different files depending on your actual requirements. Load them into Access tables from the separated files.
2. Bulk load all the lines into an Access table. The layout of this table would obviously have to be non-specific enough to handle any data that is likely to appear (If this means specifying a table containing a single long text field then so be it). Using SQL APPEND queries, or even Recordset processing VBA code if you prefer, apply the data, suitably re-created from the basic text, into the relevant eventual tables.

[janders468]

Hi diasdaman I've been swamped at work so slow in coming on the code I wrote this code up as a way to separate the files into different files based on the leading two characters of each line:

Sub CategorizeByCriteria(Criteria As String, FileFrom As String, FileTo As String)
    Dim intFileOne As Integer, intFileTwo As Integer, lngCounter As Long
    Dim strBuffer As String
    Dim strRecords() As String
    intFileOne = FreeFile
    Open FileFrom For Input As #intFileOne
    intFileTwo = FreeFile
    Open FileTo For Append As #intFileTwo
    strBuffer = Input(LOF(intFileOne), #intFileOne)
    strRecords = Split(strBuffer, vbCrLf)
    For lngCounter = LBound(strRecords) To UBound(strRecords)
        If Mid(strRecords(lngCounter), 1, 2) = Criteria Then
            Print #intFileTwo, strRecords(lngCounter)
        End If
    Next lngCounter
    Close intFileOne
    Close intFileTwo
End Sub
 

You would then call this like the following:

CategorizeByCriteria "01","C:/largefile.txt","c:/newfile.txt"
 

This would put every record that begins with "01" from "c:/Largefile.txt" in "c:/newfile.txt" It will create the file if it does not exist. You can then use predefined import specs to import the different files. Let me know about any code solutions (such as importing each of these files) you would like to see. I don't want to paste more than necessary because I have a tendency to get very lost when code goes on and on, and assume that it wouldn't be too helpful at this point. The other thing I should mention is that this assumes that each record is on its own line, or that you want to view them on a line by line basis.

[NeoPa]

Janders468's suggestion would fall into category 1.

As he's been so helpful I would urge you to snap him up (thankfully) on his offer. Not everyone will be prepared to go to so much trouble on a single question.

PS @Janders468 - Nice work and helpful attitude :)

PPS You would need to call the procedure for each file you wanted created.

[diasdaman]

Janders468,

Thank you very much. That looks like exactly what I needed, and I think I know exactly what to do from here.