Help with BCP Import - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Help with BCP Import

Author

Topic

spatrick
Starting Member

4 Posts

Posted - 2007-07-13 : 12:16:48

Hello, Let get right to it, OK?

Setup:
Windows 2003 web edition w/ SQL Express

Problem:
Error when importing text file to SQL Express. Not sure about the quality of the data since it is from a 3rd party and has over 1700 rows but it imports in to MS Access with no problems.

Error:
Unable to open BCP host data-file

Command:
bcp MLS_Data.dbo.tblFL_PuntaGorda in C:\Inetpub\AdminScripts\DL_MLS\puntagorda_data.txt -T -S.\SQLEXPRESS -f FL_PuntaGorda_bcp.fmt

Format File:
9.0
88
1 SQLCHAR 0 1 "\"" 0 Quote SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "\",\"" 1 MLS_ID SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 50 "\",\"" 2 MLS_STATE_ID SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 50 "\",\"" 3 MLS_LISTING_ID SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 50 "\",\"" 4 TLN_FIRM_ID SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 150 "\",\"" 5 MLS_OFFICE_NAME SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 50 "\",\"" 6 MLS_OFFICE_PHON SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 50 "\",\"" 7 TLN_REALTOR_ID SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 50 "\",\"" 8 MLS_AGENT_NAME SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 50 "\",\"" 9 MLS_AGENT_PHONE SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 50 "\",\"" 10 LISTING_DATE SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 50 "\",\"" 11 LISTING_EXPIRATION_DATE SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 50 "\",\"" 12 SOLD_DATE SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 50 "\",\"" 13 AVAILABLE_DATE SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 50 "\",\"" 14 PROPERTY_TYPE_CODE SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 50 "\",\"" 15 PROP_TYPE_DESCRIPTION SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 16000 "\",\"" 16 REMARKS SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 50 "\",\"" 17 STATUS_CODE SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 50 "\",\"" 18 SALE_PRICE SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 0 50 "\",\"" 19 SOLD_PRICE SQL_Latin1_General_CP1_CI_AS
21 SQLCHAR 0 50 "\",\"" 20 PROPERTY_STATE_ID SQL_Latin1_General_CP1_CI_AS
22 SQLCHAR 0 50 "\",\"" 21 STREET_NUMBER SQL_Latin1_General_CP1_CI_AS
23 SQLCHAR 0 50 "\",\"" 22 STREET_NAME SQL_Latin1_General_CP1_CI_AS
24 SQLCHAR 0 50 "\",\"" 23 STREET_TYPE SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 0 50 "\",\"" 24 STREET_DIRECTION SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 50 "\",\"" 25 UNIT_NUMBER SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 50 "\",\"" 26 LONGITUDE SQL_Latin1_General_CP1_CI_AS
28 SQLCHAR 0 50 "\",\"" 27 LATITUDE SQL_Latin1_General_CP1_CI_AS
29 SQLCHAR 0 50 "\",\"" 28 CITY SQL_Latin1_General_CP1_CI_AS
30 SQLCHAR 0 50 "\",\"" 29 CITY_ID SQL_Latin1_General_CP1_CI_AS
31 SQLCHAR 0 50 "\",\"" 30 ZIP_CODE SQL_Latin1_General_CP1_CI_AS
32 SQLCHAR 0 50 "\",\"" 31 ZIP_PLUS4 SQL_Latin1_General_CP1_CI_AS
33 SQLCHAR 0 50 "\",\"" 32 MLS_AREA SQL_Latin1_General_CP1_CI_AS
34 SQLCHAR 0 50 "\",\"" 33 COUNTY SQL_Latin1_General_CP1_CI_AS
35 SQLCHAR 0 50 "\",\"" 34 FIPS_COUNTY_CODE SQL_Latin1_General_CP1_CI_AS
36 SQLCHAR 0 50 "\",\"" 35 SUBDIVISION SQL_Latin1_General_CP1_CI_AS
37 SQLCHAR 0 50 "\",\"" 36 COMMUNITY_NAME SQL_Latin1_General_CP1_CI_AS
38 SQLCHAR 0 50 "\",\"" 37 YEAR_BUILT SQL_Latin1_General_CP1_CI_AS
39 SQLCHAR 0 50 "\",\"" 38 ACRES SQL_Latin1_General_CP1_CI_AS
40 SQLCHAR 0 50 "\",\"" 39 LOT_DIMENSIONS SQL_Latin1_General_CP1_CI_AS
41 SQLCHAR 0 50 "\",\"" 40 LOT_SQUARE_FOOTAGE SQL_Latin1_General_CP1_CI_AS
42 SQLCHAR 0 50 "\",\"" 41 LOT_SQUARE_FOOTAGE_LAND SQL_Latin1_General_CP1_CI_AS
43 SQLCHAR 0 50 "\",\"" 42 BUILDING_SQUARE_FOOTAGE SQL_Latin1_General_CP1_CI_AS
44 SQLCHAR 0 50 "\",\"" 43 BEDROOMS SQL_Latin1_General_CP1_CI_AS
45 SQLCHAR 0 50 "\",\"" 44 BATHS_TOTAL SQL_Latin1_General_CP1_CI_AS
46 SQLCHAR 0 50 "\",\"" 45 BATHS_FULL SQL_Latin1_General_CP1_CI_AS
47 SQLCHAR 0 50 "\",\"" 46 BATHS_HALF SQL_Latin1_General_CP1_CI_AS
48 SQLCHAR 0 50 "\",\"" 47 BATHS_THREE_QUARTER SQL_Latin1_General_CP1_CI_AS
49 SQLCHAR 0 50 "\",\"" 48 FIREPLACE_NUMBER SQL_Latin1_General_CP1_CI_AS
50 SQLCHAR 0 50 "\",\"" 49 TOTAL_ROOMS SQL_Latin1_General_CP1_CI_AS
51 SQLCHAR 0 50 "\",\"" 50 SCHOOL_DISTRICT SQL_Latin1_General_CP1_CI_AS
52 SQLCHAR 0 50 "\",\"" 51 SCHOOL_ELEMENTARY SQL_Latin1_General_CP1_CI_AS
53 SQLCHAR 0 50 "\",\"" 52 SCHOOL_MIDDLE SQL_Latin1_General_CP1_CI_AS
54 SQLCHAR 0 50 "\",\"" 53 SCHOOL_JUNIOR_HIGH SQL_Latin1_General_CP1_CI_AS
55 SQLCHAR 0 50 "\",\"" 54 SCHOOL_HIGH SQL_Latin1_General_CP1_CI_AS
56 SQLCHAR 0 50 "\",\"" 55 TOTAL_UNITS SQL_Latin1_General_CP1_CI_AS
57 SQLCHAR 0 50 "\",\"" 56 TOTAL_BUILDINGS SQL_Latin1_General_CP1_CI_AS
58 SQLCHAR 0 50 "\",\"" 57 TOTAL_LOTS SQL_Latin1_General_CP1_CI_AS
59 SQLCHAR 0 50 "\",\"" 58 HOA_FEES SQL_Latin1_General_CP1_CI_AS
60 SQLCHAR 0 50 "\",\"" 59 OWNERS_NAME SQL_Latin1_General_CP1_CI_AS
61 SQLCHAR 0 750 "\",\"" 60 LEGAL SQL_Latin1_General_CP1_CI_AS
62 SQLCHAR 0 50 "\",\"" 61 APN SQL_Latin1_General_CP1_CI_AS
63 SQLCHAR 0 50 "\",\"" 62 TAXES SQL_Latin1_General_CP1_CI_AS
64 SQLCHAR 0 50 "\",\"" 63 TAX_YEAR SQL_Latin1_General_CP1_CI_AS
65 SQLCHAR 0 50 "\",\"" 64 SECTION SQL_Latin1_General_CP1_CI_AS
66 SQLCHAR 0 50 "\",\"" 65 RANGE SQL_Latin1_General_CP1_CI_AS
67 SQLCHAR 0 50 "\",\"" 66 TOWNSHIP SQL_Latin1_General_CP1_CI_AS
68 SQLCHAR 0 50 "\",\"" 67 RENT_ON_SEASON SQL_Latin1_General_CP1_CI_AS
69 SQLCHAR 0 50 "\",\"" 68 RENT_OFF_SEASON SQL_Latin1_General_CP1_CI_AS
70 SQLCHAR 0 50 "\",\"" 69 PHOTO_IND SQL_Latin1_General_CP1_CI_AS
71 SQLCHAR 0 50 "\",\"" 70 LAST_MLS_UPDATE_DATE SQL_Latin1_General_CP1_CI_AS
72 SQLCHAR 0 50 "\",\"" 71 MASTER_BED SQL_Latin1_General_CP1_CI_AS
73 SQLCHAR 0 50 "\",\"" 72 BED2 SQL_Latin1_General_CP1_CI_AS
74 SQLCHAR 0 50 "\",\"" 73 BED3 SQL_Latin1_General_CP1_CI_AS
75 SQLCHAR 0 50 "\",\"" 74 BED4 SQL_Latin1_General_CP1_CI_AS
76 SQLCHAR 0 50 "\",\"" 75 BED5 SQL_Latin1_General_CP1_CI_AS
77 SQLCHAR 0 50 "\",\"" 76 KITCHEN SQL_Latin1_General_CP1_CI_AS
78 SQLCHAR 0 50 "\",\"" 77 BREAKFAST SQL_Latin1_General_CP1_CI_AS
79 SQLCHAR 0 50 "\",\"" 78 LAUNDRY SQL_Latin1_General_CP1_CI_AS
80 SQLCHAR 0 50 "\",\"" 79 DEN SQL_Latin1_General_CP1_CI_AS
81 SQLCHAR 0 50 "\",\"" 80 DINING SQL_Latin1_General_CP1_CI_AS
82 SQLCHAR 0 50 "\",\"" 81 FAMILY SQL_Latin1_General_CP1_CI_AS
83 SQLCHAR 0 50 "\",\"" 82 LIVING SQL_Latin1_General_CP1_CI_AS
84 SQLCHAR 0 50 "\",\"" 83 GREAT SQL_Latin1_General_CP1_CI_AS
85 SQLCHAR 0 50 "\",\"" 84 EXTRA SQL_Latin1_General_CP1_CI_AS
86 SQLCHAR 0 16000 "\",\"" 85 FEATURE_CODES SQL_Latin1_General_CP1_CI_AS
87 SQLCHAR 0 50 "\",\"" 86 MLS_OFFICE_ID SQL_Latin1_General_CP1_CI_AS
88 SQLCHAR 0 50 "\",\"" 87 MLS_AGENT_ID SQL_Latin1_General_CP1_CI_AS
89 SQLCHAR 0 200 "\"\r\n" 88 VIRTUAL_TOUR_URL SQL_Latin1_General_CP1_CI_AS

