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

Thanks a lot. I see already that it works just by adding
DATAFILETYPE = 'char',

to the SQL code after creating the txt file. I am aware to the fact that the code will import up to 1000 rows. Will I have the same problem with using the code you sent me in VFP? In any case I thank you,

Yehuda.

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