table to csv bcp error - 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

Transact-SQL (2005)

table to csv bcp error

Author

Topic

Mallen
Starting Member

27 Posts

Posted - 2010-11-16 : 13:40:00

Please help, totally stumped.

I am trying to export a table into a csv. The output file will not have every field filled so it will be sent to look something like:
141354,235,,,Name,,,20101193,,xyzco,,235,,

My problem is when I run the export (the same export I use to create 3 or 4 other csv files in different procedures) the file it creates is only filled with the ascii boxes ? in notepad and when I open it in excel it give me this:
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????,??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????

This is supposed to be two rows in my test data. I can see the two rows just fine in the table and in notepad there are a bunch of boxes, then a comma, then a bunch more boxes like it is two rows but why is it outputting random ascii instead of the actual data in the field? It produces no errors at all when I run it, this is the execute:
NULL
Starting copy...
NULL
2 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (2000.00 rows per sec.)
NULL

Here is the code and table in question:

CREATE TABLE YGCOut
(
[FILENO] varchar(10),
[FORW_FILE] varchar(20),
[MASCO_FILE] varchar(15),
[FORW_ID] varchar(10),
[FIRM_ID] varchar(10),
[DATE_FORW] varchar(8),
[LAW_LIST] varchar(4),
[COMM] varchar(4),
[SFEE] varchar(4),
[ORIG_CLAIM] money,
[ORIG_INT] int,
[ORIG_INT_D] varchar(8),
[RATES_PRE] int,
[RATES_POST] int,
[CRED_NAME] varchar(25),
[CRED_NAME2] varchar(25),
[CRED_STREET] varchar(25),
[CRED_CS] varchar(15),
[CRED_ZIP] varchar(9),
[DEBT_BAL] money,
[CTYPE] varchar(4),
[DATE_LPAY] varchar(8),
[AMT_LPAY] int,
[DATE_OPEN] varchar(8),
[CHRG_OFF_D] varchar(8),
[CHRG_OFF_A] int,
[PURCHASE_D] varchar(8),
[ORIG_CRED] varchar(30),
[ORIG_CRED2] varchar(30),
[PORT_ID] varchar(20),
[CRED_CNTRY] varchar(3),
[LPAY_ISS_D] varchar(8),
[LPAY_ISS_AMT] int,
[MEDIA] varchar(1),
[DELINQ_D] varchar(8),
[ACCEL_D] varchar(8),
[REPO_D] varchar(8),
[SALE_D] varchar(8),
[MATUR_D] varchar(8),
[SOL_START_D] varchar(8),
[SOL_END_D] varchar(8),
[LATE_FEE] int,
[HIST_CRED1] varchar(50),
[HIST_CRED2] varchar(50),
[HIST_CRED3] varchar(50),
[HIST_CRED4] varchar(50),
[HIST_CRED5] varchar(50),
[D1_NAME] varchar(30),
[D1_SALUT] varchar(1),
[D1_ALIAS] varchar(25),
[D1_STREET] varchar(25),
[D1_CS] varchar(23),
[D1_ZIP] varchar(9),
[D1_PHONE] varchar(12),
[D1_FAX] varchar(12),
[D1_SSN] varchar(15),
[RFILE] varchar(8),
[D1_DOB] varchar(8),
[D1_DL] varchar(17),
[D1_STATE] varchar(3),
[D1_MAIL] varchar(1),
[SERVICE_D] varchar(8),
[ANSWER_DUE_D] varchar(8),
[ANSWER_FILE_D] varchar(8),
[DEFAULT_D] varchar(8),
[TRIAL_D] varchar(8),
[HEARING_D] varchar(8),
[LIEN_D] varchar(8),
[GARN_D] varchar(8),
[SERVICE_TYPE] varchar(4),
[D1_STRT2] varchar(25),
[D1_CITY] varchar(30),
[D1_CELL] varchar(12),
[SCORE_FICO] varchar(3),
[SCORE_COLLECT] varchar(3),
[SCORE_OTHER] varchar(3),
[D1_CNTRY] varchar(3),
[D1_STREET_LONG] varchar(50),
[D1_STREET2_LONG] varchar(50),
[D2_NAME] varchar(25),
[D2_STREET] varchar(25),
[D2_CSZ] varchar(25),
[D2_PHONE] varchar(15),
[D2_SSN] varchar(15),
[D3_NAME] varchar(25),
[D3_STREET] varchar(25),
[D3_CSZ] varchar(25),
[D3_PHONE] varchar(15),
[D3_SSN] varchar(15),
[D2_DOB] varchar(8),
[D3_DOB] varchar(8),
[D2_DL] varchar(17),
[D3_DL] varchar(17),
[D2_CNTRY] varchar(3),
[D3_CNTRY] varchar(3),
[D2_STREET_LONG] varchar(50),
[D2_STREET2_LONG] varchar(50),
[D3_STREET_LONG] varchar(50),
[D3_STREET2_LONG] varchar(50)
)
INSERT INTO YGCOut ([FILENO],[FORW_FILE],[FORW_ID],[FIRM_ID],[DATE_FORW],[ORIG_CLAIM],[CRED_NAME],[CRED_STREET],[CRED_CS],[CRED_ZIP],[DEBT_BAL],[DATE_LPAY], [DATE_OPEN],[CHRG_OFF_D],[PURCHASE_D],[ORIG_CRED],[DELINQ_D],[D1_NAME],[D1_STREET],[D1_CS],[D1_ZIP],[D1_PHONE],[D1_SSN],[D1_DOB],[D1_DL],[D1_STATE],[D1_STRT2],[D1_CITY],[D2_NAME],[D2_STREET],[D2_CSZ],[D2_PHONE],[D2_SSN],[D2_DOB],[D2_DL])
SELECT
r1.[FILENO],
r1.[FORW_FILE],
r1.[FORW_ID],
r1.[FIRM_ID],
r1.[DATE_FORW],
r1.[ORIG_CLAIM],
r1.[CRED_NAME],
r1.[CRED_STREET],
r1.[CRED_CS],
r1.[CRED_ZIP],
r1.[DEBT_BAL],
r1.[DATE_LPAY],
r1.[DATE_OPEN],
r1.[CHRG_OFF_D],
r1.[PURCHASE_D],
r1.[ORIG_CRED],
r1.[DELINQ_D],
r2.[D1_NAME],
r2.[D1_STREET],
r2.[D1_CS],
r2.[D1_ZIP],
r2.[D1_PHONE],
r2.[D1_SSN],
r2.[D1_DOB],
r2.[D1_DL],
r2.[D1_STATE],
r2.[D1_STRT2],
r2.[D1_CITY],
r3.[D2_NAME],
r3.[D2_STREET],
r3.[D2_CSZ],
r3.[D2_PHONE],
r3.[D2_SSN],
r3.[D2_DOB],
r3.[D2_DL]
FROM @Record03 r3 RIGHT JOIN(@Record01 r1 LEFT JOIN @Record02 r2 ON r1.FILENO=r2.FILENO)ON r3.FILENO=r1.FILENO

DECLARE @sql varchar(8000)
SELECT @sql= 'bcp DB801..YGCOut out D:\Outfiles\YGC\TX57-'
+ cast(datepart(yyyy, getdate()) as varchar)
+ RIGHT('0' + cast(datepart(mm, getdate()) as varchar), 2)
+ RIGHT('0' + cast(datepart(dd, getdate()) as varchar), 2)
+ '.csv -c -t, -T -S'
exec master..xp_cmdshell @sql

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-17 : 01:46:32

Not sure but try to add
-CACP
so the output will be converted from SQL Server codepage to ANSI/Windows codepage.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources