I am trying to bulk insert a text file. The file has fixed-length fields
with no field terminators. BOL says that field terminators are only
needed when the data does *not* contain fixed-length fields, which
implies they are optional -- so I made a format file without any (two
consecutive tabs with nothing between them). The following message
resulted:
Server: Msg 4827, Level 16, State 1, Line 1
Could not bulk insert. Invalid column terminator for column number
1 in format file
That sounds like I am required to have some sort of terminator in the
format file, even though there aren't any in the data file. Unfortunately,
the documentation on bcp/bulk copy and format files does not directly
address this point, and I would appreciate some help.
BTW, putting '""' (empty string) for the terminator also leads to errors,
with the first field overflowing -- bulk insert can't figure out where
it ends.
Thanks,
Jim Geissman
Countrywide Home Loans 3 9186 ji**********@countrywide.com (Jim Geissman) wrote in message news:<b8**************************@posting.google. com>... I am trying to bulk insert a text file. The file has fixed-length fields with no field terminators. BOL says that field terminators are only needed when the data does *not* contain fixed-length fields, which implies they are optional -- so I made a format file without any (two consecutive tabs with nothing between them). The following message resulted:
Server: Msg 4827, Level 16, State 1, Line 1 Could not bulk insert. Invalid column terminator for column number 1 in format file
That sounds like I am required to have some sort of terminator in the format file, even though there aren't any in the data file. Unfortunately, the documentation on bcp/bulk copy and format files does not directly address this point, and I would appreciate some help.
BTW, putting '""' (empty string) for the terminator also leads to errors, with the first field overflowing -- bulk insert can't figure out where it ends.
Thanks, Jim Geissman Countrywide Home Loans
Jim,
Just a thought, but have you tried using the "-c" flag with the BCP IN
command instead of using a format file? Create a target table where
the column widths exactly match the fields in your file, and give it a
try. ("-c" takes no parameters). Assuming you've got record
terminators in the correct place, I think this should work.
Personally, I hate using format files and avoid them like the plague
if I can.
bcp <db>..<target_tbl> in <datafile> -Uuser -Ppass -Sserver -c
Phil
Thanks, Phil.
I wish that were true. However it seems that -c assumes \t (tab)
separators. At least it doesn't work. Putting in -t (specify separator
but don't provide one) causes bcp to just sit there and do nothing.
I'm going to use DTS and specify column by column where they all end.
It's such a waste of effort, though, because the data is from the Census
and the input exactly matches the table, character by character.
Thanks again
Jim Jim,
Just a thought, but have you tried using the "-c" flag with the BCP IN command instead of using a format file? Create a target table where the column widths exactly match the fields in your file, and give it a try. ("-c" takes no parameters). Assuming you've got record terminators in the correct place, I think this should work. Personally, I hate using format files and avoid them like the plague if I can.
bcp <db>..<target_tbl> in <datafile> -Uuser -Ppass -Sserver -c
Phil
Jim Geissman (ji**********@countrywide.com) writes: I am trying to bulk insert a text file. The file has fixed-length fields with no field terminators. BOL says that field terminators are only needed when the data does *not* contain fixed-length fields, which implies they are optional -- so I made a format file without any (two consecutive tabs with nothing between them). The following message resulted:
Server: Msg 4827, Level 16, State 1, Line 1 Could not bulk insert. Invalid column terminator for column number 1 in format file
That sounds like I am required to have some sort of terminator in the format file, even though there aren't any in the data file. Unfortunately, the documentation on bcp/bulk copy and format files does not directly address this point, and I would appreciate some help.
You must specify the separator in quotes, but it can be the empty
string, "". The tabs does not mean anything to BCP, as far as I know.
At least it never complain about lack of tabs in my format files.
BTW, putting '""' (empty string) for the terminator also leads to errors, with the first field overflowing -- bulk insert can't figure out where it ends.
What about posting:
o CREATE TABLE statement for your table.
o The format file. (The one with "" in it.)
o A sample file to bulk-load.
That makes it a little easier to have a guess of what is going on.
If the data file is more than 75 characters wide, you are probably
better of putting it an attachment.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: nimdez |
last post by:
Hi,
I am working on an existing code base in which a lot of data displayed
to the user is formatted in tables. Most tables are printed row-by-row
using printf() with "%s" print conversion...
|
by: perspolis |
last post by:
I have 3 columns in my datatabel name Total,unit,Price.
I use a column expression in my project..and in this
expression i multiplied two column...
for example ...
|
by: Robert Schuldenfrei |
last post by:
Dear NG,
After being away from C# programming for a spell, I am trying my hand at
what should be a simple task. I have been hitting my head against the wall
this morning. I have a simple order...
|
by: Owen T. Soroke |
last post by:
Using VB.NET
I have a ListView with several columns.
Two columns contain integer values, while the remaining contain string
values.
I am confused as to how I would provide functionality to...
|
by: Aaron Smith |
last post by:
Ok. I have a dataset that has multiple tables in it. In one of the child
tables, I have a column that I added to the DataSet (Not in the
DataSource). This column does not need to be stored in the...
|
by: ricky |
last post by:
Hello,
If anyone could help me with this I would highly appreciate it. I've
tried everything and nothing works. What I am trying to do is so damn
basic and it's just frustrating that it seems...
|
by: TPhelps |
last post by:
I have a sample of an unbound (autogeneratecolumns is true) sortable/pagable
datagrid that works.
I want to change one of the columns to a hyperlink. The examples I find use
a bound column. I...
|
by: Steph. |
last post by:
I have a List view displaying data in Detail mode with several columns.
How I can get the column index the user clicked on ? (when user click on an item inside the ListView, not on a column...
|
by: Peter Gibbs |
last post by:
I need some help with this problem.
I'm using Access 2002 with XP.
My problem is with a 2-column listbox.
My VBA code puts text data into the listbox.
The problem is that the text data...
|
by: Frank |
last post by:
I could use your help. I need to add tooltips to each column of an
HTML table, for the entire column of cells. I want to add a single
element to the table, and have that one element represent the...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |