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 |
|
avmreddy17
Posting Yak Master
180 Posts |
 Posted - 2006-04-30 : 11:04:17
|
| Thanks for all the responcesI have a text file of 360 Mb with around 220 columns in it approx. I created a table with the same structure. I would like to use the BCP to insert the data into the table. How do I create the format file to do it initially. I also want to automate the BCP as I will begetting this file on a daily basisThx for all the helpVenu |
|
|
avmreddy17
Posting Yak Master
180 Posts |
Posted - 2006-04-30 : 11:42:41
|
| Also, in the 220 columns I mentioned some of the columns are empty and all the columns are seperated by the PIPE .Some thing like this below , but it has qround 220 columns like this.1.0| |0.000001| | |XXXXXXX| ThxVenu |
 |
|
|
avmreddy17
Posting Yak Master
180 Posts |
Posted - 2006-04-30 : 15:18:11
|
| I was able to BCP in the data from a flat file. So Ignore my previous posting...I an new to using the BCP Utility.. bear with me If I am asking any stupid Questions..1. When do we need a BCP Format file2. Basically , I wanted to ignore the First and Last row in the Flat file while loading the data to SQL Server, For ignoring the First Row I used the -F switch harcoding the value to 2 . How do I ignore the last row as the no of rows change in the file every day. Is there a alternate way to use the -L switch.3. What are the Precautions I shud take while using the BCP utility?Thanks for all help in AdvanceThxVenu |
|
|
|
sshelper
Posting Yak Master
216 Posts |
Posted - 2006-05-01 : 07:58:35
|
| >> 1. When do we need a BCP Format file <<Here's what Books Online has to say about the format file:"A format file provides a way to bulk copy data selectively from a data file to an instance of SQL Server. This allows the transfer of data to a table when there is a mismatch between fields in the data file and columns in the table. This approach can be used when the fields in the data file are: - Fewer than the columns in the table.- More than the columns in the table.- In a different order from the columns in the table."Hope this helps.SQL Server Helperhttp://www.sql-server-helper.com |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2006-05-01 : 09:57:42
|
quote:
Originally posted by avmreddy17
I was able to BCP in the data from a flat file. So Ignore my previous posting...I an new to using the BCP Utility.. bear with me If I am asking any stupid Questions..
There are no stupid questions...however, using Books online will give you a lot of answersquote:
1. When do we need a BCP Format file
Usually when you have a fixed width file...in your case you don't need one. You just need to specify what the field termintor is in the bcp command line, which in your case is pipe...just a guess but the data source is Oracle, is it not?quote:
2. Basically , I wanted to ignore the First and Last row in the Flat file while loading the data to SQL Server, For ignoring the First Row I used the -F switch harcoding the value to 2 . How do I ignore the last row as the no of rows change in the file every day. Is there a alternate way to use the -L switch.
Don't think you can ignore the last row. I'm assuming they are headers and trailers. If that's the case you would want to audit the file anyway. You need a staging table to load all the data. Are the first and last rows idenified by something unique?quote:
3. What are the Precautions I shud take while using the BCP utility?
Should? Yes, make sure you write scripts to intergogate the file, and audit like I mentioned before. Load to a staging table, check the data, then populate the database table if everything checks outquote:
Thanks for all help in AdvanceThxVenu
Your welcome, and good luck. I f you supply us with some more details (like the what the first and last row looks like) we could help you more.Also, read the hint link in my sigMan that's most I've typed in 6 weeks.Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
avmreddy17
Posting Yak Master
180 Posts |
Posted - 2006-05-24 : 15:23:59
|
| First row is the Header and the last Row is a Footer...Footer has Date and no of rowsThxVenu |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-05-24 : 16:52:15
|
You may not have to worry about the last row. If the format is different from the other rows, it might produce an error and just ignore that line. BCP by default will ignore 10 errors and keep going. You can change the number of rows it will ignore with the -m switch.You can specify the field terminator with the -c paramater and the row terminator with the -r switch. Sometimes the row terminator for a file from a UNIX system is a line feed, instead of the Windows default of Carriage Return+Line Feed. If that is the case you can specify the row terminator in hex fomat: -r 0x0AThis command may get you started:bcp "MyDB.dbo.MyTable" in "x:\MyDataFile.Txt" -S MyServer -T -F 2 -b 50000 -t 0x7C -r 0x0A CODO ERGO SUM |
|
|
|
|
|
|
|
|
BCP