Stored procedure for importing data from the flat pipe delimited text file into a table

Question

Post reply

Stored procedure for importing data from the flat pipe delimited text file into a table

Vikka

Hall of Fame

Points: 3715
More actions
January 30, 2008 at 7:40 pm

#202864

Does somebody has one? Or where can I get one?

Viewing 10 posts - 1 through 9 (of 9 total)

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

yann.larvor SSC Eights! Points: 911 More actions · Answer 1

http://technet.microsoft.com/en-us/library/ms188365.aspx

French Geek.
http://blog.developpez.com/ylarvor
I work my english 🙂

Vikka Hall of Fame Points: 3715 More actions · Answer 2

Thanks! It works.

Are you French? I am Russian 😛

yann.larvor SSC Eights! Points: 911 More actions · Answer 3

Yes, i'm living in nantes in britany.

French Geek.
http://blog.developpez.com/ylarvor
I work my english 🙂

Vikka Hall of Fame Points: 3715 More actions · Answer 4

Doesn't work. How to insert into myTable (id int primary key not null, name varchar(50), date datetime )

id | name | date

from the file C:\myFile.txt :

100| yann | 02/01/2008

200| vika | 02/01/2008

bulk insert myTable

from 'C:\myFile.txt'

with FIELDTERMINATOR = '|'

type mismatch or invalid character for the specified codepage) for row 1, column 1 (id).

Probably fields in the text file are char and in the table it's int. What to do in this case, convert just field somehow?

Vikka Hall of Fame Points: 3715 More actions · Answer 5

Vikka

Hall of Fame

Points: 3715

February 1, 2008 at 6:08 pm

#774504

Mom tought me some French but I cannot write

yann.larvor SSC Eights! Points: 911 More actions · Answer 6

this code :

bulk insert myTable from 'C:\file.txt' with (FIELDTERMINATOR = '|');

works on my serveur sql serveur 2005 sp2 french edition developper:)

French Geek.
http://blog.developpez.com/ylarvor
I work my english 🙂

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Do you need to exclude the column names from the import? Meaning - does the pipe delimited file have column names?

If so - then add the FIRSTROW=2 option into the WITH

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Vikka Hall of Fame Points: 3715 More actions · Answer 8

Yes it works if all fields in myTable are varchar. As soon as I make them int, datetime etc, I get type mismatch error when I bulkinsert from the text file. So what I do now is I have two tables myTable and myTable2. myTable is all varchar and myTable two has numeric, varchar and datetime fields.

I bulk insert into myTable. After that I

insert into myTable2 select * from myTable

and then I get good data where numbers and dates are in the correct format, not varchar.

It's ugly but it works. Thanks!

gregarobinson Right there with Babe Points: 736 More actions · Answer 9

Same her, I have to make all my fields varchar to get the bulk insert to work. Otherwise I get a type mismatch error too. I would prefer to not make all my fields varchar. Is there a work around for this?