Results 1 to 10 of 10
  1. #1
    Join Date
    May 2004
    Posts
    38

    Exclamation Unanswered: different delimiters in txt file

    Does anyone know how to get external data from a txt file into a access table when the txt file has different delimiters in it i.e. my file has , . spaces ( ) / etc splitting a line up to mean separate fields however I cannot change these within the actual text file as there are over thousand lines and the file will constantly be updated with new lines of the same format.

    Cheers

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    import filter/specification?

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Can you give an example? It can be done with code. But there needs to be 'rules' for splitting a record in the file. For example, if the first field is delimited by a comma and the second field is always delimited by period. Or something like that then code can be written for that.

    Another possibility is... If you link to the file with a comma as the delimiter (may be another delimiter) are the rest of the fields subfields of those fields. An example would be:

    Field1,Field2;Field3/Field4,Field5%Field6

    If you linked to this with a comma as the delimiter you would have the following fields:

    Field1
    Field2;Field3/Field4
    Field5%Field6

    Is so, then you can link to the file and then use a query to split the fields even more.

  4. #4
    Join Date
    May 2004
    Posts
    38
    I have tried import specification within External data from the file menu, however you can only specify one delimiter for all the fields

    A typical line of my file looks like this

    11,C,52.96,-1.17,03-09-2004,19:00:01 Pass->Fail Datas(ROUND,10,30,6,8,39564.482125,AEND=8333,36,-220,393.7375,BEND=8348,44,-55)

    The field will be as follow

    Field1,Field2,Field3.Field4,Field5,Field6,,Field7 Field8 Field9(Field10,Field11,Field12,Field13,Field14,Fie ld16.Field17,Field18,Field19,Field20,Field21,Field 23,Field24,Field25)

    As you can see there are more than just a comma as a delimiter and I am at a lost how to use more than one when importing

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in which case, as D Kunkle says, you are going to need to write VB function to parse the record defining your own criteria

    you will have to open the file, then sequentially scan the records in the file
    parse the records for you predetermined separators
    and then transfer the records to the relevant table(s)

    HTH

  6. #6
    Join Date
    May 2004
    Posts
    38
    Can someone direct me with regard to opening a file and then parsing the file with the delimiters and then sending this to a table

  7. #7
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Seeing as your problem lies within field7 thru field10 you can "simply" import it using a comma seperated file. Then use an update query to seperate out the now combined field7 into the new fields you need.

    I think this would be easiest if your not "into" VBA

    Regards

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    to go down the VBA route
    you need to select / specirfy the file - up to you how yiou do that

    Open tbFileName For Input As #1 'opef the file for reading
    While Not EOF(1) 'iterate through the file till eof reached

    Line Input #1, strLine 'read a line from the file

    ..... 'find the next character you are searching for (using instr)
    ......'split that string off using left$ or mid$ (your choice)
    'the implementation of the split could be a function eg Columnx=ParseText(strline,startat,searchfor)

    ......'write to table using .add & .update methods of your ADO/DAO object

    wend 'continue loop processing.....
    cloase #1 ' release the resources attached to the file

  9. #9
    Join Date
    May 2004
    Posts
    38
    I have tried the vb putting getting errors staiting the file is already open, this ispossibly because I am changing the file extension from one to a txt file. I have tried the update query it all works fine untill i turn it into a update query - when i do this i get an invalid naming error. I know you can seperate all the deliminaters within Excel giving me all the fields I want, and I just want access to do that but the import in access only allows for one deliminator

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you have succesfully imported this file into excel, and are unable to do write you own access import filter, why don't you save the excel data, and then import / link that into your Access app?

    That way round its under your contol. Its not the most elegant soluiton, but if it gets your data in who cares? you may need to reconsider if this is an operaton you need to do frequently but it will get you going at present. If this is a frequent operation then write a fulter, its not a major task, approx 0.5 to 1 days max I reckon.

Posting Permissions

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