convert file before importing

[slimjen1]

All, I have code to import multiple text files into multiple tables in access 2003 There are about 15 tables It works but I have two problems. 1st I was converting the files from another source to text and placing them into a folder where I created import specification files. The problem is I need to automate the conversion part. How do I use VBA code to look at the file and add the extension .txt so that the program can find the file in the code. Here's part of my code. I didn't list all 15

'Import Text files into corresponding tables

DoCmd.TransferText acImportFixed, "PhoneNoProblems Import Specification", _
"PhoneNoProblems", "X:\DB_Working\0017_Errors\TextFiles\PhoneNoProblems.txt", True

DoCmd.TransferText acImportFixed, "StrangeZipcodes Import Specification", _
"StrangeZipcodes", "X:\DB_Working\0017_Errors\TextFiles\StrangeZipcodes.txt", True

In the textfiles folder, before the filename with .txt exist I have to add the .txt How do I add it automatically before the import?

2nd I need a cleanup routine to remove the additional lines in the text files like: list and sort which can appear randomily throughout the text files.
I hope I've explained this so you can help me
Thanks

 

[GolferGuy]

First, either add .txt to the file name when it is being created or written to disk in the conversion routine, or take .txt out of the code you have shown. Either should do the trick.
Your 2nd request for a cleanup routine. It would really help if you could show what the data in your text file looks like, and which of that data you need to "clean up". Please show both good lines and the lines that need to be cleaned up.

 

[slimjen1]

I ve added a sample file. In this file I need to automatically remove extra lines, and lines that begin with, in this example, "Sort" and the extra title headers like client, bucket ext. Some files instead of "Sort" has "List". Some have symbols like that little box. I need to also remove the last line that gives the number of records listed. How do I do this before importing?

Also, I do not create the files and they are some kind of batch from a Unix program. So I can't change the ext before it's created. Also, if I remove the .txt in the code, I can not or do not know how to create an import specification file to import and it will not import into the access table. Or am I missing a step that I can put into my code.
Thanks for the help

 

[GolferGuy]

Rather than trying to do anything with this file before importing it, I would import this file into a 1 field per record table. Then, I would delete any record that either has nothing in it (a blank line), or tha has something other than a number as the first text within the line. I would suggest deleting lines with code in a query like this: Len(Trim(fldWholeLine)) would be in the Field line of a query in design view, then the criteria line would have a 0. Of course this would be a Delete query. Another test for delete would be: IsNumeric(Left(Trim(fldWholeLine))) which would be in the Field line of a query in design view, and the criteria line would have False. And the last test for delete would be: Left(fldWholeLine, 1) in the Field line and in the Criteria line you would have <> " ". These three tests would have to be an OR condition between all three tests.

 

[slimjen1]

Thanks for your response. The only problem with this is that there are over 15 files. I was hoping to use code to clean this up.
Thanks

 

[GolferGuy]

Either do one file at a time, or load them all into the one field per record table and do the clean up of all the files at once. I really don't see the problem. Could you explain a little more what problem you see?

 

[slimjen1]

This import will take place on a weekly basis. There will be new files every week to import. I found a way to rename the file to take care of the first part of my problem. I setup an import specification for each file. I just wanted a way to cleanup each text file before importing
thanks

 

[boblarson]

I can come up with the code to clean it for import. It is actually very easy and I've done this before. I'll post back with it shortly.

 

[boblarson]

Here you go - this should at least get you started. You should be able to modify to fit your needs.

'---------------------------------------------------------------------------------------
' Procedure : CleanUpTextFile
' Author    : Bob Larson
' Date      : 3/12/2008
' Purpose   : This will clean your text file so it can be imported
'             You pass the path and name of the file to the function and
'             it will do the work.
'---------------------------------------------------------------------------------------
'
Public Function CleanUpTextFile(strTextFile As String)
    Dim strTextOut As String
    Dim varSplit As Variant
    Dim strTempLine As String
    Dim intcount As Integer
    Dim blnHeaders As Boolean
    Dim strRepline As String
    
    ' opens the input file
    Open strTextFile For Input As #1
    ' splits the input file to get everything but the extension
    varSplit = Split(strTextFile, ".")
    ' uses the file name and appends a one on it.  You can change this to a variable
    ' if you want to be able to loop through all of the files.
    
    strTextOut = varSplit(0) & "1.txt"
    ' opens the output file
    Open strTextOut For Output As #2
    ' works until it gets to the end of the input file
    Do Until EOF(1)
        ' reads the line from the input file
        Line Input #1, strTempLine

' replaces all spaces with empty strings to check for the numeric rows (if there are dates also involved
' you would need to modify to also replace the slashes "/"
strRepline = Replace(strTempLine, " ", "", , , vbTextCompare)
    ' bypasss the line if strRepline is an empty string
    If strRepline <> "" Then
        ' checks for the header row
        If InStr(1, strTempLine, "CLIENT...", vbTextCompare) > 0 Then
            ' if the flag is not set yet then it will copy the header row
            If blnHeaders = False Then
                ' prints the line
                Print #2, strTempLine
                ' sets the flag so it won't print the header row again
                blnHeaders = True
            End If
        Else
            ' If the templine is not an empty string then it checks to see if it is numeric
            ' and if it is then it will write that line
                If IsNumeric(strRepline) Then
                    ' prints the line if numeric
                    Print #2, strTempLine
                End If
        End If
End If

Loop
Close #1
Close #2
End Function

 

[slimjen1]

Thanks so much! I'll give this a try.