Import data into SQL database from txt file

Question

I'm trying to import some data from a text file into an SQL database in my Visual Studio project. All the columns have imported correctly apart from the date column. I used the following command to import the data:

BULK INSERT T2 FROM 'c:\Temp\Data.txt' WITH (FIELDTERMINATOR = ',')

In the text file i have dates like 02-02-12, 03-02-12, etc but in the database, all rows have been set to 01/01/1900. I thought this might have occured because the date formats are different in the text file comparing to the SQL database, does anyone know how i could import my dates into the database?

Thanks

BULK INSERT is pretty fussy about dates, what happens if you change the date format to mm/dd/yyyy (use / instead if - to separate date portions)? That should work, but how feasibile is it to update your data file? — Sparky
– Sparky, Commented Jan 18, 2012 at 21:54
@Sparky The data is within a text file, and i would have to change over 2000 lines manually :-( — Blob
– Blob, Commented Jan 18, 2012 at 22:02
well, you have csv data... open it as a csv file in excel and then format the dates in the format you need... — maneesha
– maneesha, Commented Jan 18, 2012 at 22:29
Is this a one time INSERT? Like Maneesha, if so, you could open it in Excel and change the date format using Excel formulas. If this is an on-going import though, that solution won't work... — Sparky
– Sparky, Commented Jan 18, 2012 at 22:34

wmz · Accepted Answer · 2012-01-19 00:14:07Z

1

I run into the same problem using your example in 2008r2. No error but all set to 1900-01-01. Looks it's a quirk in bcp when used to populate DATE field. It behaves this way only for DATE column type

So if you use Date type, change it to Datetime or smalldatetime (both worked for me) and see if it helps. Remember to set dateformat so you have days, months and years properly read. If it is day-month-year (in text file) it should be:

set dateformat dmy
BULK INSERT T2 FROM 'c:\Temp\Data.txt' WITH (FIELDTERMINATOR = ',')

I was able to find one report on it, but no definitive answer why this happens: similar case reported here

answered Jan 19, 2012 at 0:14

wmz

3,6851 gold badge16 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

wmz Over a year ago

found it as active bug in MS database: link

Blob Over a year ago

That command worked perfectly, however now i am wondering, how do i tell SQL to miss a column to import? e.g. i have 6 columns of data separated by commas, how do i only import columns 1,2,4 and 5?

wmz Over a year ago

@user1091114 You would have to use format file. See this link

Collectives™ on Stack Overflow

Import data into SQL database from txt file

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related