Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2009
    Posts
    35

    Unanswered: Copy data from one file to another, with edits (was "Help!")

    Hi, I have a text file and am trying to copy across all the data into a new text file. This sounds simple enough but I require some additional data to be added (three zero's). The file will look similar to as follows:

    TCS 12345678910
    Lin 010203040506
    TCS 234567891011
    Lin 0203040506

    and the output to look something like:

    TCS 12345670008910
    Lin 010203040506
    TCS 234567800091011
    Lin 0203040506

    Note : I only require the line's TCS to be updated with the "000". The example is very basic, just require some code to show how to do this.

    If anybody can help, it will be greatly appreciated.

    Ste

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try this:

    Code:
    Option Compare Text
    Option Explicit
    
    Function InFileToOutFile(InFile As String, OutFile As String)
    
        Dim intInHandle As Integer
        Dim intOutHandle As Integer
        Dim strInLine As String
        Dim strOutLine As String
        
        intInHandle = FreeFile
        Open InFile For Input As #intInHandle
        intOutHandle = FreeFile
        Open OutFile For Output As #intOutHandle
        Do Until EOF(intInHandle)
            Line Input #intInHandle, strInLine
            If Left(strInLine, 4) = "TCS " Then
                strOutLine = Left(strInLine, 11) & "000" & Mid(strInLine, 12)
            Else
                strOutLine = strInLine
            End If
            Print #intOutHandle, strOutLine
        Loop
        Close #intOutHandle
        Close #intInHandle
        
    End Function
    Have a nice day!

  3. #3
    Join Date
    Feb 2009
    Posts
    35
    much appreciated Sinndho!!!
    I am currently in the process of making it relevant to my file(s). Just a quick question(s)

    1. Where do you state were to put your input and output files i.e. input.txt and output.txt. Do I need to hard-code a path?

    2. If Left(strInLine, 4) = "TCS " Then
    strOutLine = Left(strInLine, 11) & "000" & Mid(strInLine, 12)

    I am assuming the "4" means to start after the TCS meaning if I hadn't put a space between the TCS and the 123....would that change to StrInLine, 3)?

    Thanks for your help!

    Ste

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome.

    The incoming and outcoming files path and name are passed as parameters to the function. Ex:
    Code:
    InFileToOutFile("C:\Documents and Settings\SinnDHo\My documents\Access\dbforums\OriginalFile.txt", "C:\Documents and Settings\SinnDHo\My documents\Access\dbforums\ProcessedFile.txt")
    You're assuming right: The Left function returns the nth left characters of a string whatever these characters can be, so a space counts as a character. More generally:

    - The Left function returns a string containing a specified number of characters from the left side of a string.
    - The Right function returns a string containing a specified number of characters from the right side of a string.
    - The Mid function returns a string containing a specified number of characters from a string.

    See: Functions (Visual Basic)

    Have a nice day!

  5. #5
    Join Date
    Feb 2009
    Posts
    35
    I am unable to track to where abouts of the output file is located thus not being able to see that it is picking up the input.

    I understand that the path is set on yours but don't know where to locate the file. Is there away I can directly find out?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's up to you to tell the function where the input file is and where to place the output file (they can be at different locations). If you do not specify a path, just a file name, then Access will search in it's default file location. To know where it is, open Access, in the Tools menu select Options then select the General tab. There you can find a text box specifying the default folder for the databases. This default folder is not only for databases it's also where Access will search for a file if you do not specify another folder.

    - If you do not specify a folder Access will search for the input file and will write the output file in it's default folder. Ex:
    Code:
    InFileToOutFile("MyInputFile.txt", "MyOutputFile.txt")
    (MyInputFile.txt is in the default Access folder and the output file MyOutputFile.txt will also be written in the default Access folder)

    - If you specify both an input and an output path, then Access will use them. Ex:
    Code:
    InFileToOutFile("C:\SomePath\MyInputFile.txt", "C:\AnotherPath\MyOutputFile.txt")
    (MyInputFile.txt is in the C:\SomePath folder and the output file MyOutputFile.txt will be written in the C:\AnotherPath folder)

    - You can also mix and specify only one path. Ex.
    Code:
    InFileToOutFile("MyInputFile.txt", "C:\AnotherPath\MyOutputFile.txt")
    (MyInputFile.txt is in the default Access folder and the output file MyOutputFile.txt will be written in the C:\AnotherPath folder) or:
    Code:
    InFileToOutFile("C:\SomePath\MyInputFile.txt", "MyOutputFile.txt")
    (MyInputFile.txt is in the C:\SomePath folder while MyOutputFile.txt will be written in the default Access folder).

    By the way, path and folder are synonyms.

    Have a nice day!

  7. #7
    Join Date
    Feb 2009
    Posts
    35
    Hi sorry to be a pain, but I still am a little uncertain. What I really need is to know exactly where to put the location of the path for I/O. Where you have :

    Function InFileToOutFile(InFile As String, OutFile As String)

    I have tried to add the line

    InFileToOutFile("MyInputFile.txt", "MyOutputFile.txt").

    This I thought looked wrong and of course it did not work.
    Also I am uncertain of what to change? Do I need to change anything in the code to make it relevant to my DB?

    This is in a module isn't it? I just don't seem to be able to get any kind of Input initialising or output creating. My I file is called TCLP_PASS1 and O is called Source.txt.

    Last but not least do I need to run this code through a macro or compile it?

    Thanks again!

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You do not have to change anything in the function itself; leave it as it is written in my first answer. Simply copy/paste the code into a module.

    It's when you call the function that you pass the parameters telling where the infile is located and where to write the output file.

    So, if you want to use the function from a macro, create a macro, in the Action column select RunCode and type:
    InFileToOutFile("infile.txt","outfile.txt") as the name of the function to be called.

    If you want to use it from a form, add:
    InFileToOutFile("infile.txt","outfile.txt") in the event section of the form's module where you want the action to take place.

    Have a look at the attached file to see how it works.

    Have a nice day.
    Attached Files Attached Files

  9. #9
    Join Date
    Feb 2009
    Posts
    35
    Thanks for that! I have finally got it working. I am fairly new to working with modules! Much appreciated!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •