10 replies to this topic

[Amy Sibilla]

Hi there,

I need to load a new superlayer system with data from an excel file. Could someone give me an idea as to how to go about this.

Thank you in advance, Amy

[mdexter]

I assume you're loading into PRO-ISAM? If so, the simplest way would be to export the Excel into fixed-length ASCII text and then create a matching PRO-IV external (SEQ) file type that matches the exact layout. HTH. Mark Dexter

[CSuarezdelReal]

Same if you use any database. Just do what Mark said as the fist step, and then run a function that copies the SEQ file to the actual database table.

[bmoussa]

Since you are using SuperLayer (SL), I assume you are on a Glovia ERP.
To load data from Excel:
1- Save the worksheet in CSV format with comma delimited, or if the data has impeded commas (e.g. address), use semi comma. This will create a file with extension “.csv”
2- Rename this file to extension “.txt”. Assume the file name is “c_pi.txt”
3- Copy this file to …../userdata directory.

In SL
1- Option #2 create a file, for example “C_PI” (for Physical Inventory). In the alternate file name field, “Alt<” enter ‘c_pi_txt”.
2- Make the first field “C_PI_RECORD” with record length say “A” 200, and make sure that the Key field is blank. When getting out from the File Def, you are going to get an error message about file has no key, ignore it.
3- Use Option # 7 External File. File name = C_PI, File type = SEQ
4- Make the External type for the only field “C_PI_RECORD” “ALPHA”.

Create a function that reads C_PI file and updating the Glovia file/table.
Using the FOR statement look for the comma and move the data, for example:

#X = LEN(C_PI_RECORD)
FOR #I = 1 TO #X
IF C_PI_RECORD(#I,#I) = ‘;’ THEN LOOPEXIT;
ENDFOR
ITEM = C_PI_RECORD(1,(#I – 1))
#J = #I + 1
FOR #K = #J TO #X
IF C_PI_RECORD(#K,#K) = ‘;’ THEN LOOPEXIT
ENDFOR
REVISION = C_PI_RECORD(#J,(#K – 1))

And so on. You need to hard code the CCN in key mapping window, if it is not on the excel file.

[bmoussa]

sorry for the typo.

“Alt<” enter ‘c_pi_txt”. should be

“Alt<” enter ‘c_pi.txt”.

Good Luck

Bill

[Amy Sibilla]

Thanks so much. I'm going to see if I can follow all this and give it a try. - Amy

[Joseph Bove]

Along the lines of what Bill is saying:

Here is a global logic that makes dealing with csv files much nicer

PARSE

A PARMS($$STRING, $DELIMITER)
   
   #DELIMIT = INDEX($$STRING, $DELIMITER)
   IF #DELIMIT = 0 THEN
      $$STRING = ''
      $$PARSED_STRING = ''
      STEP 1
   ENDIF
   $$PARSED_STRING = $$STRING(1, #DELIMIT - 1)
   $$STRING = $$STRING(#DELIMIT + LEN($DELIMITER), LEN($$STRING))
   STEP 1

1  RETURN($$PARSED_STRING)

PARSE is destructive. Assuming that your file looks like "name", "address 1", "address 2"

You could simply code as follows

$NAME = PARSE($$ASCII_TEXT, ',')
$$ADDRESS1 = PARSE($$ASCII_TEXT, ',')
$$ADDRESS2 = PARSE($$ASCII_TEXT, ',')

We also have a separate global logic REPLACE for character substitution

So, to finish the job,

REPLACE($NAME, '"','')
REPLACE($$ADDRESS1, '"', '')
REPLACE($$ADDRESS2, '"', '')

hth,

Joseph

[Stuart Burton]

Another possible solution to this is to use the Microsoft Office Spreadsheet Active-X control. This will allow you to import data from original Excel format without having to save the file in CSV.

I haven't totally checked that this works - I had a play with the Spreadsheet control about 6 months ago and it had the ability to import/export files.

Just a thought.

[Glenn Meyers]

I have added and read data directly from an excel spread sheet.
It is exceedingly SLOW, the sheet must not change in any way, and in general it was not a functional way to do things.
If your user changes the spread sheet, you must re-code your statements.
We ended up using the fixed text (.prn) function in Excel for the output from Excel into ProIv.

Cheers!
Glenn

[Vol Yip]

I have done many data upload to GLOVIA system. Usually what I will do will be (which very similar to bomussa's suggestion):

1) Make the Spreadsheet's columns align to GLVOIA file definition. For example, if your spreadsheet needs to uplaod to ITEM file, make your item column has width 30 and Revsion column has width 6 etc.
2) Save the spreadsheet as prn file.
3) In GLOVIA, create an external file (alternate points to this prn file) with file type SEQ. You may need to go to SLEFILES to define the external column type. The file has corrssponding fields which map to the lenght of the spreadsheet column. If you use /RS/, then you need to create this file using Native PROIV, otherwsie you can create a SL file.
4) Create SL function (or Native PROIV function if the file is in Native), read the file and ADD / CHANGE the existing GLOVIA tables.

Hope this help.

Regards,

Vol

[mmeneses]

Another solution is to read the excel file direct from pro-iv. There is a command DDE_REQUEST that used to request data from an application through the DDE links within windows. I used this a lot in my application and I dont have any problem with it.

Here are the steps :
1. remove the headers of your spreadsheet

In the logic in of your pro-iv...
#STAT = DDE_REQUEST('Excel', 'System', 'Status', $STATUS')
IF $STATUS # 'Ready'
#A = SYSTEM("GUI WINDOWS ASYNC C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\EXCEL")
#STAT = DDE_REQUEST('Excel', 'System', 'Status', $STATUS')
ENDIF
#STAT = DDE_EXECUTE(('Excel', 'System', '$FILE_NAME')
#POS = INDEX($SHEET, '!') -1 $SHEET = $SHEET(1, #POS)



Then read each rows... (I set the total number of records for my loop to read head row.)

FOR #I = 1 TO 10124
#ID += 1 $LOCATION = 'R' + CONV(#ID)
#STAT = DDE_REQUEST('Excel', $SHEET, $LOCATION + 'C1', $CCN)
#STAT = DDE_REQUEST('Excel', $SHEET, $LOCATION + 'C1', $MAS_LOC)
#STAT = DDE_REQUEST('Excel', $SHEET, $LOCATION + 'C1', $WO_NUM)
LSCALL $ADDJOB
ENDFOR


HTH,
mmeneses