import from csv to Sql using bcp automated

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

import from csv to Sql using bcp automated

Author

Topic

Nebuske
Starting Member

8 Posts

Posted - 2011-03-10 : 16:03:44

Hello,
i am new to this SQL Stuff, and got a little Problem, my Boss wants me to import a csv file to an existing table per automatic batch script, maybe you guys can help me out.

CSV File is as following:

kostenstelle,kraftstoff,menge,beginn,ende
00702100;03;000126;13.08.2009 23:55;14.08.2009 23:55
00702100;10;000000;13.08.2009 23:55;14.08.2009 23:55

SqlExpress 2008 table:
abs_id, tankstelle, kostenstelle, kraftstoff, menge, beginn, ende
bigint, int, int, int, int, Datetime, Datetime

abs_id is primary key

cola.fmt

9.0
7
1 SQLCHAR 0 21 ";" 0 abs_id ""
2 SQLCHAR 0 12 ";" 0 tankstelle ""
3 SQLCHAR 0 8 ";" 3 kostenstelle ""
4 SQLCHAR 0 2 ";" 4 kraftstoff ""
5 SQLCHAR 0 6 ";" 5 menge ""
6 SQLCHAR 0 16 ";" 6 beginn ""
7 SQLCHAR 0 16 "\r\n" 7 ende ""

i tried the bcp command tool, but cant figure it out.

bcp tims.dbo.cola in hpv.csv /Usa /P*** /S**\sqlexpress /fcola.fmt

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1

with bcp tims.dbo.cola in hpv.csv /Usa /P*** /S**\sqlexpress /c same problem
i tried a few parameters but i cant get this to work, please help me :)

Thanks in advance

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-10 : 16:17:01

Your file header does not follow the format of the rest of the data. You can skip it with the -F parameter like so:

bcp tims.dbo.cola in hpv.csv /Usa /P*** /S**\sqlexpress /fcola.fmt /F2

If you still get "Unexpected EOF encountered in BCP data-file", it usually means you have blank lines (extra line feeds) in the file. You'll need to remove them before the file will import.

Nebuske
Starting Member

8 Posts

Posted - 2011-03-10 : 16:45:09

hello again, thanks for your quick response

i tried with the /F2 Parameter but it does not make any difference,

sorry i got another message along the line, forgot to post it on the previous one

SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file

I tried alot csv-file with header and without not making any difference.
with parameter /c i get only

Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file

i am absolute beginner in SQL, any help would be most welcome :)

thanks in advance

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-10 : 16:56:48

Try changing your format file to this:

9.0
7
1 SQLCHAR 0 21 "" 0 abs_id ""
2 SQLCHAR 0 12 "" 0 tankstelle ""
3 SQLCHAR 0 8 ";" 3 kostenstelle ""
4 SQLCHAR 0 2 ";" 4 kraftstoff ""
5 SQLCHAR 0 6 ";" 5 menge ""
6 SQLCHAR 0 16 ";" 6 beginn ""
7 SQLCHAR 0 16 "\r\n" 7 ende ""

Nebuske
Starting Member

8 Posts

Posted - 2011-03-10 : 17:08:56

got the same no differences,
btw the the csv file is saved as ansi code is this correct?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-10 : 17:24:20

Yes, ANSI format is fine. Was this file downloaded from a Unix/Linux system? If yes, it's probably a line-ending problem, LF instead of CR-LF. You'll either have to convert the line endings, or change the "\r\n" in the format file (I'm not sure this will work either)

It sounds like there's some bad data causing the "Invalid character value for cast specification", if you're still getting that error you'll have to scan the file for them. Things like non-numeric characters in a numeric column, etc.

Nebuske
Starting Member

8 Posts

Posted - 2011-03-10 : 17:40:33

ohhh i got a little bit going, now the real problem he says,

SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid time format

got this format in my csv

13.08.200923:55

the rest copied correct

Nebuske
Starting Member

8 Posts

Posted - 2011-03-10 : 17:42:01

difference in the fmt file i chacnged

9.0
7
1 SQLCHAR 0 0 "" 0 abs_id ""
2 SQLCHAR 0 0 "" 0 tankstelle ""
3 SQLCHAR 0 8 ";" 3 kostenstelle ""
4 SQLCHAR 0 2 ";" 4 kraftstoff ""
5 SQLCHAR 0 6 ";" 5 menge ""
6 SQLCHAR 0 16 ";" 6 beginn ""
7 SQLCHAR 0 16 "\r\n" 7 ende ""

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-10 : 17:55:43

So it's working for you now? Are all of the time values formatted that way?

Nebuske
Starting Member

8 Posts

Posted - 2011-03-10 : 18:16:48

heyy got this going thanks alot

used

bcp tims.dbo.cola in hpv.csv /Usa /P*** /S**\sqlexpress /fcola.fmt -R

-R command and it works

now i have only one problem i got this existing table, field is abs_id, in the real database this is a primary key with id, if i turn this on in my test db he says

Invalid character value for cast specification

whithout it it works great

thanks a lot so far :)

Nebuske
Starting Member

8 Posts

Posted - 2011-03-11 : 02:34:15

Hello, everything Works great now, got a mistake in my table.
Many thanks again

Case solved

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources