Results 1 to 3 of 3
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    date/Time Nulls (97-->)

    Not necessarily an Excel question but as Excel was the solution it can move to the server forum if so required.

    I am undertaking a project at the moment that requires data to be extracted on a nightly basis from a very old legacy system to a SQL database. The legacy system is only able to provide data extracts in txt or csv format. One of the fields of the csv extract which contains completion dates and therefore the extract may have null values in the field or may have a data in D41 format. This extract is clean.

    Problem arises when I try to upload the recordset to SQL server via Enterprise Manager. This specific field has properties of Date/Time and is Nullable. If I send the csv formatted extract up to the table it bugs out stating that it sees a dbString when the SQL server says it is expecting a dbDate. So I got ANGRY and started throwing teddy in the corner and tried anything and everything at it and finally converted the csv file into Excel, Bingo, loaded correctly.

    Question: What is the difference between Excel format being allowed to upload as opposed to csv in SQL server?
    Jerry

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

    Re: date/Time Nulls (97-->)

    Without seeing the actual files it's hard to say, but my guess would be that Excel formatted the column as a date, even the blank cells (nulls), allowing SQL Server to recognize the blank cells as null dates. Csv files are just plain text files, they carry no formatting information at all.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: date/Time Nulls (97-->)

    Hi Hans

    Yes, I was looking at the csv in note pad and it has a lot of line field characters at the end of each record and a End of File character at the end Hex (4) and Hex(0A) respectively. Fields with no data are obviously shown as "".

    Excel must be "communicating" for want of a better word with SQL server telling it is in Date/Time format, one of those little mysteries. <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> It has caused a bit of a punch up between me and the consultants as I proved them wrong use the pragmatic approach
    Jerry

Posting Permissions

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