File Upload Function Help

[Griggs117]

I'm using a File Upload - User Defined action javascript function to allow users to upload text files containing routes and such. The data that is uploaded is not delimited in any way, it's just a set length. Right now I'm using substr() to parse the file, and I've found it's incredibly slow. Is there any way to use File.Read() and Read_Line() functions inside of this function, or is there a better way to do it? Thanks for your help!

[DaveF]

Re: File Upload Function Help

What size is the file you are uploading? How many times are you calling substr to parse the file? You would have to make an Ajax callback to use the File.Read() and Read_Line() Xbasic functions. Were you thinking about parsing the file on the server-side and send back some data much smaller in size?

[Griggs117]

Re: File Upload Function Help

What size is the file you are uploading? How many times are you calling substr to parse the file? You would have to make an Ajax callback to use the File.Read() and Read_Line() Xbasic functions. Were you thinking about parsing the file on the server-side and send back some data much smaller in size?

Hey Dave,

Basically what I'm doing is parsing the file to insert it into a mySQL database. Right now I'm calling substr to get the full line, and around 10 times to process each line. I'm working with a sample that is 2500 lines. We'll get the data back to the client side at some point, but not during this upload process. It will be done in another web component via sql calls.

[DaveF]

Re: File Upload Function Help

I think I'm starting to get tired. I misread your original post and thought you meant that you were using substr in Javascript for some reason. I'm sorry. I'm with you now.

I can't remember if I've used this action javascript routine before. So you have an Xbasic function defined in "After upload" and you are calling "substr" about 25,000 times inside this function? That could take a while. What function are you using to read the file right now? You can use File.Read() and Read_Line() in your Xbasic function to read a file, but are you asking if you can also somehow parse the data you want from the file using these functions?

[Griggs117]

Re: File Upload Function Help

I think I'm starting to get tired. I misread your original post and thought you meant that you were using substr in Javascript for some reason. I'm sorry. I'm with you now.

I can't remember if I've used this action javascript routine before. So you have an Xbasic function defined in "After upload" and you are calling "substr" about 25,000 times inside this function? That could take a while. What function are you using to read the file right now? You can use File.Read() and Read_Line() in your Xbasic function to read a file, but are you asking if you can also somehow parse the data you want from the file using these functions?

Basically this. I'm hoping that Read and Read_Line are more efficient than calling substr. My problem right now is how to open the file as a File object. I get passed e.fileArray, and I'm using this to call each line:
dim fileContents as c = e.fileArray[1].data
dim i as n = 1
dim nextLine as c = substr(fileContents,i,254)

while nextLine != null_value()
args.add("seqNum",substr(nextLine,1,8))
'+13 more substr calls similar to above and a sql insert command
i = i + 1
nextLine = substr(fileContents,i,254)
end while

I can understand where parsing can take some time, and 2500 won't be typical for this application, but right now it's taking about 40 minutes to parse. I feel like I can get it considerably more efficient.

[DaveF]

Re: File Upload Function Help

What are you doing with "args"? Is this a SQL:Arguments object? You typically using this with a SQL::Connection object when you are executing SQL statements. You normally only create a handful of SQL::Argument objects using the "Add" method. It looks like you are creating thousands!

Isn't there a file name stored in e.fileArray[1].filename? You should be able to read the file directly with any of the file processing methods. This should take seconds to complete your reading and parsing.

[Griggs117]

Re: File Upload Function Help

It is an SQL:Arguments object, I just left off part of the code to keep it short. Here's the code in full:

'open connection to database and declare sqlCommand and arguments variable
dim cn as sql::connection
cn.open("::Name::mreplus")
dim sqlRouteCommand as c
dim sqlPreadCommand as c
dim args as SQL::Arguments
dim argsRoute as SQL::Arguments

'add cityid and userid session variable to SQL argument object
argsRoute.add("whatcity",session.cityid)
argsRoute.add("whatuser",session.whatuser)

'set fileContents to uploaded file contents
dim fileContents as c = e.fileArray[1].data

dim i as n = 1
dim nextLine as c = substr(fileContents,i,254)

while nextLine != null_value()

'parse contents based on start position and length with "substr(value to parse,start character,length)" and add them to the SQL argument object
args.add("seqNum",substr(nextLine,1,8))
args.add("cusName",substr(nextLine,9,20))
args.add("address",substr(nextLine,29,20))
args.add("modID",substr(nextLine,49,9))
args.add("status",substr(nextLine,62,1))
args.add("service",substr(nextLine,63,1))
args.add("inCode1",substr(nextLine,64,2))
args.add("inCode2",substr(nextLine,66,2))
args.add("meterNum",substr(nextLine,68,9))
args.add("dials",substr(nextLine,77,1))
args.add("highRead",substr(nextLine,78,9))
args.add("lowRead",substr(nextLine,87,9))
args.add("accNum",substr(nextLine,121,15))
args.add("comCode",substr(nextLine,115,2))
args.add("routeid",1)

