Hi,

I'm having problems importing a text file. There are no column delimiters in the file. It has the following data:
AcctNo
Street1
Street2
City
State
Zip

because some of the row may or may not fill up to 350 character, I need the powershell script to update the text file with rows that does not have 350 in length
with trailing space to make all the row with 350 character in lenght.

thanks

Can you explain the format of the incoming file in more detail? For example is a row a complete address with variable length:
acctNo Street1 Street2 City State Zip

OR is each item on separate line i.e. each item a separate row?

Also are any elements missing from some but not all records? For example will Street2 be present in every record?

Obviously it would be easier to control the way the output is formatted from the source. Any chance you can have the file created a delimited?

This is a row with complete address with variable length. I'm not worried about the missing elements, but the lost of records. According to the vendor, there were certain amount of record data, but the import seem to be less. After searching, sqlis doesn;t seem to like null value, and seem to overlap the records. I could import as 1 row column into the sql, and split afterward...and I'm kinda heading to that direction.

Is your end goal to split each field (acctno, Street, etc.) into its own column? Or do you simply want to pad a 350 character length address with spaces i.e. meaning total address is within 350 characters?

The end goal is to split each field out. However I need to have all correct number of record count from the vendor. I was looking at this http://agilebi.com/cs/blogs/jwelch/...umns.aspx, and gave me some idea. However this article based on the comma delimiter. The reason I want to fill the space in at the end that I have a small test text file, and insert the correct space in at the end, I was able to load in with ragged right format.

Splitting based on space would seem unreliable for addresses. If all you want to do is pad a full line (record) you can call the padright method

get-content ./myfile.txt | foreach-object {$_.padright(350)}

If you want split each record/line on space you can something like this in PowerShell 2.0:

get-content ./myfile.txt | foreach-object {$_ -split "\s"}

Thank you very much, the Padright did the trick.

Hello,

Thank you in advance. I need to insert a space at the end of each line of a text file. I never use powershell before. I will appreciate if anybody can help me to solve this. From the Powershell command prompt I have executed the above mentioned suggestion:

get-content ./myfile.txt | foreach-object {$_.padright(350)}

It executed but did not insert a space.

Thanks again

Do you just want to insert a single space at the end of each line? If so, this command will insert a single space:

Get-Content .\myfile.txt | foreach {$_ -replace "$"," "}

Thanks cmille19.

I have copied and paste the above command and it executed smoothly but it did not put a space at the end of each line. Am I missing something? As I told, I am a newbie.

thanks

The above command just adds a single whitespace to each line of a text file. You'll need to send the output to a file or update the existing file:

Get-Content .\myfile.txt | foreach {$_ -replace "$"," "} >> mynewfile.txt

or update in place (be careful with this one make sure you really want to do this):

(Get-Content .\myfile.txt) | foreach {$_ -replace "$"," "} | Set-Content myfile.txt

To prove the whitespace is being added and since you can't see the whitespace in the console you could run something like this, which will add a dash to the end of each line:

Get-Content .\myfile.txt | foreach {$_ -replace "$","-"}

Hi cmille19,
Thanks a lot for helping me. Lot of things to know...............