AutoHotkey Homepage AutoHotkey Community
Let's help each other out
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

aligning columns in a text file

 
Post new topic   Reply to topic    AutoHotkey Community Forum Index -> Ask for Help
View previous topic :: View next topic  
Author Message
edu



Joined: 12 Oct 2006
Posts: 88
Location: Canada
PostPosted: Wed Dec 19, 2007 6:37 pm    Post subject: aligning columns in a text file Reply with quote
Hello, forum! I'm challenged with a problem about a plain text file. I prepare spreadsheets in excel and convert them to a space delimited (fixed width) text file so that our accounting program can import, say, a price list. The problem is that I find hard to create uniformity in excel, as I have to deal with different spreadsheets, etc. So, joining the files from different spreadsheets into one file for import will look like this

Code:
09FX503     Zero Nine FX - Process Cyan 100yd           133.00    128.00    123.00      1      2      5
09FX502     Zero Nine FX - Process Magenta 100yd        133.00    128.00    123.00      1      2      5
09R206         Zero Nine Roland - Dark Red 75 m.               19.00       17.00       16.00       1       5       10
09R207         Zero Nine Roland - Kelly Green 75 m.            19.00       17.00       16.00       1       5       10


but I would like to have a way to making the spacing and columns uniform like this:

Code:
09FX503     Zero Nine FX - Process Cyan 100yd           133.00    128.00    123.00      1      2      5
09FX502     Zero Nine FX - Process Magenta 100yd        133.00    128.00    123.00      1      2      5
09R206      Zero Nine Roland - Dark Red 75 m.            19.00     17.00     16.00      1      5     10
09R207      Zero Nine Roland - Kelly Green 75 m.         19.00     17.00     16.00      1      5     10


Any ideas how to do it? Converting to comma separated (csv) first and then spaces would work? But then, how from there?

Note: The browser is displaying the text wrapped

Thanks in advance,

Edu
_________________
The best things of life are free.
Back to top
View user's profile Send private message
tic



Joined: 22 Apr 2007
Posts: 1355
PostPosted: Wed Dec 19, 2007 7:01 pm    Post subject: Reply with quote
put commas between the columns in each row and then stringsplit. find the strlen of each part of the array and then pad it out with however many extra spaces
Back to top
View user's profile Send private message
edu_guest
Guest
PostPosted: Thu Dec 20, 2007 7:05 pm    Post subject: Reply with quote
Thanks, I will study that.

Edu
Back to top
SKAN



Joined: 26 Dec 2005
Posts: 5887
PostPosted: Thu Dec 20, 2007 7:53 pm    Post subject: Reply with quote
Code:
unalignedtext=
(
09FX503     Zero Nine FX - Process Cyan 100yd           133.00    128.00    123.00      1      2      5
09FX502     Zero Nine FX - Process Magenta 100yd        133.00    128.00    123.00      1      2      5
09R206         Zero Nine Roland - Dark Red 75 m.               19.00       17.00       16.00       1       5       10
09R207         Zero Nine Roland - Kelly Green 75 m.            19.00       17.00       16.00       1       5       10
)

StringReplace,unalignedtext,unalignedtext, %A_Space%%A_Space%, `, , All
CSV := "", Quote := """" , Comma := ","
Loop, Parse, unalignedtext, `n, `r
{
  Loop, Parse, A_LoopField, CSV
    {
      LoopField := RegExReplace( A_LoopField, "(^\s*|\s*$)") ; AllTrim()
      CSV .= (  ( LoopField <> "" ) ? Quote LoopField Quote Comma : "" ) 
    }
StringTrimRight, CSV, CSV, 1
CSV .= "`n"
}

MsgBox, % CSV


Catch: A column item should never contain sdouble spaces, and if it did, would be seperated as different columns.

Smile
Back to top
View user's profile Send private message
garry



Joined: 19 Apr 2005
Posts: 1033
Location: switzerland
PostPosted: Fri Dec 21, 2007 8:53 am    Post subject: Reply with quote
thank you tic and SKAN
added a script from BoBo

Code:
F1=test1.txt
ifexist,%F1%
  filedelete,%F1%

F2=test2.txt
ifexist,%F2%
  filedelete,%F2%

;-------------- 1st part set delimiter comma
;-------- http://www.autohotkey.com/forum/post-167400.html#167400
;-------- SKAN
unalignedtext=
(
09FX503    Zero Nine FX - Process Cyan 100yd            133.00     128.00    123.00      1      2      5
09FX502     Zero Nine FX - Process Magenta 100yd        133.00    128.00    123.00      1      2      5
09R206         Zero Nine Roland - Dark Red 75 m.               19.00       17.00       16.00       1       5       10
09R207         Zero Nine Roland - Kelly Green 75 m.            19.00       17.00       16.00       1       5       10
)

