Importing data from an Excel Sheet to Powerhouse
nilesh.patel at bnpparibas.com
nilesh.patel at bnpparibas.com
Fri Mar 2 07:55:18 CST 2007
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: "subscribe" in message body to
>powerh-l-request at lists.sowder.com
>Unsubscribe: "unsubscribe <password>" 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: "subscribe" in message body to
powerh-l-request at lists.sowder.com
Unsubscribe: "unsubscribe <password>" 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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.sowder.com/pipermail/powerh-l/attachments/20070302/ae33585a/attachment-0001.htm
More information about the powerh-l
mailing list