Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing excel files (97)

    Thanks for the help Hans in my last post.
    I want to import an excel file into a table in access this works fine but the only prob is that i get these blocks appearing in between the text and some of the category text has spaces in it which it cant for example 4 C must be 4C fully left justified. Would you know how to eliminate these blocks and spaces so i dont have to remove them manually because there is over 900 records!!
    Thanks in advance,
    Rob.
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing excel files (97)

    Does the Excel table contain line ends within the cells?

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Importing excel files (97)

    Chances are those square blocks are CRLF (Carriage Return and Line Feed) characters where someone decided they needed to have multiple lines in a cell in Excel. (It is also a remote possibility that either the worksheet is corrupt, or that someone inserted special symbols in it.) The simplest method I know of for getting rid of that kind of thing is to use the Find/Replace function to find and delete all of those and replace them with something like " - " before you try to import the data. Be aware that almost certainly all the fields will come in as text in Access, especially if you have multiple numbers in a cell.
    Wendell

  4. #4
    Star Lounger
    Join Date
    Oct 2003
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing excel files (97)

    Sorry im not quite sure what you mean about line ends. If you mean false stops then no they dont. Here is a view of excel before it goes into access maybe this will help
    Attached Images Attached Images

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing excel files (97)

    As you can see, the cell on the left contains multiple lines. It would be best to either split such cells into multiple cells, or to replace the line ends (the separations between the lines) by spaces. This is best done in Excel, as Wendell pointed out.

  6. #6
    Star Lounger
    Join Date
    Oct 2003
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing excel files (97)

    Im not quite sure how to replace line ends, if i want to get rid of spaces how exactly do i do this?? For that example i click on justify left but it still leaves a few spaces from the margin!!
    Many thx,
    Rob
    Attached Images Attached Images

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Importing excel files (97)

    You need to search for special characters - it depends on how they were put in as to what they are - so you may need to resort to specifying characters in hex. I would suggest you save the file as a csv file, and then use Word to do the editing. If it is as I suspect, they are actually "Line Feed" characters, which are encoded as x0A, but in Word it is easier to search for and replace the character using the special characters - a line feed is entered as ^l - I haven't actually tried this in Excel, so you might give that a whirl before you export it to text.
    Wendell

Posting Permissions

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