That removes the row though. I want to keep the row but not have it included in the total.

Would this work to only have to hit run and it would import and create the table?

if object_id('DE_IDENTIFIED.dbo.test') is not null
drop table DE_IDENTIFIED.dbo.test

if object_id('DE_IDENTIFIED.dbo.PROVIDER_INFO_1') is not null
drop...

• This reply was modified 4 years, 7 months ago by  smattiko83.

Cool, it seems to work well. Two questions.

1. How do you know to use ROWTERMINATOR = '0x0a'? I've been thrown into creating tables, dba role and have seen others but...

create table de_identified.dbo.test

(recordtype varchar(3)

,record varchar(500)

)

BULK INSERT de_identified.dbo.test FROM 'C:\dident\PRV414WSW\PRV414SW.txt'

I get the following error:

Msg 4866, Level 16, State 1, Line 8

The bulk load failed. The column is too long in the data file for row 1, column 1. Verify...

Do I just replace the part he has the select and from with a bulk insert?

This is unedited other than deleting lines so it is small enough.

I've attached a sample text and the import specs. Thanks!

Thanks. I think they are the same when including spaces. They come from the government as a .dat file and they won't change so I'm stuck with these. I've added...

Thanks. Unfortunately I do not have ssis. Only SSMS.

Right now they are being imported into access. There is one spec for the rows that start with '1' and one spec for the rows that start with '2'. Then...

Do you mean the spec part? If so that is just a sample of the first few. I can post them all if needed.

• This reply was modified 4 years, 7 months ago by  smattiko83.

I have a date calendar table. I'm just not sure how to use it to find the weeks between a date range.

What do you mean by real dates?

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