Importing data from flat file to SQL Table

Question

Post reply

Importing data from flat file to SQL Table

Paul Corbin

Old Hand

Points: 301
More actions
May 24, 2004 at 4:34 pm

#82903

We have a comma delimited flat file that updates once every minute. I'd like to copy the data in this file into a SQL table. Can anyone tell me the best to do this?

Thanks.

Paul

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

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

jordanac Hall of Fame Points: 3653 More actions · Answer 1

Hi,

You can use a DTS package to import a text file. You can use the text file connection object.

If you are importing every minute you might get on ovelap of you trying to import while the file is being dumped. Maybe use a vb script to test the condition of the file......

Andy.

Paul Corbin Old Hand Points: 301 More actions · Answer 2

Andy.

The flat file will be updated about once per minute, but we will only bring the data into our SQL table about once a week.

Thanks for the suggestion about DTS. I'll take a look at that.

Paul

ThomasH SSCertifiable Points: 7437 More actions · Answer 3

Personally I'd use a T-SQL with BULK INSERT.

As for "testing" the file, check out this function I posted a while back with help from this forum... http://www.sqlservercentral.com/scripts/contributions/1028.asp

Once you understand the BITs, all the pieces come together

Paul Corbin Old Hand Points: 301 More actions · Answer 4

Thomas,

My preference is also to use T-SQL.

Thank you for directing me to your script and to the hint about using BULK INSERT. I've not used it before -- this is my first time importing from a flat file.

I'll let you know how it turns out.

Paul

ThomasH SSCertifiable Points: 7437 More actions · Answer 5

Here is a little snippet from some of my code... may help you get started...

Bulk Insert {TableName|ViewName} From '{FileName}'

With ( CODEPAGE = 'RAW',

DATAFILETYPE = 'char',

ROWTERMINATOR = '\n',

FIELDTERMINATOR = ',',

LASTROW = 0)

Once you understand the BITs, all the pieces come together

Paul Corbin Old Hand Points: 301 More actions · Answer 6

Thomas,

That looks like it is exactly what I'm looking for.

Thank you!!! That's a BIG help!

Paul