File Upload - from excel spreadsheet

[hogie503]

Hello Alpha Expert's

I have a File upload User define function which upload data from an Excel file and insert the data into SQL table, the issue is the first row from the Excel spreadsheet will not get uploaded / get inserted in to the SQL table,
if the first row (row 2 in excel sheet) is shift down and row 2 is empty the function work fine, it will only insert data starting at row 3 from the excel sheet. Can anyone see why the function is not uploading that first row of data (row 2 )
The sample of the first spreadsheet will work fine, but the second sample will not work which is how the sheet is exported from legacy system.. Any help will be greatly appreciated. Thank you.


function ProcessFile as V (e as P)

dim file_path as C = e.fileArray[1].tempFileUploadedData
dim file_name as C = e.fileArray[1].fileName

''' Open connections to Excel File and Database
dim excel as SQL::Connection
if .not. excel.open("{A5API='Excel', FileName='" + file_path +"'}")
e.abort = .t.
e.errorText = "Unable to read file."
end
end if

dim db as SQL::Connection
if .not. db.open("::Name::User")
e.abort = .t.
e.errorText = "Unable to connect to database."
end
end if

''' Select Excel records from first worksheet
dim table as C = word( excel.ListTables(.t., .f., .f., .f.), 1, crlf() )
if .not. excel.Execute("select * from [" + table + "]")
e.abort = .t.
e.errorText = "Unable to open worksheet:\n\n" + excel.callResult.text
end
end if

dim Month_Number AS N
dim date_submitted AS D
dim new_Cycle AS N
date_submitted = convert_type(e.dataSubmitted.TXT_PAYROLL_DATE,"D")

dim payDate as C = date_format(ctod(e.dataSubmitted.TXT_PAYROLL_DATE), "yyyy-MM-dd")
dim Check_Run as N = excel.resultSet.data("CHECKRUN")
dim args as SQL::Arguments
args.set("LeaProfileFK", session.LeaProfileFK)
args.set("PBBudgetYear", session.CurrYear)
args.set("PBCycle", new_Cycle)
args.set("PBCreateUser", session.username)
args.set("PBCheckRun",Check_Run)

' start a transaction so we can rollback changes if something goes wrong
db.BeginTransaction()

while excel.resultSet.nextRow()

dim PBAccountNumber as C = alltrim(excel.resultSet.data("ANUM"))
dim PBDescription as C = alltrim(excel.resultSet.data("A_DECR"))
dim PBAmount as N = excel.resultSet.data("Amount")
dim Check_Run as N = excel.resultSet.data("CHECKRUN")
dim PayDateCheckRun as C = excel.resultSet.data("PAYDTCKRUN")

args.set("PayDateCheckRun", PayDateCheckRun)
args.set("PayDate", payDate)
args.set("PBAccountNumber", PBAccountNumber)
args.set("PBDescription", PBDescription)
args.set("PBAmount", PBAmount)
args.set("UserName", session.UserName)
args.set("PBCheckRun",Check_Run)

dim sql as C = <<%sql%

INSERT INTO Table_1 (
PBAccountNumber,
PBDescription,
PBAmount,
PBCheckRun,
PBCycle,
PRPOMasterFK,
PBCreateUser,
PbPayDateCheckRun,
PbPostedUser
) VALUES (
FORMATMESSAGE('%s-%s-%s-%s-%s-%s-%s',
SUBSTRING(:PBAccountNumber, 1, 2),
SUBSTRING(:PBAccountNumber, 3, 3),
SUBSTRING(:PBAccountNumber, 6, 3),
SUBSTRING(:PBAccountNumber, 9, 3),
SUBSTRING(:PBAccountNumber, 12, 3),
SUBSTRING(:PBAccountNumber, 15, 2),
SUBSTRING(:PBAccountNumber, 17, 2)
),
:PBDescription,
:PBAmount,
:PBCheckRun,
:PBCycle,
:PRPOMasterFK,
:PBCreateUser,
:PayDateCheckRun,
:UserName
)
%sql%

if .not. isnull(PBAccountNumber)
db.Execute(sql, args)
if .not. SqlSuccess(db)
e.abort = .t.
e.errorText = "Failed to parse line " + excel.resultSet.CurrentRowIndex + "\n\n" + db.callResult.text
db.RollBackTransaction()
end
end if
end if
end while

db.CommitTransaction()

' Cleaning up '''''''''''''''''''''''''''''''''''''''
if excel.IsOpen
excel.close()
end if

if db.IsOpen
db.close()
end if

if file.exists(file_path)
file.remove(file_path)
end if

' Refresh preview '''''''''''''''''''''''''''''''''''
dim _dlgObj as C = "{dialog.object}"
dim jsStr as C = <<%js%
window.parent.{_dlgObj}.setValue('TXT_FILE_NAME', '{e.fileArray[1].fileName}');
window.parent.refreshData();
%js%
e.javascript = evaluate_string(jsStr)
end function

PAYDTCKRUN	CHECKRUN	ANUM	A_DECR	Amount
2022-03-15 Ck Run: 1	1	110002171000000360	NURSES #4	4,442.00
2022-03-15 Ck Run: 1	1	110002171000000410	NURSES #11	5,552.00

PAYDTCKRUN	CHECKRUN	ANUM	A_DECR	Amount
2022-03-15 Ck Run: 1	1	110002171000000360	NURSES #4	4,442.00
2022-03-15 Ck Run: 1	1	110002171000000410	NURSES #11	5,552.00

[peteconway]

When you debug(1), what is in the first value seen by xbasic in the array?

[hogie503]

Hi,

when I export the file below it always seem to start with the second record , XBasic array first value is this one below

2022-03-15 Ck Run: 1

1

110002171000000700

NURSES #5

3,522.00

PAYDTCKRUN	CHECKRUN	ANUM	A_DECR	Amount
2022-03-15 Ck Run: 1	1	110002171000000360	NURSES #4	4,442.00
2022-03-15 Ck Run: 1	1	110002171000000700	NURSES #5	3,522.00
2022-03-15 Ck Run: 1	1	110002191050001100	NURSES #6	5,323.50

[peteconway]

Just check your referring a zero based row in the array - see how that goes.