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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy