BCP command line and Format file

[pi4paul05]

I am having difficulty importing a fairly simple text file into SQL Server using bcp. The following is an example of a text file I am trying to import along with a command line that I am using to run bcp.

NAME,ADDRESS
"LEARY,MARK","10943 IRONWOOD RD"
"COSGROVE,CHRISTOPHER","7414 MESA COLLEGE DR #39"
"HENRY,H L","4441 CAMROSE AVE"

bcp CCOR.dbo.tblTest in Test.txt -c -q -Swebsrv -Usa -Pdantes -t,

My table (which has two varchar columns Name & Address) does not contain the correct data after importing this file. Some of the name ends up in the Address column. The double quotes seem to be throwing it off. Can someone suggest a command line or Format file that can be used to import this data.

Thanks,

Paul Irwin

 

[JayKusch]

In looking at the data in the file, I see a couple of possible issues. First of all it is NOT, or does not appear, to be a fixed length record. SO ... it must me variable w/ "some" type of character that delimitates each field.

Unfortunately that field looks to be a comma. Problem here is that it also looks like your Name and Address fields contain commas as part of the descriptio. So for example, I bet when you run the BCP import the first record has a Name of "Leary" and an Address of "Mark".

I know we are "somnehow" going to need to use the "" in some manner to tell BCP that the character between the "" are in deed the FULL text/description that needs to go to the corrosponding field.


Thanks

J. Kusch

 

[pi4paul05]

Yes, you are correct! The first record has a Name of "Leary" and an Address of "Mark".

Is there a way to tell BCP that the data that is seperated by commas is double quoted. I can import this file using a DTS import package, but I would rather use BCP because of the command line nature of the utility and I have a large number (1.5 million) to import.

Any help would be much appreciated.

Paul

 

[JayKusch]

I know for a fact that DTS would make short work of that 1.5 million records and would be a snap to create.

But for now I am unsure of how to tackle this issue. I will keep hammering further but maybe another member can shed some light on this issue.

Thanks

J. Kusch

 

[pi4paul05]

Is DTSRun and alternative? This would give me the command line nature of the utility with the import 'power' of DTS. Is this a reasonable option?

 

[JayKusch]

Well... DTSRun executes a package that is created in the DTS Designer environment.

Thanks

J. Kusch

 

[MDXer]

Would running a script to replace "," with "|" e a possibility. This would allow you to specify the deimeter as | and the comma in the name would not affect the import.

Personally I never use a comma and use almost exclusively a pipe or some other obscure character depending on the Data.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck