More Payroll Woes - Importing Hours

[MoGrace]

I changed PR services (again) and have to figure out how to get the data they need into a file formatted for their import file.

My current data is one record per employee - like this:

FILENO|EMPLOYEE|REG_HRS|OT1|OT2
1924|Aguirre, Redmedios |40
1854|Becerril, Candido |40| 20| 9

plus a few more fields I use for other purposes, but these are the fields I was importing.

Now I have to do this:

TYPE_PAY|TYPE_HRS|FILENO|EMPLOYEE|HOURS
E |REG|1924|Aguirre, Remedios|40
E |REG|1854|Becerril, Candido|40
E |OT1|1854|Becerril, Candido|20
E |OT2|1854|Becerril, Candido|9

I realize I can just make a new table for my input, but I still need the other one, and my input form does just what I need for the other processing steps. How would I go about using my current table to create this new one?

I'm thinking a button script could create an XLS file and then I could just skip the extra table.

[Stan Mathews]

Re: More Payroll Woes - Importing Hours

Robin,

Attach a sample of your table so we don't have to create one. Tell us what the file type is of the file "they need". There must be some date fields and ranges involved. What time period do you send them? Will all records in the output file begin with E| ?

these are the fields I was importing

Exporting?

[MoGrace]

Re: More Payroll Woes - Importing Hours

Exporting from A5 to Excel for importing to their SQL database, is my guess. I will get a sample together - stay tuned!

[GGandhi]

Re: More Payroll Woes - Importing Hours

you don't need to export to excel then import to sql, if you prefer.
if your employee table is
id numeric
name character
employee_id numeric
regular numeric
ot1 numeric
ot2 numeric

and the destination sql table in my case MySQL and the table is
id integer
name varchar
emp_id integer
pay_type varchar ( can be reg, ot1, ot2)
hours numeric
then you can just create the sql table yourself using xbasic and send it.

here is a code snippet for you to study

'debug(1)
dim tbl as p
dim name as c
dim eid as n
dim pay_type as c
dim regular as n
dim overtime1 as n
dim overtime2 as n
dim cn as sql::Connection
dim result as l = .f.
dim sqlInsert as c
dim args as sql::Arguments

result = cn.Open("::Name::alphasoftware")
if result then
		tbl = table.open("mograce")
		tbl.fetch_first()
		while .not. tbl.fetch_eof()
		eid = tbl.e_id
		name = tbl.name
		regular = tbl.reg
		overtime1 = tbl.ot1
		overtime2 = tbl.ot2
		sqlInsert = "INSERT INTO mograce (name, emp_id, pay_type, hours ) VALUES (:newName, :newEID, :newPay_type, :newHours)"
		args.set("newName", name)
		args.Set("newEID", eid)
		args.Set("newPay_type", "REG")
		args.Set("newHours",regular)
		result = cn.Execute(sqlInsert,args)
		if result then
			if overtime1 <> 0 then
				sqlInsert = "INSERT INTO mograce (name, emp_id, pay_type, hours ) VALUES (:newName, :newEID, :newPay_type, :newHours)"
				args.Set("newPay_type", "OT1")
				args.set("newHours",overtime1)
				result = cn.Execute(sqlInsert,args)
				if result then
					if overtime2 <> 0 then
						sqlInsert = "INSERT INTO mograce (name, emp_id, pay_type, hours ) VALUES (:newName, :newEID, :newPay_type, :newHours)"
						args.Set("newPay_type", "OT2")
						args.Set("newHours",overtime2)
						result = cn.Execute(sqlInsert,args)
					end if
				end if
			end if
		end if
	tbl.fetch_next()
	end while
        tbl.close()
	cn.close()
else
	msgbox("ERROR", "Unable to connectt to database")
end if

start dbf table:
dbf file
http://screencast.com/t/NGletOtfc
end sql table:
http://screencast.com/t/wYytzWWXi

obviously you will need to adjust to all the fields in your table and name them accordingly and name the connection string as you have defined.

edit: you see double entry for aguire since I ran twice to test using debug. also you can set to add E as the first field.

[Stan Mathews]

Re: More Payroll Woes - Importing Hours

Gandhi,

I imagine she's sending the Excel file to the payroll processing company and they import on their end.

Probably be better to have a plain text file. They can open that in excel if they want. Script below writes all records to the text file. No allowance for choosing date rante. That would have to be added to the three queries.

tbl = table.open("empdata") 'adjust table name
ix = tbl.query_create("T","isnotblank(\"reg_hrs\")","fileno")
tbl.fetch_first()
fil = file.create("c:\temp\paydata.txt",FILE_RW_EXCLUSIVE) 'adjust output location and name as desired
fil.write_line("TYPE_PAY|TYPE_hRS|FILENO|EMPLOYEE|HOURS")
while .not. tbl.fetch_eof()
	file.write_line("E|REG|"+alltrim(tbl.fileno)+"|"+alltrim(tbl.employee)+"|"+alltrim(str(tbl.reg_hrs,10,2)))
	tbl.fetch_next()
end while
ix.drop()
jx = tbl.query_create("T","isnotblank(\"ot1\")","fileno")
tbl.fetch_first()
while .not. tbl.fetch_eof()
	file.write_line("E|OT1|"+alltrim(tbl.fileno)+"|"+alltrim(tbl.employee)+"|"+alltrim(str(tbl.OT1,10,2)))
	tbl.fetch_next()
end while
Jx.drop()
Kx = tbl.query_create("T","isnotblank(\"ot2\")","fileno")
tbl.fetch_first()
while .not. tbl.fetch_eof()
	file.write_line("E|OT2|"+alltrim(tbl.fileno)+"|"+alltrim(tbl.employee)+"|"+alltrim(str(tbl.OT2,10,2)))
	tbl.fetch_next()
end while
kx.drop()
fil.flush()
fil.close()
tbl.close()

[GGandhi]

Re: More Payroll Woes - Importing Hours

oh, I see. you are right.
thanks.

[MoGrace]

Re: More Payroll Woes - Importing Hours

Hi Gandhi,
This is an online payroll service so I cannot write to their table directly, but can upload my data with a properly formatted file.

Stan,
You didn't wait for my table! But that's ok because you gave me what I needed to get started with this - thanks!

[GGandhi]

Re: More Payroll Woes - Importing Hours

okay I understand after stan pointed out to me. if you desire to create an excel file then
the code may be changed to. hopefully this works.

'debug(1)
dim tbl as p
dim employee_id as n
dim name as c
dim regular as n
dim overtime1 as n
dim overtime2 as n

dim Doc as Office::Excel2003Document
dim Sheet as Office::Spreadsheet
dim row as n = 1
dim col as n = 1

Sheet = doc.AddSheet("employee data")
sheet.Write(row,col,"EMP ID")
sheet.Write(row,col+1,"EMP Name")
sheet.Write(row,col+2,"Pay Type")
sheet.Write(row,col+3,"Hours")

tbl = table.open("mograce")
tbl.fetch_first()
while .not. tbl.fetch_eof()
	employee_id = tbl.emp_id
	name = tbl.name
	regular = tbl.reg
	overtime1 = tbl.ot1
	overtime2 = tbl.ot2
	row = row+1
	sheet.Write(row,col,employee_id)
	sheet.Write(row,col+1,name)
	sheet.Write(row,col+2,"REG")
	sheet.Write(row,col+3,regular)
	if overtime1 != 0 then
		row = row+1
		sheet.Write(row,col,employee_id)
		sheet.Write(row,col+1,name)
		sheet.Write(row,col+2,"OT1")
		sheet.Write(row,col+3,overtime1)
		if overtime2 != 0 then
			row = row+1
			sheet.Write(row,col,employee_id)
			sheet.Write(row,col+1,name)
			sheet.Write(row,col+2,"OT2")
			sheet.Write(row,col+3,overtime2)
		end if
	end if
	tbl.fetch_next()
	end while
	tbl.close()
	doc.Save("c:\temp\employee_data.xls")
	msgbox("Table exported to excel")
delete doc

dbf:
http://screencast.com/t/eyN21ajdU

excel end:
http://screencast.com/t/myVrY3Kzg