Page 1 of 4 123 ... LastLast
Results 1 to 15 of 48
  1. #1
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extracting correct data from text file (2000)

    Hi,

    I am having difficulty in extracing data from a text file , which l am importing into access as a delimited file.
    I only want to extract data which is a valid length.
    i.e 5 characters in length before the full stop and 8 characters in length after the full stop.
    When l try to extract data it extracts invalid as well as valid data.
    The example below shows data which is valid and invalid format and is stored in a .txt file.

    I would really appreciate any help in this matter, can the file be imported in an easier way and how can l just extract only the valid data from the file?

    extract from file:

    "de#08732.de072998" - invalid
    "de#08732.de096125" - invalid
    "gbcly.kellyg" - invalid
    "gbcly.lewisj" - invalid
    "gbcly.malickd" - invalid
    "gbcly.pearcem" - invalid
    "gbcly.pearcen" - invalid
    "gbcly.powneyj" - invalid
    "gbcly.afshan" - invalid
    "atv01.atv01001" - valid (needs to be extracted from file and into access).

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Extracting correct data from text file (2000)

    Again i would import this file into a 1 column table. Then you can extract the data based upon the length of the 2 parts.

    Why don't you send the text file.

  3. #3
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting correct data from text file (2000)

    Hello,

    thank you for your response.

    I have a few questions:

    1. How do l import the file into one column, if fields are sepereated by a full stop. I have try to use a delimited file but cannot do this as fields are seperated by a full stop.

    2. If l try to import as a fixed width width l don't know what settings to use, any ideas what settings l would use?

    3. How can l import the file automatically in a macro, is this possible?
    If so , how do l do this?

    4. I don't know the exact command on how to extract the data from the file, you nformed me l use a query using a mid command , but l am unsure how to use this command.

    I have attached a smaller version of the file as the main file is just large, l would appreciate any assistance you could provide in help in me with this problem.

    In the attached file only the last three rows are valid. The data which needs to be extracted from these rows is the data after the fullstop. i.e the 8 characters after the full stop (excluding the " quote marks)

    If you are able to resolve this issue could you possible include screen shots of how you resolved , this would be much appreciated.

    Justin

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Extracting correct data from text file (2000)

    Responses to questions:
    1. The text file contains 1 field per record that are surrounded by quotes, not separated by a full stop.
    2. Its a delimited file
    3. I will give you an example of doing this in VBA behind a form
    4. See answer to 3
    I will post the solution soon

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Extracting correct data from text file (2000)

    Here is the sample database i created. if you have any queried please don't hesitate to ask.

  6. #6
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting correct data from text file (2000)

    That appears to work fine, many thanks for that.

    Justin

  7. #7
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting correct data from text file (2000)

    Hi Pat,

    I have a question and that is l how can l get the coding you have gave me to go in a macro so it can be done automatically,also l need to put a blank field in before field 1(it can be called any name, in the given example it is called field 2), reason being when l import data from all the other files the required information is in field 1, as l need to append this data into another table.
    i.e. a blank column of data , see screenshot.

    Any help would be appreciated.

    Justin

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Extracting correct data from text file (2000)

    Firstly, i never use macros except in an extreme case, eg AutoExec. Macros can be run automatically if you specifiy it i the start line of Access or from a Form that is set to start automatically from the Start options. Seeing as you can run a form automatically, you can leave the solution as it already is.

    I don't understand your reasoning for introducing an extra field in the imported table (i presume) and why does it have to be the first field.

  9. #9
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting correct data from text file (2000)

    Hi,

    I do not need to insert a second column now, as l resolved this issue, all l require is the changes l need to make to the VBA code in order to extract the data from the following files:
    As the code you gave me to extract data from the file l attached works perfectly.

    Narrowband file
    ==============
    "atv01 ","atv01011",1,23426,64000,64000,23426,23426
    "atv01 ","atv01012",1,2635,45333,45333,2635,2635
    "atv01 ","atv01013",3,3088,52000,52000,1029,1679
    "atv01 ","atv01014",1,2214,50666,50666,2214,2214

    Ssl file
    ========
    "atv01 ","atv01011"
    "atv01 ","atv01012"
    "atv01 ","atv01013"
    "atv01 ","atv01016"

    Again the only data l require is the 8 digit characters from the file. i.e atv01016

    I tried to amend your VBA code but had a few problems.
    If you could help me out it would be much appreciated.


    Justin

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Extracting correct data from text file (2000)

    Do you wish to import the data from both files below, if so just replace the Mid(F1,...) with just F2 should do the trick. The field F2 holds the data you require, so there's no need to use Mid to extract the data from it.

  11. #11
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting correct data from text file (2000)

    that worked fine, thanks

  12. #12
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting correct data from text file (2000)

    Hi,

    I have the following details which need to be imported using the coding which you kindly
    gave me. I would appreciate it very much if you could tell me what amendments need
    to be made to the coding so that only eight characters after the first space in each row
    are read in.If there is not eight characters then the data is invalid and does not have to read in.

    See below for a file which l have commented with regards to what data is valid/invalid.
    Only valid data needs to be imported.

    chv01 chv04881 1 1 1 50666 50666 0:00:42 0:00:42 - valid
    de#02800 de102508 2 1 1 52000 52000 0:00:43 0:00:43 - valid
    gbnc0 gbtxxxx 1 1 1 9600 9600 0:01:16 0:01:16 - invalid
    gbnc0 gbv08354 1 1 1 9600 9600 0:00:27 0:00:27 - valid data
    gbnc0 gbxxxxxxx 1 1 1 9600 9600 0:00:08 0:00:08 - invalid
    gbnc0 -gbt20069 2 1 1 9600 9600 0:00:25 0:00:27 - invalid
    gbnc0 ??? 1 1 1 9600 9600 0:00:32 0:00:32 - invalid
    itncoss itnc7088 5 1 1 26400 26400 0:04:23 0:11:57 - valid

    Therefore the data which should be read in is:

    chv04881
    de102508
    gbv08354
    itnc7088


    Coding which was send to me previously for similiar problem:

    Dim sSql As String
    On Error Resume Next
    sSql = "DROP TABLE xxxxxxx"
    CurrentDb.Execute sSql, dbFailOnError
    On Error GoTo Err_CommandImport_Click
    DoCmd.TransferText acImportDelim, , "tbl Import", TextFile, False
    CurrentDb.Execute "DELETE * FROM [tbl Imported]", dbFailOnError
    sSql = "INSERT INTO [tbl Imported] (F1)"
    sSql = sSql & " SELECT Mid(F1,7) FROM [tbl Import]"
    sSql = sSql & " WHERE Mid(F1,6,1)='.' And Len(F1)=14"
    CurrentDb.Execute sSql, dbFailOnError


    I have attached a file containing the data.
    Any help would really be appreciated.

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Extracting correct data from text file (2000)

    sSql = "INSERT INTO [tbl Imported] (F1)"
    sSql = sSql & " SELECT F2 FROM [tbl Import]"
    sSql = sSql & " WHERE Len(F2)=8"

  14. #14
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting correct data from text file (2000)

    Thanks for that.

  15. #15
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting correct data from text file (2000)

    I appear to be getting an error, any ideas what could be causing this, see attachment.

    Any help would really be appreciated.

    Justin

Page 1 of 4 123 ... LastLast

Posting Permissions

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