Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2006
    Posts
    5

    Unanswered: newbie question... How to remove NULLs in table

    I apologize if this has been posted/asked before... a search of the Forum for keyword "NULL" doesn't return any result (not even a 0 found).

    When I import an XLS file into SQL2000, everything goes fine, except that every column after my data has <NULL> in it. How do I prevent this from happening, or fix it?

    Thanks,

    Rich

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm assuming you used DTS

    Excel gets to be funky

    I usually try to make sure the file I am working with has no formatting. It may also be that your "data area" is defined to more than your column sizes...just go out and delete a whole bunch of columns to the left...this can also happen for rows at the bottom.

    What I like to do though (and this is true for DB2 or SQL Server) is to import the spreadsheet to excel, have an exact copy of the destination table in access, the have an access query "import" the source data to that table. Then I set up an export spec and then export it out to either a cscv file (for SQL) or txtx (for DB2).

    Hope that helps

    I don't like DTS very much...just too damn quirky
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    had the same problem saved excel as CSV and did import.

  4. #4
    Join Date
    Jun 2006
    Posts
    5
    Quote Originally Posted by rbackmann
    had the same problem saved excel as CSV and did import.
    I tried this too, to no avail... oddly enough, I cannot delete the NULL rows as it results in the following error:

    "Key column information is insufficient or incorrect. Too many rows were affected by update."


    I will try what poster #1 has suggested...

    Thanks,

    Rich

  5. #5
    Join Date
    Jun 2006
    Posts
    5
    Quote Originally Posted by RichardSantink
    I tried this too, to no avail... oddly enough, I cannot delete the NULL rows as it results in the following error:

    "Key column information is insufficient or incorrect. Too many rows were affected by update."


    I will try what poster #1 has suggested...

    Thanks,

    Rich
    Okay this is weird, but I exported the .XLS file as Unicode text (.txt). I then removed any trailing spaces and performed the import (using DTS wiz) without any problems...

    Thanks for all the ideas,

    Rich

  6. #6
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    The attached is something that I did in Access/VBA some time ago.

    This may do what you need?
    Attached Files Attached Files

  7. #7
    Join Date
    Jun 2006
    Posts
    5
    Quote Originally Posted by rbackmann
    The attached is something that I did in Access/VBA some time ago.

    This may do what you need?
    Thanks for posting this... Even though the Unicode text is working for me, I will give this a whirl...

    Thanks,


    Rich

Posting Permissions

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