Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 To: Yehuda Epstein
  Where is Yehuda Epstein?
 Monsey
 New York - United States
 Yehuda Epstein
 Tags
Subject: RE: SQL server importing from a deli file
Thread ID: 311102 Message ID: 311117 # Views: 44 # Ratings: 2
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Tuesday, June 28, 2011 10:49:23 PM         
   


> Hi to all,
> In reference to Thread ID: 128605 Message ID: 128737. I tried to automate a table data import but with no success.
> I am trying to automate an import of a dbf table, copied to deli data format, into a SQL 2008 R2 database table. The deli file first row looks like this:
>
> "907","878","832","AK","BARROW","00550","11386",-156.788902,71.294329
>
> The sql TABLE COULMNS properties are :
>
> create table [Npanxx_2010].[dbo].[test]
> (npa char(3) null,
> nxx char(3) null,
> lata char(3) null,
> state char(2) null,
> city char(100) null,
> majorh char(5) null,
> majorv char(5) null,
> longitude float null,
> latitude float null)
>
> The SQL code I try to use is:
>
BULK
>  INSERT test FROM 'C:\geo.txt' 
>  WITH (FIELDTERMINATOR = ',',
>  ROWTERMINATOR = '/n')

>
> The error messages I get are:
> Msg 4863, Level 16, State 1, Line 1
> Bulk load data conversion error (truncation) for row 1, column 1 (npa).
> Msg 4866, Level 16, State 8, Line 1
> The bulk load failed. The column is too long in the data file for row 1, column 9. Verify that the field terminator and row terminator are specified correctly.
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
> Msg 7330, Level 16, State 2, Line 1
> Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
>
> Could you please help me resolve this issue.
>
> Thanks,
> Yehuda.

In your structure, for example, npa is defined as char(3) but in data file line I see it as 5 characters. You would need to remove quotes to make the size match. Here is a SQL bulk insert sample:

https://www.foxite.com/archives/0000301912.htm

Cetin Basoz

.Net has got better.Think about moving - check my blog:
My Blog
Blog (mirror) - sounds to be down

ENTIRE THREAD

SQL server importing from a deli file Posted by Yehuda Epstein @ 6/28/2011 9:24:44 PM
RE: SQL server importing from a deli file Posted by Cetin Basoz @ 6/28/2011 10:49:23 PM
RE: SQL server importing from a deli file Posted by Yehuda Epstein @ 6/28/2011 11:32:49 PM
RE: SQL server importing from a deli file Posted by Cetin Basoz @ 6/29/2011 12:46:31 PM
RE: SQL server importing from a deli file Posted by Yehuda Epstein @ 6/29/2011 3:34:47 PM