Jump to content


Photo
- - - - -

take on data from excel file


10 replies to this topic

#1 Amy Sibilla

Amy Sibilla

    Member

  • Members
  • PipPip
  • 23 posts
  • Gender:Female
  • Location:Warwick, United States

Posted 18 January 2005 - 05:05 PM

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

#2 mdexter

mdexter

    Advanced

  • Members
  • PipPipPip
  • 70 posts
  • Gender:Male

Posted 18 January 2005 - 05:16 PM

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

#3 CSuarezdelReal

CSuarezdelReal

    Advanced

  • Members
  • PipPipPip
  • 91 posts
  • Gender:Male

Posted 18 January 2005 - 10:46 PM

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.
Claudio Suárez del Real
"It is not the strongest of the species that survive, nor the most intelligent, but the ones most responsive to change."

#4 bmoussa

bmoussa

    Newbie

  • Members
  • Pip
  • 2 posts
  • Gender:Male
  • Location:Chicago, Illinois, USA
  • Interests:PRO-IV & Glovia application

Posted 19 January 2005 - 04:18 AM

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.

#5 bmoussa

bmoussa

    Newbie

  • Members
  • Pip
  • 2 posts
  • Gender:Male
  • Location:Chicago, Illinois, USA
  • Interests:PRO-IV &amp; Glovia application

Posted 19 January 2005 - 04:25 AM

sorry for the typo.

“Alt<” enter ‘c_pi_txt”. should be

“Alt<” enter ‘c_pi.txt”.

Good Luck

Bill

#6 Amy Sibilla

Amy Sibilla

    Member

  • Members
  • PipPip
  • 23 posts
  • Gender:Female
  • Location:Warwick, United States

Posted 19 January 2005 - 03:04 PM

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

#7 Joseph Bove

Joseph Bove

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 756 posts
  • Gender:Male
  • Location:Ramsey, United States

Posted 19 January 2005 - 04:00 PM

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

#8 Stuart Burton

Stuart Burton

    Advanced

  • Members
  • PipPipPip
  • 71 posts
  • Gender:Male
  • Location:Luton, United Kingdom

Posted 20 January 2005 - 12:32 PM

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.

#9 Glenn Meyers

Glenn Meyers

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 221 posts
  • Gender:Male
  • Location:St. Louis, MO, United States
  • Interests:I also raise African Gray Parrots and build hot rod automobiles.

Posted 21 January 2005 - 02:00 PM

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

#10 Vol Yip

Vol Yip

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 393 posts
  • Gender:Male
  • Location:Hong Kong

Posted 23 January 2005 - 12:13 PM

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

#11 mmeneses

mmeneses

    Member

  • Members
  • PipPip
  • 43 posts
  • Gender:Male

Posted 24 January 2005 - 04:24 PM

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

B)
HTH,
mmeneses



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users