Post reply

Scientific number in varchar column

  • January 31, 2020 at 8:49 am

    #3721914

    it looks like this data is coming from an excel spreadsheet that is casting everything as text, hence the scientific numbers and the bad data

    maybe worth looking at adding a persisted computed column on the table

    "alter table x add case when isnumeric(A) then convert (a, float) else NULL END as MynewColumn PERSISITED"

    my syntax might be a bit wrong - not had coffee yet

    MVDBA

  • January 31, 2020 at 2:09 pm

    #3722013

    Jeff, that would be the best case scenario, but we get many clients that give to us as is and don't want to do more.  It is in excel and i was able to add a prefix of "Z^-ABC-" to all rows for that column.  I exported it to csv and then imported it to SQL server and that solved my problem.  I was then able to remove the prefix.

    Thanks all for trying to help me with this.

  • January 31, 2020 at 2:16 pm

    #3722016

    GrassHopper wrote:

    Jeff, that would be the best case scenario, but we get many clients that give to us as is and don't want to do more.  It is in excel and i was able to add a prefix of "Z^-ABC-" to all rows for that column.  I exported it to csv and then imported it to SQL server and that solved my problem.  I was then able to remove the prefix.

    Thanks all for trying to help me with this.

    sigh... my heart dies a little bit when we ignore the option of getting a provider of data to make it useable , I know they are often "paying customers", but the cost of dev time in building import solutions (or maintaining them) can outweigh the money the customer pays.

    GIGO - garbage in, garbage out - first rule of importing files

     

    MVDBA

  • January 31, 2020 at 3:04 pm

    #3722038

    MVDBA (Mike Vessey) wrote:

    sigh... my heart dies a little bit when we ignore the option of getting a provider of data to make it useable , I know they are often "paying customers", but the cost of dev time in building import solutions (or maintaining them) can outweigh the money the customer pays.

    GIGO - garbage in, garbage out - first rule of importing files

    I'm right with you on this. The fear of somehow offending the data provider by asking them to make what is often quite a small change seems to be present in many organisations. It frustrates the **** out of me.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • January 31, 2020 at 3:36 pm

    #3722057

    GrassHopper wrote:

    Jeff, that would be the best case scenario, but we get many clients that give to us as is and don't want to do more.  It is in excel and i was able to add a prefix of "Z^-ABC-" to all rows for that column.  I exported it to csv and then imported it to SQL server and that solved my problem.  I was then able to remove the prefix.

    Thanks all for trying to help me with this.

    So the data provider is sending you the Excel spreadsheet where the data is actually good and it's your export from Excel to a CSV that's messing it up?  We need to show you how to import the data directly from the spreadsheet as character based data.  Either that or how to convert the offending column to actual text for the export.

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 31, 2020 at 3:39 pm

    #3722059

    Phil Parkin wrote:

    MVDBA (Mike Vessey) wrote:

    sigh... my heart dies a little bit when we ignore the option of getting a provider of data to make it useable , I know they are often "paying customers", but the cost of dev time in building import solutions (or maintaining them) can outweigh the money the customer pays.

    GIGO - garbage in, garbage out - first rule of importing files

    I'm right with you on this. The fear of somehow offending the data provider by asking them to make what is often quite a small change seems to be present in many organisations. It frustrates the **** out of me.

    From what the OP stated, I'm thinking there's no problem with the original data.  I'm thinking it's actually a PEBCAC problem with how the OP is exporting the data from the Excel spreadsheet to the CSV file.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 31, 2020 at 3:40 pm

    #3722060

    JEFF Do NOT go all SSIS on me 🙂 we all know it's Bulk insert with a formatfile or openrowset

    MVDBA

  • January 31, 2020 at 3:42 pm

    #3722061

    Jeff Moden wrote:

    GrassHopper wrote:

    Jeff, that would be the best case scenario, but we get many clients that give to us as is and don't want to do more.  It is in excel and i was able to add a prefix of "Z^-ABC-" to all rows for that column.  I exported it to csv and then imported it to SQL server and that solved my problem.  I was then able to remove the prefix.

    Thanks all for trying to help me with this.

    So the data provider is sending you the Excel spreadsheet where the data is actually good and it's your export from Excel to a CSV that's messing it up?  We need to show you how to import the data directly from the spreadsheet as character based data.  Either that or how to convert the offending column to actual text for the export.

    By the time it gets into Excel, it may already be too late – because of Excel being 'helpful'. Trying to fix Excel data issues is sometimes as easy as unscrambling an egg.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • January 31, 2020 at 3:47 pm

    #3722065

    MVDBA (Mike Vessey) wrote:

    JEFF Do NOT go all SSIS on me 🙂 we all know it's Bulk insert with a formatfile or openrowset

    Jeff's blood pressure just went up a notch 🙂

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • January 31, 2020 at 3:52 pm

    #3722067

    PEBCAC problem with how the OP is exporting the data from the Excel spreadsheet to the CSV file.

    PBCAC - I have to use that phrase daily - multiple times - still none of my project managers have figured it out

    "it's a pbcac issue I might need 5 days to fix it"- I love pbcac!!! 🙂

    and jeff, stop being mean.. grasshopper can you post an attachement of maybe the first few rows of a typical import in the format that they send it to you... no modifications or format changes.

    one of us blundering idiots might give you a nice script that helps out (phil you aren't included in the idiot bit)

    MVDBA

  • January 31, 2020 at 3:56 pm

    #3722068

    Phil Parkin wrote:

    MVDBA (Mike Vessey) wrote:

    JEFF Do NOT go all SSIS on me 🙂 we all know it's Bulk insert with a formatfile or openrowset

    Jeff's blood pressure just went up a notch 🙂

    wait until Joe gets in on this

    I want to see how high we can get that pressure up - in the mean time , lets help this fella out

    MVDBA

  • January 31, 2020 at 4:10 pm

    #3722073

    MVDBA (Mike Vessey) wrote:

    Phil Parkin wrote:

    MVDBA (Mike Vessey) wrote:

    JEFF Do NOT go all SSIS on me 🙂 we all know it's Bulk insert with a formatfile or openrowset

    Jeff's blood pressure just went up a notch 🙂

    wait until Joe gets in on this

    I want to see how high we can get that pressure up - in the mean time , lets help this fella out

    If OP used punched cards he wouldn't be having all this trouble.

     

  • January 31, 2020 at 4:18 pm

    #3722076

    x wrote:

    MVDBA (Mike Vessey) wrote:

    Phil Parkin wrote:

    MVDBA (Mike Vessey) wrote:

    JEFF Do NOT go all SSIS on me 🙂 we all know it's Bulk insert with a formatfile or openrowset

    Jeff's blood pressure just went up a notch 🙂

    wait until Joe gets in on this

    I want to see how high we can get that pressure up - in the mean time , lets help this fella out

    If OP used punched cards he wouldn't be having all this trouble.

    It's ok - jeff reads punchcards like he is a blind man reading brail- he can do it. it's Kimball tags he fails with

    MVDBA

  • February 4, 2020 at 2:25 am

    #3722803

    GrassHopper wrote:

    Jeff, that would be the best case scenario, but we get many clients that give to us as is and don't want to do more.  It is in excel and i was able to add a prefix of "Z^-ABC-" to all rows for that column.  I exported it to csv and then imported it to SQL server and that solved my problem.  I was then able to remove the prefix.

    Thanks all for trying to help me with this.

    There's a way to read spreadsheets directly from T-SQL.  It will require the use of an additional "provider" from Microsoft (lovingly referred to as the "Ace Driver") and the use of OPENROWSET.  If you're allowed to install the driver and use OPENROWSET, let me know because I believe we can pull off a small miracle for you without you having to open spreadsheets and export them.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 4, 2020 at 2:41 am

    #3722805

    MVDBA (Mike Vessey) wrote:

    x wrote:

    MVDBA (Mike Vessey) wrote:

    Phil Parkin wrote:

    MVDBA (Mike Vessey) wrote:

    JEFF Do NOT go all SSIS on me 🙂 we all know it's Bulk insert with a formatfile or openrowset

    Jeff's blood pressure just went up a notch 🙂

    wait until Joe gets in on this

    I want to see how high we can get that pressure up - in the mean time , lets help this fella out

    If OP used punched cards he wouldn't be having all this trouble.

    It's ok - jeff reads punchcards like he is a blind man reading brail- he can do it. it's Kimball tags he fails with

    Heh... and I've found a lucrative side business... I sell the chad from the punchcards to the Broward County Canvassing Board in Florida. 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 16 through 30 (of 36 total)

You must be logged in to reply to this topic. Login to reply