Import Datetime to ms sql

[Bill Parker]

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 Parker]

Re: Import Datetime to ms sql

I was not precise when I said "DBF to SQL". I am exporting to CSV and importing that to SQL Server.

It turns out SQL Server Time field needs a period between seconds and milliseconds, not a space as Alpha exports to CSV. I see no way to accomplish that format change within the CSV export. I have a number of tables up to 700K recs/table, so I don't want to do post-export processing before the SQL import.

Has anyone ever exported a time field to SQL Server? The AlphaDAO genie does not work at all.

Bill.

[menehune]

Re: Import Datetime to ms sql

Never done it within the A5 environment but from MS Access to SQL Server you have to cast the time field as text in this format "00:00:00.000" for a 24hr interval. Try creating a calculated character field in the dbf to cvs export process.

[madtowng]

Re: Import Datetime to ms sql

Have you considered ignoring the basic csv export and genie and just using xbasic to put the data into your mssql table ?

This would give you the option of using the time() function which gives you major control of how the date/time values
are sent to the table.

[Bill Parker]

Re: Import Datetime to ms sql

I may have to resort to calc fields, although it will be painful due to the large number of tables affected.

I don't want to do a row-wise script with xbasic because it would be way too slow for the large number of records.

Bill.

[RichCPT]

Re: Import Datetime to ms sql

Not enough details to know exactly what you need to do here, but I would look at going direct from DBF to SQL. Here's some options I see in the documentation.

option 1: If your DBF tables are listed in the Control panel of a project, right-click the table, "Create operation...", select "Export Records" then click one of the two "create xxx" buttons and follow the prompts.

option 2: For a more programmatic solution, will the xbasic procedure "SQL::Connection::ResultSetFromDBF " work or do you have too many records for it to handle. Here's the documentation link:

SQL::Connection::ResultSetFromDBF Function

[madtowng]

Re: Import Datetime to ms sql

Hey Bill,

It took me a while to create a dbf table with a million records and do the testing.
The script below took 148 seconds to read the dbf and create a csv file with the sql date/time format I think you wanted.
The bottom script creates a file that after the last 2 line are removed can be imported into a dbf table.
If I forgot to explain anything else, just ask, I'm more than willing to answer questions.

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))

[madtowng]

Re: Import Datetime to ms sql

Bill,

I found a slight problem when I went to see how long it would take to import those million records into a sql table.
The code below generate a million row txt file in about 79 seconds.
After that is imported into a dbf table named millionData, the bottom script reads the million records and creates
a csv file named testFile.txt in about 129 seconds. After non-data rows were removed from testfile.txt, it took sql
3 seconds to import the million rows.

Please let me know if these timings are good or bad.

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")

[Bill Parker]

Re: Import Datetime to ms sql

Thanks Gregg. Did you use Bulk Insert to add to sql table?

I wrote my script using your pattern. To export 780K records took 27 min vs. 45 sec. for an export operation. The flexibility was nice, but I guess I'll need to go the route of adding calc fields to the dbf table for both time and logical fields in order to keep the running time manageable. This will be run multiple times a day while we are in transition.

Bill.

[madtowng]

Re: Import Datetime to ms sql

Hi Bill,
The code below is what I used to import the data to sql.
Just for giggles and grins, please share the script that took 27 minutes. I'm curious.

BULK
INSERT CSVTest
FROM 'c:\downloads\testfile.txt'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
);

[Bill Parker]

Re: Import Datetime to ms sql

Sure.

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

None of the options are great. There are a lot of tables that will need calc fields added, and some DBF tables are getting closer than I want to 2GB limit.

[RichCPT]

Re: Import Datetime to ms sql

Bill,
Did you see the XBasic function I mentioned in post # 6 that takes data directly from DBF and puts it into SQL?

option 2: For a more programmatic solution, will the xbasic procedure "SQL::Connection::ResultSetFromDBF " work or do you have too many records for it to handle. Here's the documentation link:

SQL::Connection::ResultSetFromDBF Function

[Bill Parker]

Re: Import Datetime to ms sql

Rich,

I previously tried the AlphaDAO export genie but could not get it to work for a table like this. The error is not granular enough for me to figure out what is wrong.

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)

All of the fields had to be calculated to get correct values, or to change field names to new standard. e.g.

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
...

Then the Create statement could be customized appropriately since most of the default types need to be changed.

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,
...

The genie could be helpful if it was possible to drill into the xbasic that is ultimately run by a5_AlphaDAO_export(), but it is a black box.

I have not tried the ResultSetFromDBF approach, but it sort of looks to have the same issues as the export genie. I WOULD like to have the export genie working if I could figure out the problems.

Bill.

[madtowng]

Re: Import Datetime to ms sql

Bill,

This isn't pretty. I reworked your code a bit and it got the time down to a little bit over 17 minutes.
Still much longer than I think either one of us wanted it to be.
Add to that, I am sure I assumed the wrong datatype based on the code you posted. I saw in your later post
that you have several tables to convert.


I'm not sure if you're plan is to move to sql and abandon the dbfs or what.
I am sure that with some creative thinking, you can get things done in a quicker time frame.


Capture.PNG

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

[RichCPT]

Re: Import Datetime to ms sql

If the SQL Server Native Client drivers are not installed Alpha has a terrible time properly reading and writing date/time fields.

The following MS page talks a bit about the driver: https://docs.microsoft.com/en-us/sql...ql-server-2014

If you have the "SQL Server Installation Center" program in your SQL program group on your server then you might go into maintenance mode and ensure that "SQL Client Connectivity SDK" is checkmarked under the "Shared Features" section.