StringReplace,unalignedtext,unalignedtext, %A_Space%%A_Space%, `, , All
;CSV := "", Quote := """" , Comma := ","
Comma := ","
Loop, Parse, unalignedtext, `n, `r
{
  Loop, Parse, A_LoopField, CSV
    {
      LoopField := RegExReplace( A_LoopField, "(^\s*|\s*$)") ; AllTrim()
      CSV .= (  ( LoopField <> "" ) ? Quote LoopField Quote Comma : "" )
    }
StringTrimRight, CSV, CSV, 1
;CSV .= "`n"
Fileappend,%CSV%`r`n,%F1%
CSV=
}

;----------- 2nd part ---------------------------
transform,S,chr,32
TAB1 =14
TAB2 =38
TAB3 =12
TAB4 =12
TAB5 =12
TAB6 =6
TAB7 =6
TAB8 =6


loop,read,%F1%
 {
  LR=%A_loopReadLine%
  stringsplit,C,LR,`,,
  C1 := LP(C1,TAB1,S,"L")
  C2 := LP(C2,TAB2,S,"L")
  C3 := LP(C3,TAB3,S,"R")
  C4 := LP(C4,TAB4,S,"R")
  C5 := LP(C5,TAB5,S,"R")
  C6 := LP(C6,TAB6,S,"R")
  C7 := LP(C7,TAB7,S,"R")
  C8 := LP(C8,TAB8,S,"R")
  Fileappend,%C1% %C2% %C3% %C4% %C5% %C6% %C7% %C8%`r`n,%F2%
  }
;---------- F2 textfile used non proportional script like FixedSys ----------
run,%F2%
return



;--------------------------- script von BoBo ---------------------------------
;LinePadding("Text",20,A_Space,"L")

;1. Parameter: Text/Wert welcher in der Zeile übergeben wird        WORD
;2. Parameter: Die Gesamtlänge der Zeile, hier 20 Zeichen           LENGHT
;3. Parameter: Füllzeichen, hier A_Space (das Leerzeichen)          FILL
;4. Parameter: Die Ausrichtung, hier L (heisst Linksbündig)         LEFT / RIGHT

;A:= LP(A,TAB1,S,"R")
;B:= LP(B,TAB1,S,"R")

;Line := LinePadding("TestString",20,A_Space,"L")
;MsgBox |%Line%| ; zu Testzwecken

;-------------------------------------------
LP(String,FieldLen,ToAppend,Justification)
 {
   StringLen, StringLen, String ; Anzahl Zeichen der Zeichenkette ermitteln
   LCnt := FieldLen-StringLen ; Anzahl Füllzeichen ermitteln
   Loop, % LCnt
     Appended := (Appended . ToAppend) ; Auffüllen
   If Justification = R
      Return (Appended . String) ; Linksbündig
   If Justification = L
         Return (String . Appended) ; Rechtsbündig
 }
;----------------------------------------------------------------------------------
Back to top
View user's profile Send private message
tic



Joined: 22 Apr 2007
Posts: 1355
PostPosted: Fri Dec 21, 2007 12:33 pm    Post subject: Reply with quote
probably a nicer way would be to use:

Code:
FileRead, File, 1.txt
StringReplace, File, File, % "  ", `,, All

Loop, Parse, File, `n
{
   Line := A_Index   
   StringSplit, %Line%Line, A_LoopField, `,

   Loop, % %Line%Line0
   {
      If %Line%Line%A_Index%
      {
         If (SubStr(%Line%Line%A_Index%,1,1) = " ")
         StringTrimLeft, %Line%Line%A_Index%, %Line%Line%A_Index%, 1
         
         If (A_Index != %Line%Line0)
         FileAppend, % %Line%Line%A_Index% "`t`t", 2.txt
         Else
         FileAppend, % %Line%Line%A_Index% "`n", 2.txt
      }
   }
}


1.txt is the messy one in and 2.txt is the neat one out.
Back to top
View user's profile Send private message
garry



Joined: 19 Apr 2005
Posts: 1033
Location: switzerland
PostPosted: Fri Dec 21, 2007 1:28 pm    Post subject: Reply with quote
thank you tic, good example to keep for my small scripts example
with BoBo's function it's possible to add width for each column and left/right justification , in this case should be text left , digits right
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    AutoHotkey Community Forum Index -> Ask for Help All times are GMT
Page 1 of 1

 
Jump to:  
You can post new topics in this forum
You can reply to topics in this forum


Powered by phpBB © 2001, 2005 phpBB Group