Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Feb 2004
    Posts
    99

    Unanswered: How the heck can I get this into an Access database

    I want to import the following type of file into an Access database but am struggling, all self-taught or self bumbled (delete as appropriate!!)

    I have a file attached which lists company data...

    What I want to do is to import into an Access database whereby....

    Each line that starts with an * is a new field

    and each record is broken out by the *01 E field.

    In the attached I would therefore have 2 records with 34 fields per record.

    Has anyone got any clue how I could start to approach this?

    Many thanks for your help.

    Jessica
    Attached Files Attached Files
    pachamama
    www.researchbank.co.uk
    Fortune Global 500 Databank

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think a vb function to import the data may be more appropriate

    the reason: you have a variable number of data sentances, and a variable content

    some of the data may need to be normalised

    I'd suggest the function read the text file then do whatver vlaidation you have to, and write to waht ever table(s) your physical design requires.

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Posts
    99
    Thanks Mark, beyoud my scope of understanding so I'll go away and scratch my head.
    pachamama
    www.researchbank.co.uk
    Fortune Global 500 Databank

  4. #4
    Join Date
    Feb 2005
    Posts
    333
    It looks like you're trying to reinvent XML. Where did you get your data file? If you have the time, do this. Make a new table in access that contains one record of your data. Save the table, blah,blah,blah. Rght click on the new table icon on the database manager and select export. Xport the table as XML. Once you see what is happening, you can probably do a find/replace to your existing data to add the XML tags.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    The source of the problem is the file format. Before you start writing all sorts of complex routines let's just rule one thing out: do you have any control over the file (or program outputting it)? If so, can you get it to output in some sort of standard format (e.g. CSV, XML) and if not - can you request this of the people that do have control?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2005
    Posts
    333
    The source of the problem is the file format. Before you start writing all sorts of complex routines let's just rule one thing out: do you have any control over the file (or program outputting it)? If so, can you get it to output in some sort of standard format (e.g. CSV, XML) and if not - can you request this of the people that do have control?
    That's what I said.

  7. #7
    Join Date
    Jul 2005
    Posts
    50
    According to your definition that, "*01 E" denotes the start of a new record, the second record has more fields than 34.

    Assuming you had a way to get in the same number of fields per record, in VBA you could open the text file for input, do an .AddNew on an .mdb table upon which you used the .OpenRecordset method, stuff each value into the corresponding field of the open recordset, then update the table (for the current record) with the .Update method.

    I can tell from the length of some of your field values, though, that you have other issues. The field types will have to be larger than Text Types on many of the fields. You will have to either create Memo fields or split the values in some other way if you want to stick to Text values.

    Code:
    Some code snippets you might use:
    
        ' To open a table for update in current database
        Set dbTextToMDB= CurrentDb
        Set rsData= dbTextToMDB.OpenRecordset("tb_ImportTextData") 
    
    and
    
       ' To open the text file for input
        Open sTextFileName For Input As #1
    
        Do Until EOF(1)
            rsData.AddNew
            For iLoop = 1 To 34
                 Line Input #1, sNextLine
                 ' You might want to remove the leading asterisk
                 ' of course, with your value lengths, you have to test 
                 ' each line for the asterisk, among other things
                 sNextLine = Trim(Right$(sNextLine, Len(sNextLine) - 1))
                 ' You can use ordinal numbers to cut down on your code
                rsData.Fields(iLoop).Value = Trim(sNextLine)
            Next iLoop
            rsData.Update
        Loop
    I'm doing this without testing in the IDE, so don't hold me to the exact code ... this gives you one concept of how you might approach it.

  8. #8
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3
    Also, it's not a very big file, you could just go through it and add commas, or semicolons for delimeters yourself. Just a thought.

  9. #9
    Join Date
    Jul 2005
    Posts
    50
    I agree if it is YOU who determines the output of the data, or you can request a particular format for the data (as in calling up the mainframe guys), ask them for an xml output. (Not some proprietary BS though.) Or ask for some other standard delimited format (comma or tab or tilde or whatever).

    This would TREMENDOUSLY simplify your problem. Then I'd just link or import the data using the existing Access wizard.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by campster
    That's what I said.
    http://www.dbforums.com/showpost.php...63&postcount=5


    To be fair - I did think I was talking about getting one step closer to the source of the problem whereby if the file was outputted in a non-proprietry format in the firstplace then there would be no need for any piddling about (vb, find replace etc etc).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2005
    Posts
    333
    To be fair - I did think I was talking about getting one step closer to the source of the problem whereby if the file was outputted in a non-proprietry format in the firstplace then there would be no need for any piddling about (vb, find replace etc etc).
    Yeah but if you're bored at work, piddling aint so bad.

  12. #12
    Join Date
    Feb 2004
    Posts
    99
    Thanks all for your valued replies, it's like waiting for a bus here, no replies for a while and then shed loads of advice.

    I guess I am going to have to walk before I can run... in answer to some of the questions

    1) I have no control over the database but if I could get it output into CSV I would be halfway there
    2) vb is way beyond my realms of understanding at the moment
    3) campster the XML idea sounds within my grasp, will give that a crack
    4) mlbuie, whoosh missed the concept, looks good on paper but have no idea how to approach what you suggest!!
    5) padraig, this is a sample file, the full monty is huge so manual editing would be too much hassle

    Thanks all, will scratch my head further for a while!!
    pachamama
    www.researchbank.co.uk
    Fortune Global 500 Databank

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I wouldn't discount writing a vba function (just) yet

    whether you are a novice or not, if you plan on using Access for more than its simple forms & report designers you are going to need to dip you toe into the waters that comprise VB/VBA. Having a specific user requirement is that impetus needed, and often a good starting point.

    its nowhere near as difficult as it first sounds, and if you are prepared to put some effort in there are plenty of people who will help.
    just my 2 1/2d
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    If you can get the output into CSV I would suggest you are about nine tenths of the way there. CSV is a standard information transportation format (like XML) - Access will understand it no problem. You can then import with a single line of VB code. Having said that, as Mark says, there plenty who will help t'otherwise
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Jul 2005
    Posts
    50
    Quote Originally Posted by pachamama
    ...
    4) mlbuie, whoosh missed the concept, looks good on paper but have no idea how to approach what you suggest!!
    ...
    Not that hard ... look at the code snippets carefully:

    - You specify the database you are going to work with (in this case I chose the current database ... the one open in the Access instance you'd be writing code in).

    - Instantiate a recordset based on the table you pre-created for your data's destination

    - Open the text file for input, loop through it's lines, and deposit each line into a corresponding field location within the recorded you have added with the .AddNew method.

    - Use the .Update method to add the completed record.

    - Repeat for each record

    If that concept is too dificult for you, you MIGHT be in the wrong buisness.

    If you don't understand how to do a thing, in this business you have to buckle down and figure it out. You should be able to figure out how to at least import text data from a file into an Access table from what I've given you ... you just have to take the time to try it.

    - Find code samples of Text File access and Access table updates.

    - Copy the code into MS Access Module and step thru it in debug mode (the trusty F8 function key). If the light bulb doesn't come on after that, pass the database maintenance job off to someone else.

    Just trying to save you some grief...

Posting Permissions

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