Import from DBF to MS SQL works fine, except when I include a Time field. I am using Bulk Import function of MS SQL so fields can easily be skipped. Get same error from SSMS or xbasic. Is it something obvious I am missing?
Bill.
Bill.
Alpha Software Mobile Development Tools: Alpha Anywhere | Alpha TransForm |
dim city as c dim state as c dim postal as c dim outfile as c = "c:\downloads\testfile.txt" dim dataout as c dim beg as t dim t1 as t dim t2 as t dim loopit as n dim dbf as p dbf = table.open("testfile",FILE_RO_SHARED) dbf.fetch_first() beg={01/01/2001 07:00} dataout = "City"+chr(9)+"State"+chr(9)+"Postal"+chr(9)+"RandomTime" file.append_line(outfile,dataout) dataout="" t1=now() loopit=1 for n = 1 to 1000000 if loopit <= 250 then dataout =dataout+ dbf.city+chr(9)+dbf.state+chr(9)+dbf.postal+chr(9)+ time("yyyy-MM-dd 0h:0m:0s.2",dbf.randomtime) if n/250 <> int(n/250) then dataout=dataout +crlf() loopit=loopit+1 else file.append_line(outfile,dataout) dataout = "" loopit=1 end if end if dbf.fetch_next() ''' if loopit > 250 then ''' file.append_line(outfile,dataout) ''' dataout = "" ''' loopit=1 ''' end if next dbf.close() file.append_line(outfile,"Started:"+time("yyyy-MM-dd 0h:0m:0s.2",t1)) t2=now() file.append_line(outfile,"Finished:"+time("yyyy-MM-dd 0h:0m:0s.2",t2)) '''debug(1) td=t2-t1 file.append_line(outfile,"Elapsed time: "+alltrim(str(td))+" seconds")
dim city as c dim state as c dim postal as c dim outfile as c = "c:\downloads\testfile.txt" dim dataout as c dim beg as t dim t1 as t dim t2 as t dim loopit as n beg={01/01/2001 07:00} dataout = "City"+chr(9)+"State"+chr(9)+"Postal"+chr(9)+"RandomTime" file.append_line(outfile,dataout) dataout="" t1=now() loopit=1 for n = 1 to 1000000 if loopit <= 250 then dataout =dataout+ "Milwaukee"+chr(9)+"WI"+chr(9)+"53207"+chr(9)+ time("dd/MM/yyyy 0h:0m",beg+(300*n)) if n/250 <> int(n/250) then dataout=dataout +crlf() loopit=loopit+1 else file.append_line(outfile,dataout) dataout = "" loopit=1 end if end if ''' if loopit > 250 then ''' file.append_line(outfile,dataout) ''' dataout = "" ''' loopit=1 ''' end if next file.append_line(outfile,"Started:"+time("dd/MM/yyyy 0h:0m:0s",t1)) t2=now() file.append_line(outfile,"Finished:"+time("dd/MM/yyyy 0h:0m:0s",t2))
dim city as c dim state as c dim postal as c dim outfile as c = "c:\downloads\millionData.txt" dim dataout as c dim beg as t dim t1 as t dim t2 as t dim loopit as n beg={01/01/2001 07:00} dataout = "City"+chr(9)+"State"+chr(9)+"Postal"+chr(9)+"RandomTime" file.append_line(outfile,dataout) dataout="" t1=now() loopit=1 for n = 1 to 1000000 if loopit <= 250 then dataout =dataout+ "Milwaukee"+chr(9)+"WI"+chr(9)+"53207"+chr(9)+ time("MM/dd/yyyy 0h:0m",*add_minutes(beg,n*5)) if n/250 <> int(n/250) then dataout=dataout +crlf() loopit=loopit+1 else file.append_line(outfile,dataout) dataout = "" loopit=1 end if end if ''' if loopit > 250 then ''' file.append_line(outfile,dataout) ''' dataout = "" ''' loopit=1 ''' end if next file.append_line(outfile,"Started:"+time("dd/MM/yyyy 0h:0m:0s",t1)) t2=now() file.append_line(outfile,"Finished:"+time("dd/MM/yyyy 0h:0m:0s",t2))
dim city as c dim state as c dim postal as c dim outfile as c = "c:\downloads\testfile.txt" dim dataout as c dim beg as t dim t1 as t dim t2 as t dim loopit as n dim dbf as p dbf = table.open("millionData",FILE_RO_SHARED) dbf.fetch_first() beg={01/01/2001 07:00} dataout = "City"+chr(9)+"State"+chr(9)+"Postal"+chr(9)+"RandomTime" file.append_line(outfile,dataout) dataout="" t1=now() loopit=1 for n = 1 to 1000000 if loopit <= 250 then dataout =dataout+ dbf.city+chr(9)+dbf.state+chr(9)+dbf.postal+chr(9)+ time("yyyy-MM-dd 0h:0m:0s.2",dbf.randomtime) if n/250 <> int(n/250) then dataout=dataout +crlf() loopit=loopit+1 else file.append_line(outfile,dataout) dataout = "" loopit=1 end if end if dbf.fetch_next() ''' if loopit > 250 then ''' file.append_line(outfile,dataout) ''' dataout = "" ''' loopit=1 ''' end if next dbf.close() file.append_line(outfile,"Started:"+time("yyyy-MM-dd 0h:0m:0s.2",t1)) t2=now() file.append_line(outfile,"Finished:"+time("yyyy-MM-dd 0h:0m:0s.2",t2)) '''debug(1) td=t2-t1 file.append_line(outfile,"Elapsed time: "+alltrim(str(td))+" seconds")
BULK INSERT CSVTest FROM 'c:\downloads\testfile.txt' WITH ( FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n' );
FUNCTION SQL_charges_csv AS C (Dbid as C ) 'Description:Dbid is W1, W2, etc. 'took 27 min in W2 w/ 780K records. 'lose tabs if 2 in a row and blanks are removed, so use ||. Dim outfile as C = p_datapath() +"charges.csv" Dim dataout as C = "" Dim tCharges as P tCharges = table.open("charges") tCharges.fetch_first() 'debug(1) t1 = now() loopit = 1 n = 0 while .not. tCharges.fetch_eof() n = n +1 if loopit <= 250 'skip ID field and Dbid. they will be added in sql. 'need to quote Description since it could contain commas. dataout = dataout +time("yyyy-MM-dd 0h:0m:0s.2",tCharges.create_time) +chr(9) +\ tCharges.create_by -chr(9) +\ time("yyyy-MM-dd 0h:0m:0s.2",tCharges.update_time) +chr(9) +\ tCharges.update_by -chr(9) +\ Dbid -chr(9) +\ tCharges.person_id +chr(9) +\ tCharges.id -chr(9) +\ quote(trim(tCharges.description)) +chr(9) +\ tCharges.date_entered +chr(9) +\ tCharges.date +chr(9) +\ tCharges.type -chr(9) +\ tCharges.charge_amt +chr(9) +\ tCharges.fee_percent -chr(9) +\ tCharges.fee_type -chr(9) +\ tCharges.other_costs +chr(9) +\ tCharges.tot_charge +chr(9) +\ tCharges.adjustment_amt +chr(9) +\ tCharges.net_total +chr(9) +\ tCharges.paid +chr(9) +\ tCharges.unpaid +chr(9) +\ tCharges.personid_old -chr(9) +\ trim(tCharges.issuing_agency) if n/250 <> int(n/250) dataout = dataout +crlf() loopit = loopit +1 else file.append_line(outfile, dataout) dataout = "" loopit = 1 end if end if tCharges.fetch_next() end while if dataout <> "" file.append_line(outfile, dataout) end if tCharges.close() t2 = now() td = t2 -t1 msg = "Started:" +time("yyyy-MM-dd 0h:0m:0s.2",t1) +crlf() +"Finished:"+time("yyyy-MM-dd 0h:0m:0s.2",t2) +crlf() +"Elapsed time: "+alltrim(str(td))+" seconds" +crlf(2) +n +" records processed." ui_msg_box("Ended", msg,UI_INFORMATION_SYMBOL) END FUNCTION
Could not export table 'charges.dbf'. Error exporting table: Database API specific error Invalid numeric type in CA5SQLParameterBlockODBC::FillDataBlock The error occurred executing statement number 2. SQL: INSERT INTO dbo.charges ( Id, CreateTime, CreateBy, UpdateTime, UpdateBy, Dbid, PersonId, ChargeId, Description, DateEntered, ChargeType, ChargeAmount, FeePercent, FeeType, FeeAmount, TotalCharge, AdjustmentAmount, NetCharge, PaidAmount, UnpaidAmount, Personid_Old, IssuingAgency) VALUES ( :new.Id, :new.CreateTime, :new.CreateBy, :new.UpdateTime, :new.UpdateBy, :new.Dbid, :new.PersonId, :new.ChargeId, :new.Description, :new.DateEntered, :new.ChargeType, :new.ChargeAmount, :new.FeePercent, :new.FeeType, :new.FeeAmount, :new.TotalCharge, :new.AdjustmentAmount, :new.NetCharge, :new.PaidAmount, :new.UnpaidAmount, :new.Personid_Old, :new.IssuingAgency) OPTION (MAXDOP 1)
Id = recno() CreateTime = time("yyyy-MM-dd 0h:0m:0s.2",create_time) CreateBy = Create_by UpdateTime = time("yyyy-MM-dd 0h:0m:0s.2",update_time) UpdateBy = Update_By Dbid = "W2" PersonId = Person_Id ChargeId = Id ...
CREATE TABLE charges ( Id int IDENTITY(1,1) NOT NULL, CreateTime datetime2(2) NULL, CreateBy varchar(25) NULL, UpdateTime datetime2(2) NULL, UpdateBy varchar(25) NULL, Dbid char(2) NULL, PersonId int NULL, ChargeId varchar(50) NULL, ...
FUNCTION sql_charges_csv2 AS C ( ) 'Description:Dbid is W1, W2, etc. 'took 27 min in W2 w/ 780K records. 'lose tabs if 2 in a row and blanks are removed, so use ||. Dim outfile as C = "c:\downloads\charges.txt" Dim dataout as C = "" Dim tCharges as P tCharges = table.open("charges") tCharges.fetch_first() debug(1) t1 = now() loopit = 1 n = 0 while .not. tCharges.fetch_eof() n = n +1 if loopit <= 250 then 'skip ID field and Dbid. they will be added in sql. 'need to quote Description since it could contain commas. dataout = dataout +time("yyyy-MM-dd 0h:0m:0s.2",tCharges.create_time) +chr(9) + tCharges.create_by +chr(9) + time("yyyy-MM-dd 0h:0m:0s.2",tCharges.update_time) +chr(9) + tCharges.update_by +chr(9) + tCharges.person_id +chr(9) + quote(trim(tCharges.description)) +chr(9) + tCharges.date_entered +chr(9) + tCharges.date +chr(9) + tCharges.type +chr(9) + tCharges.charge_amt +chr(9) + tCharges.fee_percent +chr(9) + tCharges.fee_type +chr(9) + tCharges.other_costs +chr(9) + tCharges.tot_charges +chr(9) + tCharges.adjustment_amt +chr(9) + tCharges.net_total +chr(9) + tCharges.paid +chr(9) + tCharges.unpaid +chr(9) + tCharges.personid_old +chr(9) + trim(tCharges.issuing_agency) if n/250 <> int(n/250) dataout = dataout +crlf() loopit = loopit +1 else file.append_line(outfile, dataout) dataout = "" loopit = 1 end if end if tCharges.fetch_next() end while if dataout <> "" file.append_line(outfile, dataout) end if tCharges.close() t2 = now() td = t2 -t1 msg = "Started:" +time("yyyy-MM-dd 0h:0m:0s.2",t1) +crlf() +"Finished:"+time("yyyy-MM-dd 0h:0m:0s.2",t2) +crlf() +"Elapsed time: "+alltrim(str(td))+" seconds" +crlf(2) +n +" records processed." ui_msg_box("Ended", msg,UI_INFORMATION_SYMBOL) END FUNCTION
Comment