<br><font size=2 face="sans-serif">Hi Jeff,</font>
<br>
<br><font size=2 face="sans-serif">I like the idea about converting to
a fixed lenght, <u>how do you do that</u>?</font>
<br>
<br><font size=2 face="sans-serif">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).</font>
<br>
<br><font size=2 face="sans-serif">Option Explicit</font>
<br>
<br><font size=2 face="sans-serif">Sub MakeFixedWidth()</font>
<br><font size=2 face="sans-serif">Dim MyStr As String, PageName As String,
FirstRow As Integer, LastRow As Integer, MyRow As Integer</font>
<br>
<br><font size=2 face="sans-serif">PageName = "C:\S6291\FixedWidth_"
& Format(Time, "HHMM") & ".txt" ' location
and name of saved file</font>
<br><font size=2 face="sans-serif">FirstRow = Range("B1").Value
' the range of the table to be exported</font>
<br><font size=2 face="sans-serif">LastRow = FirstRow + Range("D1").Value
- 1</font>
<br>
<br><font size=2 face="sans-serif">Open PageName For Output As #1</font>
<br><font size=2 face="sans-serif">For MyRow = FirstRow To LastRow ' loop
through each row of the table</font>
<br><font size=2 face="sans-serif"> MyStr = ""</font>
<br><font size=2 face="sans-serif"> MyStr = Cells(MyRow, 1).Value
& String(6 - Len(Cells(MyRow, 1).Value), " ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & String(7 -
Len(Cells(MyRow, 2).Value), " ") & Cells(MyRow, 2).Value</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & " "
& Format(Cells(MyRow, 4).Value * 100, "0000")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & " "
& Cells(MyRow, 5).Value & String(13 - Len(Cells(MyRow, 5).Value),
" ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Cells(MyRow,
6).Value & String(1 - Len(Cells(MyRow, 6).Value), " ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Cells(MyRow,
7).Value & String(2 - Len(Cells(MyRow, 7).Value), " ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Cells(MyRow,
8).Value & String(35 - Len(Cells(MyRow, 8).Value), " ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Cells(MyRow,
9).Value & String(40 - Len(Cells(MyRow, 9).Value), " ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Cells(MyRow,
10).Value & String(10 - Len(Cells(MyRow, 10).Value), " ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Cells(MyRow,
11).Value & String(3 - Len(Cells(MyRow, 11).Value), " ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Cells(MyRow,
12).Value & String(3 - Len(Cells(MyRow, 12).Value), " ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Cells(MyRow,
13).Value & String(10 - Len(Cells(MyRow, 13).Value), " ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Cells(MyRow,
14).Value & String(4 - Len(Cells(MyRow, 14).Value), " ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Format(Cells(MyRow,
15).Value , "00000000000.00") & " "</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & "HO"
& Cells(MyRow, 16).Value & String(7 - Len(Cells(MyRow, 16).Value),
" ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Cells(MyRow,
17).Value & String(1 - Len(Cells(MyRow, 17).Value), " ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Cells(MyRow,
18).Value & String(1 - Len(Cells(MyRow, 18).Value), " ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Format(Cells(MyRow,
19).Value * 100, "0000000000000") & " "</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Cells(MyRow,
20).Value & String(1 - Len(Cells(MyRow, 20).Value), " ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Cells(MyRow,
21).Value & String(11 - Len(Cells(MyRow, 21).Value), " ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Cells(MyRow,
22).Value & String(1 - Len(Cells(MyRow, 22).Value), " ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Format(Cells(MyRow,
23).Value, "000000000.0000") & " "</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Cells(MyRow,
24).Value & String(10 - Len(Cells(MyRow, 24).Value), " ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Cells(MyRow,
25).Value & String(10 - Len(Cells(MyRow, 25).Value), " ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Cells(MyRow,
26).Value & String(1 - Len(Cells(MyRow, 26).Value), " ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Cells(MyRow,
27).Value & String(1 - Len(Cells(MyRow, 27).Value), " ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & "1"
& Cells(MyRow, 28).Value & String(7 - Len(Cells(MyRow, 28).Value),
" ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Cells(MyRow,
29).Value & String(7 - Len(Cells(MyRow, 29).Value), " ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Cells(MyRow,
30).Value & String(1 - Len(Cells(MyRow, 30).Value), " ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Cells(MyRow,
31).Value & String(2 - Len(Cells(MyRow, 31).Value), " ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Cells(MyRow,
32).Value & String(1 - Len(Cells(MyRow, 32).Value), " ")</font>
<br><font size=2 face="sans-serif"> MyStr = MyStr & Cells(MyRow,
33).Value & String(3 - Len(Cells(MyRow, 33).Value), " ")</font>
<br><font size=2 face="sans-serif">' MyStr = MyStr & Format(Cells(MyRow,
7).Value, "0000000.00")</font>
<br><font size=2 face="sans-serif">Print #1, MyStr</font>
<br><font size=2 face="sans-serif">Next</font>
<br><font size=2 face="sans-serif">Close #1</font>
<br><font size=2 face="sans-serif">Sheets("ok wk").Range("G1").ClearContents
' Place
the filename with the path</font>
<br><font size=2 face="sans-serif">Sheets("ok wk").Hyperlinks.Add
Range("G1"), PageName '</font>
<br><font size=2 face="sans-serif">End Sub</font>
<br>
<br>
<br>
<br>
<br>
<table width=100%>
<tr valign=top>
<td><font size=4 face="helv"><b>Internet</b></font>
<br><font size=1 face="sans-serif"><b>wonicon@optusnet.com.au</b></font>
<br>
<p><font size=1 face="sans-serif"><b>Sent by: powerh-l-bounces+nilesh.patel=bnpparibas.com@lists.sowder.com</b></font>
<p><font size=1 face="sans-serif">02/03/2007 14:16</font>
<td>
<table width=100%>
<tr>
<td>
<div align=right><font size=1 face="sans-serif">To</font></div>
<td valign=top><font size=1 face="sans-serif">powerh-l</font>
<tr>
<td>
<div align=right><font size=1 face="sans-serif">cc</font></div>
<td valign=top>
<tr>
<td>
<div align=right><font size=1 face="sans-serif">Subject</font></div>
<td valign=top><font size=1 face="sans-serif">Re: Importing data from an
Excel Sheet to Powerhouse</font></table>
<br>
<table>
<tr valign=top>
<td>
<td></table>
<br></table>
<br>
<br>
<br><font size=2><tt>Hi<br>
</tt></font>
<br><font size=2><tt>You could convert the file to fixed length, define
it in the<br>
dictionary, then use qtp to read each record and convert each field<br>
to fixed length outputing to a subfile.<br>
</tt></font>
<br><font size=2><tt>Another option is to use bcp to load the data into
an rdbms. On the<br>
fly you could create a table, load the data, extract to a subfile if<br>
necessary when finished you can drop the table. If the table exists<br>
then something went wrong with the previous load.<br>
</tt></font>
<br><font size=2><tt>Jeff<br>
</tt></font>
<br><font size=2><tt>At 06:51 PM 2/03/2007, you wrote:<br>
>Hello All,<br>
><br>
> I've a problem importing data from Excel sheets to power house
format.<br>
> Most of the times, users send data in XLS format, if its a matter
of<br>
> 10-20 records, we could do it manually,<br>
> but its impossible when its a matter 1000 and above records,<br>
><br>
> Please suggest a way to do this.<br>
><br>
> Thanks and Regards,<br>
> K Vamsi Krishna<br>
><br>
>--<br>
>"Nothing is impossible as impossible itself says i m possible"<br>
>--<br>
>= = = = = = = = = = = = = = = = = = = = = = = = = = = =<br>
>Mailing list: powerh-l@lists.sowder.com<br>
>Subscribe: &quot;subscribe&quot; in message body to<br>
>powerh-l-request@lists.sowder.com<br>
>Unsubscribe: &quot;unsubscribe &lt;password&gt;&quot;
in message<br>
>body to powerh-l-request@lists.sowder.com<br>
>http://lists.sowder.com/mailman/listinfo/powerh-l<br>
>This list is closed, thus to post to the list you must be a subscriber.<br>
>Add 'site:lists.sowder.com powerh-l' to your search terms to search<br>
>the list archive at Google.<br>
</tt></font>
<br><font size=2><tt>--<br>
= = = = = = = = = = = = = = = = = = = = = = = = = = = =<br>
Mailing list: powerh-l@lists.sowder.com<br>
Subscribe: &quot;subscribe&quot; in message body to powerh-l-request@lists.sowder.com<br>
Unsubscribe: &quot;unsubscribe &lt;password&gt;&quot; in
message body to powerh-l-request@lists.sowder.com<br>
http://lists.sowder.com/mailman/listinfo/powerh-l<br>
This list is closed, thus to post to the list you must be a subscriber.</tt></font>
<br><font size=2><tt>Add 'site:lists.sowder.com powerh-l' to your search
terms to search the list archive at Google.</tt></font>
<br><pre>
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.
</pre>