Importing data from an Excel Sheet to Powerhouse

Sun Mar 4 08:16:05 CST 2007

Hi Nilesh

Depends upon the operating system, the code below looks like VMS so 
you do not have to actually convert to fixed length, just define a 
variable length file size greater than the largest record you should 
receive in any particular file.

For MPE you can use the editor, for window/dos we have a utility 
program, for Unix you can use awk (I think or is it sed or dd?)

Jeff

At 12:55 AM 3/03/2007, you wrote:

>Hi Jeff,
>
>I like the idea about converting to a fixed lenght, how do you do that?
>
>At present, I am in a process of converting a large excel file with 
>lots of fields and I used the following macro to create a flat file 
>( I found a example macro on the net).
>
>Option Explicit
>
>Sub MakeFixedWidth()
>Dim MyStr As String, PageName As String, FirstRow As Integer, 
>LastRow As Integer, MyRow As Integer
>
>PageName = "C:\S6291\FixedWidth_" & Format(Time, "HHMM") & ".txt" ' 
>location and name of saved file
>FirstRow = Range("B1").Value ' the range of the table to be exported
>LastRow = FirstRow + Range("D1").Value - 1
>
>Open PageName For Output As #1
>For MyRow = FirstRow To LastRow ' loop through each row of the table
>  MyStr = ""
>  MyStr = Cells(MyRow, 1).Value & String(6 - Len(Cells(MyRow, 1).Value), " ")
>  MyStr = MyStr & String(7 - Len(Cells(MyRow, 2).Value), " ") & 
> Cells(MyRow, 2).Value
>  MyStr = MyStr & " " & Format(Cells(MyRow, 4).Value * 100, "0000")
>  MyStr = MyStr & " " & Cells(MyRow, 5).Value & String(13 - 
> Len(Cells(MyRow, 5).Value), " ")
>  MyStr = MyStr & Cells(MyRow, 6).Value & String(1 - 
> Len(Cells(MyRow, 6).Value), " ")
>  MyStr = MyStr & Cells(MyRow, 7).Value & String(2 - 
> Len(Cells(MyRow, 7).Value), " ")
>  MyStr = MyStr & Cells(MyRow, 8).Value & String(35 - 
> Len(Cells(MyRow, 8).Value), " ")
>  MyStr = MyStr & Cells(MyRow, 9).Value & String(40 - 
> Len(Cells(MyRow, 9).Value), " ")
>  MyStr = MyStr & Cells(MyRow, 10).Value & String(10 - 
> Len(Cells(MyRow, 10).Value), " ")
>  MyStr = MyStr & Cells(MyRow, 11).Value & String(3 - 
> Len(Cells(MyRow, 11).Value), " ")
>  MyStr = MyStr & Cells(MyRow, 12).Value & String(3 - 
> Len(Cells(MyRow, 12).Value), " ")
>  MyStr = MyStr & Cells(MyRow, 13).Value & String(10 - 
> Len(Cells(MyRow, 13).Value), " ")
>  MyStr = MyStr & Cells(MyRow, 14).Value & String(4 - 
> Len(Cells(MyRow, 14).Value), " ")
>  MyStr = MyStr & Format(Cells(MyRow, 15).Value , "00000000000.00") & " "
>  MyStr = MyStr & "HO" & Cells(MyRow, 16).Value & String(7 - 
> Len(Cells(MyRow, 16).Value), " ")
>  MyStr = MyStr & Cells(MyRow, 17).Value & String(1 - 
> Len(Cells(MyRow, 17).Value), " ")
>  MyStr = MyStr & Cells(MyRow, 18).Value & String(1 - 
> Len(Cells(MyRow, 18).Value), " ")
>  MyStr = MyStr & Format(Cells(MyRow, 19).Value * 100, "0000000000000") & " "
>  MyStr = MyStr & Cells(MyRow, 20).Value & String(1 - 
> Len(Cells(MyRow, 20).Value), " ")
>  MyStr = MyStr & Cells(MyRow, 21).Value & String(11 - 
> Len(Cells(MyRow, 21).Value), " ")
>  MyStr = MyStr & Cells(MyRow, 22).Value & String(1 - 
> Len(Cells(MyRow, 22).Value), " ")
>  MyStr = MyStr & Format(Cells(MyRow, 23).Value, "000000000.0000") & " "
>  MyStr = MyStr & Cells(MyRow, 24).Value & String(10 - 
> Len(Cells(MyRow, 24).Value), " ")
>  MyStr = MyStr & Cells(MyRow, 25).Value & String(10 - 
> Len(Cells(MyRow, 25).Value), " ")
>  MyStr = MyStr & Cells(MyRow, 26).Value & String(1 - 
> Len(Cells(MyRow, 26).Value), " ")
>  MyStr = MyStr & Cells(MyRow, 27).Value & String(1 - 
> Len(Cells(MyRow, 27).Value), " ")
>  MyStr = MyStr & "1" & Cells(MyRow, 28).Value & String(7 - 
> Len(Cells(MyRow, 28).Value), " ")
>  MyStr = MyStr & Cells(MyRow, 29).Value & String(7 - 
> Len(Cells(MyRow, 29).Value), " ")
>  MyStr = MyStr & Cells(MyRow, 30).Value & String(1 - 
> Len(Cells(MyRow, 30).Value), " ")
>  MyStr = MyStr & Cells(MyRow, 31).Value & String(2 - 
> Len(Cells(MyRow, 31).Value), " ")
>  MyStr = MyStr & Cells(MyRow, 32).Value & String(1 - 
> Len(Cells(MyRow, 32).Value), " ")
>  MyStr = MyStr & Cells(MyRow, 33).Value & String(3 - 
> Len(Cells(MyRow, 33).Value), " ")
>' MyStr = MyStr & Format(Cells(MyRow, 7).Value, "0000000.00")
>Print #1, MyStr
>Next
>Close #1
>Sheets("ok wk").Range("G1").ClearContents                   ' Place 
>the filename with the path
>Sheets("ok wk").Hyperlinks.Add Range("G1"), PageName        '
>End Sub
>
>
>
>
>Internet
>wonicon at optusnet.com.au
>
>Sent by: powerh-l-bounces+nilesh.patel=bnpparibas.com at lists.sowder.com
>
>02/03/2007 14:16
>To
>powerh-l
>cc
>Subject
>Re: Importing data from an Excel Sheet to Powerhouse
>
>
>
>
>Hi
>
>You could convert the file to fixed length, define it in the
>dictionary, then use qtp to read each record and convert each field
>to fixed length outputing to a subfile.
>
>Another option is to use bcp to load the data into an rdbms. On the
>fly you could create a table, load the data, extract to a subfile if
>necessary when finished you can drop the table. If the table exists
>then something went wrong with the previous load.
>
>Jeff
>
>At 06:51 PM 2/03/2007, you wrote:
> >Hello All,
> >
> >  I've a problem importing data from Excel sheets to power house format.
> >  Most of the times, users send data in XLS format, if its a matter of
> >  10-20 records, we could do it manually,
> >  but its impossible when its a matter 1000 and above records,
> >
> >  Please suggest a way to do this.
> >
> >  Thanks and Regards,
> >  K Vamsi Krishna
> >
> >--
> >"Nothing is impossible as impossible itself says i m possible"
> >--
> >= = = = = = = = = = = = = = = = = = = = = = = = = = = =
> >Mailing list: powerh-l at lists.sowder.com
> >Subscribe: &quot;subscribe&quot; in message body to
> >powerh-l-request at lists.sowder.com
> >Unsubscribe: &quot;unsubscribe &lt;password&gt;&quot; in message
> >body to powerh-l-request at lists.sowder.com
> >http://lists.sowder.com/mailman/listinfo/powerh-l
> >This list is closed, thus to post to the list you must be a subscriber.
> >Add 'site:lists.sowder.com powerh-l' to your search terms to search
> >the list archive at Google.
>
>--
>= = = = = = = = = = = = = = = = = = = = = = = = = = = =
>Mailing list: powerh-l at lists.sowder.com
>Subscribe: &quot;subscribe&quot; in message body to 
>powerh-l-request at lists.sowder.com
>Unsubscribe: &quot;unsubscribe &lt;password&gt;&quot; in message 
>body to powerh-l-request at lists.sowder.com
>http://lists.sowder.com/mailman/listinfo/powerh-l
>This list is closed, thus to post to the list you must be a subscriber.
>Add 'site:lists.sowder.com powerh-l' to your search terms to search 
>the list archive at Google.
>
>
>
>This message and any attachments (the "message") is
>intended solely for the addressees and is confidential.
>If you receive this message in error, please delete it and
>immediately notify the sender. Any use not in accord with
>its purpose, any dissemination or disclosure, either whole
>or partial, is prohibited except formal approval. The internet
>can not guarantee the integrity of this message.
>BNP PARIBAS (and its subsidiaries) shall (will) not
>therefore be liable for the message if modified.
>
>                 ---------------------------------------------
>
>Ce message et toutes les pieces jointes (ci-apres le
>"message") sont etablis a l'intention exclusive de ses
>destinataires et sont confidentiels. Si vous recevez ce
>message par erreur, merci de le detruire et d'en avertir
>immediatement l'expediteur. Toute utilisation de ce
>message non conforme a sa destination, toute diffusion
>ou toute publication, totale ou partielle, est interdite, sauf
>autorisation expresse. L'internet ne permettant pas
>d'assurer l'integrite de ce message, BNP PARIBAS (et ses
>filiales) decline(nt) toute responsabilite au titre de ce
>message, dans l'hypothese ou il aurait ete modifie.
>