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 |
|
stumbling
Posting Yak Master
104 Posts |
 Posted - 2006-08-08 : 07:48:04
|
Hi allwork wants me to update our data bases on a semi regular basis with information contained in a txt file. I would like to use BCP but i need to take sections of the file out and insert into the relevant columns in the table. All lines in the file look something like this below. The problem i see is how can i tell BCP to stop and start at certain columns in the file??I have looked at books on line but i must be missing something?Any help would be greatly appreciated Cheers Phil002 35MCMANUS RK Mr R MMan 2 Ced Court Cobram VIC3644 MCMANUS RK BM NNY0000 99999999 30 1N1 C 02 10202961108 0000000000+0000000000+0000000000+0000017000+0000000000+0000000000+0000000000+0000000000+0000000000+0000000000+0000002002+000000000074884+199604290000005415+20060621200606300000002002+ 35N1993033100000000+Y002000000000000000000NPNNNNNNNNNNNNNNNNNN1 NNNN |
|
|
fcoreyesv
Starting Member
18 Posts |
Posted - 2006-08-08 : 09:44:29
|
| What is the statement you are using to do the import?Are you using any of the switches to specify the field terminator ?Another way is to create a program that reads each record into memory and then you decide which values you want to save into the tables. |
 |
|
|
stumbling
Posting Yak Master
104 Posts |
Posted - 2006-08-09 : 06:01:56
|
| I suppose what the problem for me is that i am uncertain on how to specify a start and stop position to pull data from in the file i had another look at BOL but can not see an easy way to do this. Once i get past this specific hurdle i feel i am on my way for the rest. If there is any way to do this like i would in query analyzer using Substring then i would greatly appreciate the info. :-) Phil |
|
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2006-08-09 : 16:26:01
|
stumbling,quote:
I suppose what the problem for me is that i am uncertain on how to specify a start and stop position
You do this by specifying a field terminator. Unfortunately, your sample data uses two separate ones (a space and a +).You could either:a) Import each line into a single varchar() field in a staging table, and write a stored procedure to parse each row in that staging table into its' separate parts and insert them into your destination table; orb) Write an external application to read each line, parse the individual values, and use a parameterized query to do the insert.The choice is up to you, based on your abilities.Ken |
|
|
|
stumbling
Posting Yak Master
104 Posts |
Posted - 2006-08-09 : 21:18:59
|
Thanks kenthought i might have to insert the entire row and then pump it out. Trouble now is that i was going to use Bulk Insert which is fine however it only inserts the first 257 characters in to the database the field type is varchar(1000) is there a limit on this type of statement that you know off? BULK INSERT DEMO.dbo.[PM] FROM 'C:\murray\pm.txt' |
|
|
|
|
|
|
BCP data from a file