Excel to space delimited txt file with 900 characters

[ginny777]

Hi! I am a newbie and have searched for an answer to my question on the messages boards, but don't see one that quite fits.

I will be receiving a file in Excel which contains a column for the first name, last name, a date and an email address (it could possibly have more - street address, etc.- but this is the norm). This file will need to be turned into a space delimited .txt file with no tabs. The text file will need 42 "columns" each a specific length for a total of 900 characters in each row. A lot of the columns will be blank, but the spaces must be there, and some of the columns will need to be populated with the same default data in each cell.

Some help with this would be greatly appreciated, right now I'm doing this by hand, but we will have thousands in upcoming files, and it will be too time consuming!

Extra help - one of the columns will need to be a ascending number sequence - 11 characters and prepadded with zeros (00000000001,00000000002 etc.), and the date column will need to be in CCYY-MM-DD (2011-04-08).

Ginny

 

[RSXchin]

why no tabs?
does each column have to be the same length?
if they do, what if some columns are too long?

actually, you said each column was a specific length. what are they?

 

[ginny777]

The text file will be going directly into a database that cannot handle tabs.
Each column is an exact length, varying from 1 to 283. Example: when you open the txt file, each email address has to start at column 277.

Here is a sample, (it goes all the way to 900):
<table x:str="" style="border-collapse: collapse; width: 260pt;" width="346" border="0" cellpadding="0" cellspacing="0"><col style="width: 37pt;" width="49"> <col style="width: 27pt;" width="36"> <col style="width: 48pt;" width="64"> <col style="width: 148pt;" width="197"> <tbody><tr style="height: 15.75pt;" height="21"> <td class="xl25" style="height: 15.75pt; width: 37pt;" width="49" height="21">START</td> <td class="xl25" style="border-left: medium none; width: 27pt;" width="36">END</td> <td class="xl25" style="border-left: medium none; width: 48pt;" width="64">LENGTH</td> <td class="xl25" style="border-left: medium none; width: 148pt;" width="197">FIELD NAMES</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" x:num="" height="17">1</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">TRAN-TYPE</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" x:num="" height="17">2</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">11</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">10</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">PHONE-NBR</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" x:num="" height="17">12</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">41</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">30</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">LAST-NAME</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" x:num="" height="17">42</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">71</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">30</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">FIRST-NAME</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" x:num="" height="17">72</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">72</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">MI</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" x:num="" height="17">73</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">112</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">40</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">ADDRESS-1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" x:num="" height="17">113</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">152</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">40</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">ADDRESS-2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" x:num="" height="17">153</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">192</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">40</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">ADDRESS-3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" x:num="" height="17">193</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">222</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">30</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">CITY</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" x:num="" height="17">223</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">225</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">3</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">ST</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" x:num="" height="17">226</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">231</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">6</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">ZIP</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" x:num="" height="17">232</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">235</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">4</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">ZIP4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" x:num="" height="17">236</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">244</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">9</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">SSN</td> </tr> </tbody></table>

 

[ginny777]

I will only be able to check on this thread off and on, but please know any help is appreciated!
Ginny

 

[RSXchin]

I need all of the column widths IN ORDER!!!!! (separated by comma's in a list please) I should be able to come up with just what you need by lunch tomorrow. Unless someone else beats me to it.

 

[RSXchin]

Also, what are the file naming conventions?

 

[ginny777]

1,10,30,30,1,40,40,40,30,3,6,4,9,1,1,3,10,10,1,1,1,1,1,2,128,10,10,6,10,283,10,25,9,10,25,1,4,11,10,2,30,40

 

[ginny777]

I just have to have the date somewhere in the name, and it must be a .txt file.

 

[ginny777]

Thanks.

 

[RSXchin]

where is the 00000000001 and the date?