Oracle, CSV and Excel



  • Ok, normally I don't like to talk about my job, mostly because I can't but this is not covered so I guess I'm on the clear.

    The person that does the updates on our Oracle db did not come the other day so they ask me if I could do the modifications myself, I said fine, this should only take a couple of minutes of my time so it was not a big deal.   When I received the file and open it I noticed that it was a csv file (it wasn't the extention the file had), the email also said that they needed more data added and that it was in an excel file.

    So far so good, right?

    So I add the data, save the file and send it to the processing program and the program barf, badly.  I check the logs and the problem is that they added a header and excel fuck with it, I go and open it in notepad and fix it... I upload it again and it barf again, this time about one of the date columns ( the table has like 5 datetime columns) so I check it again and sure thing Excel fucked the date format, my bad for not expecting it, I go and make it look like the rest of the dates in the other date columns (why would it only fuck with the first column, I don't know yet).  I send it again, and again the program barfs...., I check it and it is in the same format as the other columns.  So.... I decided to look up the table in the db... for some weird ass reason that column is a varchar instead of a date... and in order to protect the data from imput they put some retarded, not standard date representation.

    I think I would love to talk to the person that designed this and depending on his answer throw stuff at him/her



  • I didn't know Oracle sold to North Korea.



  • @serguey123 said:

    Ok, normally I don't like to talk about my job, mostly because I can't but this is not covered so I guess I'm on the clear.

    The person that does the updates on our Oracle db did not come the other day so they ask me if I could do the modifications myself, I said fine, this should only take a couple of minutes of my time so it was not a big deal.   When I received the file and open it I noticed that it was a csv file (it wasn't the extention the file had), the email also said that they needed more data added and that it was in an excel file.

    So far so good, right?

    So I add the data, save the file and send it to the processing program and the program barf, badly.  I check the logs and the problem is that they added a header and excel fuck with it, I go and open it in notepad and fix it... I upload it again and it barf again, this time about one of the date columns ( the table has like 5 datetime columns) so I check it again and sure thing Excel fucked the date format, my bad for not expecting it, I go and make it look like the rest of the dates in the other date columns (why would it only fuck with the first column, I don't know yet).  I send it again, and again the program barfs...., I check it and it is in the same format as the other columns.  So.... I decided to look up the table in the db... for some weird ass reason that column is a varchar instead of a date... and in order to protect the data from imput they put some retarded, not standard date representation.

    I think I would love to talk to the person that designed this and depending on his answer throw stuff at him/her'

    In our programs we always using number fields to store dates. this solving two fold purpose. one of diff formating world-wide and also of sorting.



  • @blakeyrat said:

    I didn't know Oracle sold to North Korea.

    2 cent says poster is from Yogoslavia.



  • @blakeyrat said:

    I didn't know Oracle sold to North Korea.

    Sadly they sell it to your goverment.

    @Nagesh said:

    In our programs we always using number fields to store dates. this solving two fold purpose. one of diff formating world-wide and also of sorting.

    varchar is not a numeric type, also, dude, wtf?  This has been solved years ago, you have the date in the server in the date type and when the information is served, it is transformed to the local date setting.  The stuff that stump me the most is the lack of consistency and the fact that they created their own standard of date representaion.

    @Nagesh said:

    2 rupees says poster is from Yogoslavia.

    FTFY



  • CSV and Excel is always a bad combination. Mainly, as you said, due to Excel's overenthusiastic data conversion. You're better off forcing Excel to import the data as a text file; then you can specify types for each column (in particular, text columns can be imported as text and avoid Excel trying to turn them into numbers or dates). Or setting the appropriate columns to Text first, and then pasting in from a text editor. Or just not using Excel at all, though if half your data's in a .csv and the other half is in an .xls(x), that's harder to manage.
    @serguey123 said:

    I think I would love to talk to the person that [b]designed excreted[/b] this and [b]depending on his answer[/b] throw stuff at him/her

    FTFY.



  • @Scarlet Manuka said:

    @serguey123 said:
    I think I would love to talk to the person that designed excreted this and depending on his answer throw stuff at him/her
    FTFY.

    I'm trying to give the person that wrote this the  benefit of the doubt, but I'm finding it harder and harder to do it.  Because the column is not a date but a string, the sorting by that column  in the front end app fails miserably.  Also it seems they decided guid wasn't good enough for them so they decided to roll their own implementation, also the db design is shit, every time I check this stuff I find more piles of steaming fail.  The thing is that this software is used worldwide by at least 10 000 people.... argh

     



  • I talked to management and convince them that the whole csv+ excel stuff was shit so they decided to discontinue this and use webservices on asp.net.  I hope we are not trading a wtf for another but they asigned this to the same team that wrote and support the front end app which doesn't bode well.  At least my sanity is secured as I don't work on that.  Ohh, did I mention that they decide to use for a primary key a combination of the date plus a 4 digit number and if we insert more than 5000 records in a single date the primary key would not longer be unique?  Now guess what is the probability of this scenario happening.  But no, we are not allowed to touch the steaming pile of shit that is the db and the front app (it is also guilty of not letting us upgrade to Vista or Win7).  My hands are twiching.


Log in to reply