Yes this is VFP 7.
I understand exactly what you are saying Cetin however I think I found a another answer that takes care of a couple of other inssues I know that I am going to have (Skipping Fields). The other answer is a Format file. The whole app is table driven so I should have most of what I need to create a Format File on the fly, if not I will just add to data dictionary files. I just printed all the specs for a format file from Books Online.
Thanks I may have other questions on this before I am done.
>>I am in the process of writing a VFP app to migrate data from 75 VFP tables to the 75 corresponding SQL Server 2000 tables. All told around 15 million records 4 gig of data.
>>
>>So far app will create all tables on SQL and create the indexes for all tables on SQL. It will also COPY all of the VFP table data to txt files.
>>
>>The flow for the whole things is working very well (Indexes are created last after data is moved into SQL).
>>
>>After the tables are created then the app goes into a mode where for each VFP table:
>>
>>1). A "COPY TO (cSQLBCPFile) TYPE DELIMITED" is issued. cSQLBCPFile = [Location on SQL Server Hard Disk]
>>
>>2). An SPT - BULK INSERT Command is issued from the VFP app telling SQL Server to Bulk insert the ASCII file that was created in #1. At the moment I am using the command:
>>
>>cSQL = [BULK INSERT MCS.dbo.Activity ] + ;
>> [FROM 'e:\SQLBCP\SQLBCP.txt' ]
>>
>>I keep getting the SQL error: Unexpected end-of-file...
>>
>>I have trimmed the ASCII file back to 1 column (1 field) and three records:
>>
>>51
>>45
>>69
>>
>>The first field or column in the SQL table is Numeric 5.
>>
>>I have tried both of these:
>>
>>cSQL = [BULK INSERT MCS.dbo.Activity ] + ;
>> [FROM 'e:\SQLBCP\SQLBCP.txt' ] + ;
>> [WITH (DATAFILETYPE = 'char') ]
>>
>>cSQL = [BULK INSERT MCS.dbo.Activity ] + ;
>> [FROM 'e:\SQLBCP\SQLBCP.txt' ] + ;
>> [WITH (DATAFILETYPE = 'native') ]
>>
>>Thanks in advance for advice.
>>
>>Ron
>
>Ron,
>FieldTerminator and rowterminator are missing from 'bulk insert'. A delimited file has , as fieldterminator. RowTerminator defaults to newline so you wouldn't need it. Fieldterminator defaults to 'tab' character.
>Hopefully you have VFP7, otherwise char fields would have dblquotes that you should clear somehow. With VFP7 :
>
>COPY to (cSQLBCPFile) TYPE DELIMITED WITH "" WITH TAB
>
>would give you a nice file that could be bulk inserted without specifiying field and row terminators :
>
>cSQL = [BULK INSERT MCS.dbo.Activity ] + ;
> [FROM 'e:\SQLBCP\SQLBCP.txt' ] + ;
> [WITH (DATAFILETYPE = 'char') ]
>
>Otherwise specify them. ie :
>
>cSQL = [BULK INSERT MCS.dbo.Activity ] + ;
> [FROM 'e:\SQLBCP\SQLBCP.txt' ] + ;
> [WITH (DATAFILETYPE = 'char', FIELDTERMINATOR = ',') ]
>
>Cetin
What ben makes tracks for what wil be. Words in the air pirnt foot steps on the groun for us to put our feet in to.
Riddley Walker