Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import Text File - Create Table (A2000)

    I have a text doc, as attached, which I would like to import into a table.

    The doc has spaces between instead of comma's etc.
    This is making it difficult to import because access see's the file as fixed width, which it isn't.
    Some of the manufacturers ie FORD are one word where other manufacturers have two, ie ALFA ROMEO.
    With this problem, the data gets mis aligned into the fields.

    Is there a way to overcome this other than to manually insert comma's where I need them.
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Text File - Create Table (A2000)

    You could always bring the text file into Word and do Find and Replace.

    Find Alfa Romeo replace it with Alfa_Romeo

    Then replace all the spaces with a comma

    then find Alfa_Romeo and replace with Alfa Romeo.

    It's not automated, but it sure beats putting commas in by hand.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Text File - Create Table (A2000)

    Thanks, I'll try that.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Text File - Create Table (A2000)

    How do you want them broken up?

    For example, this line: LAND ROVER Discovery 96 To 97 Discovery 96 12

    How should it appear?

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Text File - Create Table (A2000)

    Hi Rupert (Long Time since we spoke)

    This particular entry should be:

    [LAND ROVER] [Discovery] [96 To 97] [Discovery]

    I realise I will have to edit some entries manually, but didn't feel like manually editing them all.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Text File - Create Table (A2000)

    Yes is being a while! You should be nearly finished <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    How about this line? ALFA ROMEO 33 33 4

    And are there any other lines that might need handling differently?

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Import Text File - Create Table (A2000)

    Based on the link, it appears that there is a master set of data for all of the vehicles. For example,

    [LAND ROVER] [Discovery] [96 To 97] [Discovery]

    I would suggest the following:

    In excel,
    1. import all of the data. Contactenate the fields into one cell to give you one big ugly string of data.
    2. Set up a named range that has two cells. The first cell is just like the ugly imported data or LAND ROVERDiscovery96 To 97Discovery. The second cell would be [LAND ROVER] [Discovery] [96 To 97] [Discovery]
    3. Sort the named range and imported data in ascending order.
    4. Use a VLookup command to compare the first ugly cell in the named range to the first ugly cell of the imported data and if matching, output the correct data or some other identifier. Copy the Vlookup to all of the cells to be checked. You could then use If statements to create the fields needed based on the identifier.
    - This should work very quickly. Post back if you have any questions.

    How many data fields are being evaluated? How many different sets of data are there?
    Regards,

    Gary
    (It's been a while!)

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Text File - Create Table (A2000)

    Ooh, steady on.

    <img src=/S/grin.gif border=0 alt=grin width=15 height=15>I've just got used to Access and now I've got to learn Excel !! ( Just Kidding) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I'll have a go at your suggestions and post back any problems.
    Thanks for your suggestion.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Text File - Create Table (A2000)

    Hows That!!!!
    Attached Files Attached Files

  10. #10
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Text File - Create Table (A2000)

    Thats as near to perfect as damn is to swearing.

    Need I say More.

    Thanks Rupert, you're a star. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/clever.gif border=0 alt=clever width=15 height=15>

  11. #11
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Text File - Create Table (A2000)

    <P ID="edit" class=small>(Edited by D Willett on 14-Jan-03 18:31. )</P>Nearly finished, I wish !!

    By the way.
    The data comes not far from you, Thatcham !!

    Thanks for helping.

  12. #12
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Text File - Create Table (A2000)

    BTW

    How did you do it for future reference.

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Text File - Create Table (A2000)

    Ignoring the puzzle (below), I think it's worthwhile telling you what I actually did as it's a trick I've learnt which can help you out in some awkward data conversion situations.

    All you do is copy and paste the text into a word document. Word is pretty clever, and if it detects that the text you are pasting into it is from an HTML source it formats it correctly for you in the word document, normally putting it in a table. Now you can select the table, copy it and past it into Excel.

    Once you have it in Excel, you select everything you want (including the headers) then press:
    Insert >>>
    Name >>>
    Define.
    Choose a meaningful name (no spaces). Save and close the spreadsheet. Now open Access and click on:
    Tables>>>
    Import>>>
    Find the spreadsheet you want to import, change the type of file to import to .XLS. This triggers a wizard. The wizard asks you various questions about the import. Click

  14. #14
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Text File - Create Table (A2000)

    Ignoring the puzzle (below), I think it's worthwhile telling you what I actually did as it's a trick I've learnt which can help you out in some awkward data conversion situations.

    All you do is copy and paste the text into a word document. Word is pretty clever, and if it detects that the text you are pasting into it is from an HTML source it formats it correctly for you in the word document, normally putting it in a table. Now you can select the table, copy it and past it into Excel.

    Once you have it in Excel, you select everything you want (including the headers) then press:
    Insert >>>
    Name >>>
    Define.
    Choose a meaningful name (no spaces). Save and close the spreadsheet. Now open Access and click on:
    Tables>>>
    Import>>>
    Find the spreadsheet you want to import, change the type of file to import to .XLS. This triggers a wizard. The wizard asks you various questions about the import. Click

  15. #15
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Text File - Create Table (A2000)

    No it's ok Rupert, no need to delete the attachment.
    It's universal data anyhow and I do speak to the owners of the site anyway.

    I suppose I could have asked them for a CSV or something but that would take the fun out of programming.

    The guy who set up part of the site created an estimating package (on-line).
    A company came in and bought the rights to the software. The programmer now lives in the Caribean somewhere !!

    His software was really simple using a treeview structure.

    Any way, back to work now, have a good day and watch those high winds today.

Posting Permissions

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