February 20, 2017 at 6:57 am
Hi,
I have a csv file - output from a sybase database which is column delimited with a ','
whne I try to import this into a sqlserver 2005 database using BCP via a batch file using a .fmt file I get the above error
for %%i in (D:\AppSupp\files_2b_sqlloaded.txt) do (
bcp Ideal_Risk.dbo.Sybase_Disk_space in %%i -t , -f D:\AppSupp\Sybase_stats.fmt -L 1 -e d:\AppSupp\Error.log -m 2 -T >D:\AppSupp\import.log
)
initially I thought it was due to CRLF as the last line, I have now removed this (via another script)
still it errored - so I manually removed white space from the csv file - still fails
9.0
8
1 SQLCHAR 0 50 "," 1 Database_Name Latin1_General_CI_AS
2 SQLCHAR 0 18 "," 2 Data_Size_MB ""
3 SQLCHAR 0 18 "," 3 Used_Data_MB ""
4 SQLCHAR 0 18 "," 4 Data_Full_Pcnt ""
5 SQLCHAR 0 18 "," 5 Log_Size_MB ""
6 SQLCHAR 0 18 "," 6 Free_Log_MB ""
7 SQLCHAR 0 18 "," 7 Log_Full_Pcnt ""
8 SQLCHAR 0 19 "\r\n" 8 Snap_shot ""
data example rows
web_tempdb , 512.00 , 8.79 , 1.72 , 512.00 , 508.25 , 0.73 , Jan 31 2017 11:41AM
sep_tempdb , 1024.00 , 10.01 , 0.98 , 512.00 , 510.00 , 0.39 , Jan 31 2017 11:41AM
sybsecurity , 1536.00 , 1027.71 , 66.91 , 128.00 , 127.50 , 0.39 , Jan 31 2017 11:41AM
sa_tempdb , 4096.00 , 21.55 , 0.53 , 512.00 , 510.00 , 0.39 , Jan 31 2017 11:41AM
master , 128.00 , 15.90 , 12.42 , NULL , 112.10 , NULL , Jan 31 2017 11:41AM
sybsystemprocs , 256.00 , 127.85 , 49.94 , NULL , 128.15 , NULL , Jan 31 2017 11:41AM
IDEAL , 118784.00 , 95438.59 , 80.35 , 1024.00 , 996.25 , 2.71 , Jan 31 2017 11:41AM
dbccdb , 2048.00 , 1813.31 , 88.54 , 128.00 , 127.50 , 0.39 , Jan 31 2017 11:41AM
user_tempdb , 512.00 , 7.55 , 1.48 , 512.00 , 510.00 , 0.39 , Jan 31 2017 11:41AM
sybsystemdb , 13.00 , 1.84 , 14.15 , NULL , 11.16 , NULL , Jan 31 2017 11:41AM
sybmgmtdb , 160.00 , 23.72 , 14.83 , 10.00 , 9.96 , 0.41 , Jan 31 2017 11:41AM
if_tempdb , 512.00 , 9.62 , 1.88 , 512.00 , 503.98 , 1.57 , Jan 31 2017 11:41AM
tempdb , 2052.00 , 40.00 , 1.95 , 1152.00 , 1145.22 , 0.59 , Jan 31 2017 11:41AM
model , 153.00 , 6.15 , 4.02 , NULL , 146.85 , NULL , Jan 31 2017 11:41AM
Im sure it is the data but I am stumped as to what is wrong
February 20, 2017 at 7:21 am
In your format file, you are telling it to delimit column 2 onwards with a Tab character, but the sample data you posted is comma delimited.
February 20, 2017 at 7:32 am
doh... I had posted the wrong .fmt file.... now corrected
I tried changing to tab to see if it made any difference..... but it didnt
February 23, 2017 at 2:11 am
turns out the problem was the date field on each row.. wasnt being recognised as datetime therefore would not import
changed process to import into a temp table with the date field as varchar
then did an insert from temp to base table converting the date field into a bona fide datetime field 🙂
February 23, 2017 at 5:43 am
rob_nye - Thursday, February 23, 2017 2:11 AMturns out the problem was the date field on each row.. wasnt being recognised as datetime therefore would not importchanged process to import into a temp table with the date field as varchar
then did an insert from temp to base table converting the date field into a bona fide datetime field 🙂
Importing into a staging table, like you did, is usually the best way to go because it allows you to pre-validate all data long before it hits your production table. It also makes doing "upserts" a whole lot easier, if that's something that you need to do.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply