SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 import from csv to Sql using bcp automated
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Nebuske
Starting Member

Germany
8 Posts

Posted - 03/10/2011 :  16:03:44  Show Profile  Reply with Quote
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

USA
15676 Posts

Posted - 03/10/2011 :  16:17:01  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

Nebuske
Starting Member

Germany
8 Posts

Posted - 03/10/2011 :  16:45:09  Show Profile  Reply with Quote
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


Edited by - Nebuske on 03/10/2011 16:46:38
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 03/10/2011 :  16:56:48  Show Profile  Visit robvolk's Homepage  Reply with Quote
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 ""
Go to Top of Page

Nebuske
Starting Member

Germany
8 Posts

Posted - 03/10/2011 :  17:08:56  Show Profile  Reply with Quote
got the same no differences,
btw the the csv file is saved as ansi code is this correct?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 03/10/2011 :  17:24:20  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

Nebuske
Starting Member

Germany
8 Posts

Posted - 03/10/2011 :  17:40:33  Show Profile  Reply with Quote
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
Go to Top of Page

Nebuske
Starting Member

Germany
8 Posts

Posted - 03/10/2011 :  17:42:01  Show Profile  Reply with Quote
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 ""

Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 03/10/2011 :  17:55:43  Show Profile  Visit robvolk's Homepage  Reply with Quote
So it's working for you now? Are all of the time values formatted that way?
Go to Top of Page

Nebuske
Starting Member

Germany
8 Posts

Posted - 03/10/2011 :  18:16:48  Show Profile  Reply with Quote
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 :)
Go to Top of Page

Nebuske
Starting Member

Germany
8 Posts

Posted - 03/11/2011 :  02:34:15  Show Profile  Reply with Quote
Hello, everything Works great now, got a mistake in my table.
Many thanks again

Case solved
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000