Please help,
Shawn

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-13 : 12:19:00

You might want to use a different delimiter... Check if your actual data has this delimiter in any of its columns which could be throwing off the import by a column.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

spatrick
Starting Member

4 Posts

Posted - 2007-07-13 : 12:55:48

Thanks for taking the time to reply.

It wouldn't matter if there were commas in the data itself because I can not do anything about it. The data comes from a 3rd party source which each record is entered in by several hundred people. I have to work with the data as-is. Any aternative method that is automated I am open to suggestions. I will be receiving this type of date every day from several sources. Needs to be automated.

Thanks,
Shawn

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-13 : 13:02:37

Based on the data you have so far (over time) you probably can identify some special character like ~ or ^ that is not used in the data file. Try using that delimiter. Can you provide some sample data so we can see how the data looks like,..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

spatrick
Starting Member

4 Posts

Posted - 2007-07-13 : 13:25:01

One row of data:

"puntagorda","FL","662658","586614","Exit Realty Foundations","9417643948","1297018","Barkhurst,Judith","","","","","","S","Ranch","Just bring your toothbrush and move into this beautiful, almost turnkey, 3/2/2 home with a beautiful pool and large lanai. You will love living in this wonderful home is such a nice, quiet neighborhood close to shopping and all the other conveniences. DON T MISS OUT ON THIS RARE OPPORTUNITY!","A","219900","","FL","717","RED BAY ST","","","","","","Port Charlotte","Y22737","33948","","P101-Port Charlotte SW of 41","Charlotte","12015","PCH","","1988","","80x127 x 80x127","","","1459","3","2","2","0","","0","","","Meadow Park","Murdock","","Port Charlotte","","","","0","","PCH 023 0734 0009 PORT CHARLOTTE SEC23 BLK734 LT9455/ 920 620/2110 652/2028 974/1050 2287 /620 2589/34","","2652","2006","9455","","","","","X","","11 x 15","10 x 11","10 x 11","","","11 x 17-5","8 x 11","4-10 x 10","","","","0x0","18-3 x 20-5","","A05,E05,E23,J02,J08,M01,M05,N07,N10,N11,N13,N18,N19,N24,O03,O09,O14,O15,O18,O22,Q04,Q08,R05,S41,T08,T23,T32,U01,U04,W12,W27,W45,Y25,Z07,Z09","","",""

One option could be to do a find and replace with ASP before running BCP. Hopefully I do not have to do that, it's just one more step I got to worry about.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-13 : 14:11:06

If you cannot change the data, use a staging table to import into first, manually do some data fixes for data that did not get imported.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

spatrick
Starting Member

4 Posts

Posted - 2007-07-13 : 14:21:17

Alright, I know I can import this data as-is into MS Access but I did it manually with the import wizard in MS Access. Is there command line tool or .dll I can use to automate this task. Once I have it MS Access what is the automated proceudre to import from MS access to SQL Express. I know I can do it manually but rememeber I will receive several of these text files every night around 2AM. I don't know about you but I am usually catching some z's about that time. Any help would be grateful.

Thanks so far,
Shawn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources