importing text file, bulk insert to populate table

Question

importing text file, bulk insert to populate table

smattiko83

SSC-Addicted

Points: 487
More actions
December 23, 2019 at 6:55 pm

#3710094

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,

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

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

Mike01 SSChampion Points: 11395 More actions · Answer 1

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: 162 More actions · Answer 2

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: 1004323 More actions · Answer 3

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.

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 SSC-Addicted Points: 487 More actions · Answer 4

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: 1004323 More actions · Answer 5

@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.

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: 1004323 More actions · Answer 6

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.

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 SSC-Addicted Points: 487 More actions · Answer 7

smattiko83

SSC-Addicted

Points: 487

December 27, 2019 at 4:46 pm

#3710793

What do you mean by real dates?