Need to import 240 txt files from CD into tables-monthly 3

[Soundsmith]

Nothing I could find in the FAQs or forums quite describe this:

I receive a cd monthly which contains 240 fixed width, comma-and-quote delimited text files. The first row holds the field names, and the data structure may change from month-to-month.

I want to read the filenames into an array or table, then one-by-one import them into a database, ADHOC.

I think I would prefer to address the import wizard programmatically, if possible, as I can easily import one file with it, but 240 at one sitting is quite a chore!

Thanks for any help you can offer. David 'Dasher' Kempton
The Soundsmith

http://www.thesoundsmith.com

 

[young]

I did the same project before. The following code may help you.

InputDir = InputBox(InputMsg, "YOUR DATABASE NAME", "d:\YOUR TEXT FILE&quot
' Change the file extension on the next line for the
' type of file you want to import.
ImportFile = Dir(InputDir & "\*.TXT&quot

Do While Len(ImportFile) > 0
' DO WHATEVER YOU WANT HERE TO PROCESS FILE
ImportFile = Dir 'get next txt file

Loop

 

[Soundsmith]

Thanks, Young. That gets me the filename, and if nothing breaks, can continue to the end. I can append the names to a table for processing later.

But I still need a way to programatically control the Import Wizard to process the data. This is about 450 MB in text form, so I would rather avoid looping through line by line and parsing into a single record append.

David 'Dasher' Kempton
The Soundsmith

http://www.thesoundsmith.com

 

[young]

The code I gave you is not for line by line but file by file. You need code to import text file to Access table. The easier way is using a micro and the convert to VBA.

 

[p27br]

Hi

You need to include an transfertext command in the dir loop
DoCmd.TransferText acImportDelim, "YourSpecifications", "Import", "YourDir & Yourfilename
You need to save an import specification first.
What works for me is I create a temporary import table then I do a runquery that moves the data to a new table and deletes the import table. You'll just have to click a button and voila !

 

[Soundsmith]

Thanks to you both! Young, I used your technique to get the file names into a table, then I used TransferText (that was the function I was looking for) to import the data. But that's something I hadn't thought of, to create a macro first then convert to VBA. That could resolve a lot of my stupid beginner questions!

Compared to my FoxPro routine, which converts the CD in under six minutes, this is dog slow (about 40 minutes) but I'm going to try copying the CD to hard disk first and see if the total process runs faster... David 'Dasher' Kempton
The Soundsmith

http://www.thesoundsmith.com

 

[MichaelRed]

This may help within the overall process. It is (almost) blindingly fast in getting a file into memory and seperated into "logical" units. You would need to have both pre and post processes to use/compliment this function, such as the routine to 'gather' the file names (already shown above) and then to 'accept' the results from this and convert into your tables.

This (first) routine is just a 'test case' to illustrate how to call the function and to show the results:

Public Function basTstCSV2Array()

    Dim Idx As Long
    Dim Jdx As Long
    Dim Kdx As Long
    Dim Ldx As Long
    Dim FilNme As String
    Dim MyArray As Variant
    
    'Here, you need to change the file name (FilName] assignment to your favorite (CSV) text file
    FilNme = "C:\My Documents\MyCsvFile.Txt"

    MyArray = basCSV2Array(FilNme)
    
    Idx = UBound(MyArray, 1)
    Jdx = UBound(MyArray, 2)

    For Kdx = 0 To Idx
        For Ldx = 0 To Jdx
            Debug.Print MyArray(Kdx, Ldx);
        Next Ldx
        Debug.Print
    Next Kdx

End Function
[code]

This is the routine you may want to implement to retrieve the files and reduce them to a simple logical structure (array) 

[code]
'_________________________________________________________
Public Function basCSV2Array(fName As String, _
                             Optional RecSep As String = vbCrLf, _
                             Optional FldSep As String = ",") As Variant

    Dim Fil As Integer
    Dim NRecs As Long
    Dim NFlds As Long
    Dim Idx As Long
    Dim J As Long                               'indices
    Dim RawFile As String                       'Holds the entire contents of the file
    Dim RawSplit() As String                    'the file split up on a line per line basis
    Dim OneLine() As String                     'used to hold the tokens for one line
    Dim RptAry() As String                      '2d array. Holds X lines & N elements per line

    Fil = FreeFile                              'get the next free file number

    'This works for large files. I (Troy Williams)tried it
    'with a 50 meg file on a computer with 128 Mb of ram and it worked fine.
    'open the file and dump the contents into the rawfile variable

    Open fName For Binary As #Fil               'Open file
    RawFile = String$(LOF(Fil), 32)             'Create "empty" String of Length
    Get #Fil, 1, RawFile                        'Fill "Empty Str with File
    Close #Fil                                  'Close File

    'Get the Nunber of Records and Fields
    RawSplit = Split(RawFile, RecSep)           'Split the file up by lines
    NRecs = UBound(RawSplit) - 1                'Num Lines/Records in the file

    OneLine = Split(RawSplit(0), FldSep)        'Split the first line
    NFlds = UBound(OneLine)                     'Get the Number of Fields

    ReDim RptAry(0 To NRecs, 0 To NFlds)

    For Idx = 0 To NRecs
       OneLine = Split(RawSplit(Idx), FldSep)
       For Jdx = 0 To NFlds
          RptAry(Idx, Jdx) = OneLine(Jdx)
       Next Jdx
    Next Idx

    basCSV2Array = RptAry

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Soundsmith]

Michael, that's just beautiful! As I'm just learning the Access world, there are a number of commands I've never worked with, like UBound and the low-level stuff. I spend hours trying to find the 'magic word' for some function that I use daily in Fox, only to write clumsy work-arounds because I can't find it in Help. My old programming 'mentor' (here's the book, here's the password, have fun, don't bother me!) used to say the best way to learn code was to read somebody else's (works great except in C++!)

I won't be able to implement this idea immediately, because I'm actually working primarily on a more urgent project, but I've copied the code to another module in the import project, and will definitely try it as soon as time permits.

Big thanks. I really appreciate it.
David 'Dasher' Kempton
The Soundsmith

http://www.thesoundsmith.com