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.
| Author |
Topic |
|
hutty
Starting Member
37 Posts |
 Posted - 2005-04-28 : 09:26:59
|
| I'm trying to load data to an sqlserver table from a comma delimeter file. I'm trying to use the FORMATFILE property. I've bcp out the format of the table so it can be used as the load format. However, when I used the file to import I get error "invalid collation name for source column 3. Here's what the data file looks like. Intelis,LOCK.E23100,3.00000,3.00000,3.00000,3.00000,3.00000,3.00000,3.00000 And here's the bcp out formatfile. I have tried various names for the collation column, but nothing seems to work. 8.0 14 1 SQLCHAR 0 20 "" 1 Entity SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 15 "" 2 Account SQL_Latin1_General_CP1_CI_AS 3 SQLDECIMAL 0 9 "" 3 Period "" 4 SQLNUMERIC 0 9 "" 4 Period "" 5 SQLDECIMAL 0 9 "" 5 Period 3 "" 6 SQLDECIMAL 0 9 "" 6 Period 4 "" 7 SQLDECIMAL 0 9 "" 7 Period 5 "" 8 SQLDECIMAL 0 9 "" 8 Period 6 "" 9 SQLNUMERIC 0 9 "" 9 Period 7 "" 10 SQLDECIMAL 0 9 "" 10 Period 8 "" 11 SQLDECIMAL 0 9 "" 11 Period 9 "" 12 SQLDECIMAL 0 9 "" 12 Period 10 "" 13 SQLDECIMAL 0 9 "" 13 Period 11 "" 14 SQLDECIMAL 0 9 "" 14 Period 12 "" "" |
|
|
mpetanovitch
Yak Posting Veteran
52 Posts |
Posted - 2005-04-28 : 13:29:27
|
hutty, there seems to be alot of issues with your format file.In my experience, these things are sensitive; you have to play with them to get them to work. Here is a sample of what might work based of the sample data you supplied.8.0 9 1 SQLCHAR 0 20 "," 1 Entity SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 15 "," 2 Account SQL_Latin1_General_CP1_CI_AS 3 SQLDECIMAL 0 9 "," 3 Period SQL_Latin1_General_CP1_CI_AS 4 SQLDECIMAL 0 9 "," 4 Period SQL_Latin1_General_CP1_CI_AS 5 SQLDECIMAL 0 9 "," 5 Period 3 SQL_Latin1_General_CP1_CI_AS 6 SQLDECIMAL 0 9 "," 6 Period 4 SQL_Latin1_General_CP1_CI_AS 7 SQLDECIMAL 0 9 "," 7 Period 5 SQL_Latin1_General_CP1_CI_AS 8 SQLDECIMAL 0 9 "," 8 Period 6 SQL_Latin1_General_CP1_CI_AS 9 SQLDECIMAL 0 9 "\r\n" 9 Period 7 SQL_Latin1_General_CP1_CI_AS Let me know if this helps.Mike Petanovitch |
 |
|
|
hutty
Starting Member
37 Posts |
Posted - 2005-04-28 : 16:04:35
|
| Thanks Mike. That seems to get me past that stage. I'm getting the error: "Server: Msg 8152, Level 16, State 9, Line 1String or binary data would be truncated.The statement has been terminated."FYI, your solution opened my eyes to what the problem was with the load file. You didn't include the 1 or 2 after Period. I ran your solution and got the error on Period 3. As it turns out there are no spaces Period and the number. Should read Period1, etc. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-04-28 : 17:39:33
|
| Why are you using a format file?For export use a query or view.For import use a view or staging table.Only use a format file if you really need to as they usually add overhead to development, maintenance and administration.The datatype refers to the file not the table - for this it is all sqlchar not sqldecimal - but as I say a format file shouldn't be necessary.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
hutty
Starting Member
37 Posts |
Posted - 2005-04-28 : 23:45:46
|
| The reason I'm using the format file is that I would like to build an app in vb.net that would load data into SQL table. I change the datatype to all SQLCHAR. I get the error "bulk insert data conversion error (type mismatch).Thanks in advance. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-04-29 : 05:45:03
|
| That means that one of the columns in the table doesn't match the data.Probably it's trying to put character dtata into a numeric column.Have a look at http://www.mindsdoor.net/SQLTsql/BCP_quoted_CSV_Format_file.htmlfor how to use format files.I would bcp this into a single column table then insert from there to the main table unless there are massive amounts of rows which would make it too slow in which case I would bcp into a view without the format file.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
hutty
Starting Member
37 Posts |
Posted - 2005-05-02 : 13:11:21
|
| I was able to get it to work by using "SET ANSI_WARNING OFF". Took about 12 seconds for 63544 rows.Is there an article on the site that reference how to import data into sql table using the view.thanks |
|
|
|
|
|
|
|
|
Bulk Insert Formatfile