importing text file, bulk insert to populate table

  • smattiko83

    Old Hand

    Points: 351

    I have to import a text file to build a table and all of the date fields currently are in integer form. It comes in three different ways. The first two are like '20191223' or '0' if there is no date. The third way in the text file is like '2170915', missing the second character. The current script that is used has the below code to import those date fields . Is there a way I can modify the import script to import the field as a date, add either a '9' or '0' to missing second character and have the '0' be blank? Thanks.

    [admdat] [int] NULL,

    [bthdat] [int] NULL,

  • Mike01

    SSChampion

    Points: 11116

    One approach would be to load the data to a staging table first, then you can manipulate to put into the main table.  You can have the datatype be a varchar in the staging table so it will load, then massage it on the insert.

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • blakemcneill

    SSC Enthusiast

    Points: 152

    You may also want to look into creating an SSIS package since it's job is to extract, transform, and load (ETL) data. I'm assuming you've already looked into this but I would definitely push back on who or what is creating that file and get those inconsistencies fixed.

  • Jeff Moden

    SSC Guru

    Points: 995976

    smattiko83 wrote:

    I have to import a text file to build a table and all of the date fields currently are in integer form. It comes in three different ways. The first two are like '20191223' or '0' if there is no date. The third way in the text file is like '2170915', missing the second character. The current script that is used has the below code to import those date fields . Is there a way I can modify the import script to import the field as a date, add either a '9' or '0' to missing second character and have the '0' be blank? Thanks.

    [admdat] [int] NULL,

    [bthdat] [int] NULL,

    You're asking for trouble here.  In order for "0" to be converted to blank, the whole column is going to have to be character based.  For way too many reasons to list here, that's a fabulously bad idea.  The dates should be converted either to the DATETIME, or DATE datatype.

    As Mike suggested, you want to load the data into a staging table (a really good idea even if  the data is supposedly perfect because it's frequently/usually not) and then do your translations/modifications from there.  I also suggest that your "0" date be converted to a NULL so that it actually works for DATETIME or DATE.

    Seriously... storing dates as integers or text in SQL Server will do nothing but lead to an incredible world of hurt.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • smattiko83

    Old Hand

    Points: 351

    Thanks. Yeah it is really bad data and no one here cared about the data types. They thought making dates integers would fix the y2k issues that never happened  in 99/00 if that tells you how long they've been doing it this way.

    I'm not sure about the SSIS.  The database is not on a network either so the text files are placed on an external drive once received then physically walked to a room that connects to the server that is not online. The text files are then placed in a folder and the bulk insert script is run in ssms.

  • Jeff Moden

    SSC Guru

    Points: 995976

    @smattiko83,

    Do you also want the dates to be validated as "real" dates?

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • Jeff Moden

    SSC Guru

    Points: 995976

    Jeff Moden wrote:

    @smattiko83,

    Do you also want the dates to be validated as "real" dates?

    I'm asking this question because the translations you ask for are fairly trivial and I'm trying to generate some POP code for you.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • smattiko83

    Old Hand

    Points: 351

    What do you mean by real dates?

Viewing 8 posts - 1 through 8 (of 8 total)

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