'SQL insert statements
'insert the routeid and other arguments into the pread table
sqlPreadCommand = "Insert into pread (cname,rdingseq,acctnum,seraddr1,modid,acctstat,service,rdercod1,rdercod2,meternum,dials,highread,lowread,commcode,routeid) Values (:cusName,:seqNum,:accNum,:address,:modID,:status,:service,:inCode1,:inCode2,:meterNum,:dials,:highread,:lowread,:comCode,:routeid)"
cn.execute(sqlPreadCommand,args)

'set nextLine to the next line by incrementing i
i = i + 1
nextLine = substr(fileContents,i,254)

'clear the SQL arguments before proceeding to avoid data conflict
'args.clear()

end while

end function


e.fileArray does contain a file name, however it's the name of the file on the client's computer. I haven't found any way to call the name of the file stored on the server.

EDIT: Also, is there any better way of setting the sql arguments?

[DaveF]

Re: File Upload Function Help

Have you tried commenting out the cn.execute() line to see how long everything else takes? That would be my guess as to what is taking so much time? 2500 SQL execute statements seems like it could take quite some time.

SQL:Arguments::Set() redefines an existing argument value or adds a new one if it does not exist. It might be a little more efficient than SQL:Arguments::Add() and you don't have to call SQL:Arguments::Clear().

[DaveF]

Re: File Upload Function Help

You can move the data buffer to a blob data type and then call the peekc method to pull a string of characters out. It takes two parameters, a starting position and a length.

dim hldData as b = e.fileArray[1].data
dim hldLine as c

hldLine = hldData.peekc(1, 8) ' copies 8 characters beginning at position 1

http://wiki.alphasoftware.com/~alpha...B.PEEKC+Method

[Pat Bremkamp]

Re: File Upload Function Help

I would look at ways to do the parsing either entirely in Alpha or entirely in SQL. If you are using MySQL, there is a LOAD DATA method that could speed up the process immensely. Or, you could use a stored procedure using the PREPARE function to insert records a few hundred at a time instead of 1 at a time. Here are excerpts from one of my stored procedures:

## Create a user variable with the insert portion of the statement
SET @ins = 'INSERT IGNORE INTO rating_plate_report
( PROJECT_NUMBER, DESCRIPTION, DISPLAY_VALUE, RP_SEQUENCE, PID_SEQUENCE, ELEC_SEQUENCE, HDR_SEQUENCE ) VALUES ';
## The following runs a few hundred times
SET @ins = concat(@ins,' ( ',p_project,', \'',v_name,'\', \'',v_display,'\', ',v_rp,', ',v_pi,', ',v_el,', ',v_hd,' ),');
##The following creates the single statement, runs it, and removes it from memory
PREPARE ins_statement FROM @ins;
EXECUTE ins_statement;
DEALLOCATE PREPARE ins_statement;

This routine inserts a couple hundred lines on my server in .06 seconds.

Or, if you want to do it in Alpha, you can read the entire 2500 lines into a single variable and use FOR EACH to pick them off one at a time.
vBulk = e.fileArray[1].data
for each vLine in vBulk
parse each line
next

Looks like your lines do not have any delimiters between fields. If there were, like tabs or commas, then the word() functions are very fast instead of substr().

Then, you could use the CompileStringTemplate() function to build an insert statement to insert a couple hundred lines at a time. Here's a compile string statement that I use to create some html output that shows some of this.

vTOCRow = "<tr><td style=\"font-family:tahoma;font-size:10pt;text-align:left;width:2in;\">{vTitle}</td></tr>"
p = CompileStringTemplate(vTOCRow)

for each vLn in ReportList
vTitle = word(vLn.value,2,"|")
*concat(vTOCBody,p.Output())
next

In your case, you would have a {seqNum},{cusName},{address} and so on in your template.

Good luck.

[frankbicknell]

Re: File Upload Function Help

Another approach would be to change all of the spaces to commas in the entire text file with something like strtran(text_file_variable,space(1) or " ",","). It looks like the only field that has more than one word is address. So then you could just insert the entire parsed text and concatenate the address field in a mysql procedure or an alpha callback. Using a MySQL procedure it would probably take less than 1 second.

[DaveF]

Re: File Upload Function Help

David,

I noticed that you are only incrementing your index (i) by 1 character at a time, not 254 or whatever your row size is. This could take quite a bit of time to process. If a row of data in your upload file is 254 characters, you should be doing the following:

i = i + 254

[Griggs117]

Re: File Upload Function Help

David,

I noticed that you are only incrementing your index (i) by 1 character at a time, not 254 or whatever your row size is. This could take quite a bit of time to process. If a row of data in your upload file is 254 characters, you should be doing the following:

i = i + 254

I think you probably just solved 50 of my problems. I knew it had to be something I was doing! Also thank you Pat for all of that information. That compile string statement looks really interesting, and I think I'll try to use it once I get a csv to import.

EDIT: Fixed the code, and you fixed all my problems Dave. 2500 lines now run in about 10 seconds!