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 

Removing Third Value in CSV File ? how to ?
Goto page 1, 2  Next
 
Post new topic   Reply to topic    AutoHotkey Community Forum Index -> Ask for Help
View previous topic :: View next topic  
Author Message
Shalow
Guest
PostPosted: Mon Jun 30, 2008 6:35 pm    Post subject: Removing Third Value in CSV File ? how to ? Reply with quote
I have the following file :

1,2,3,4
1,2,3,4

I want to remove all the ,3 and keep it

1,2,4
1,2,4


How do I do this with autohotkey, because the ,3 is very variable, so the search replace function doesn't work.
Back to top
totana
Guest
PostPosted: Mon Jun 30, 2008 6:57 pm    Post subject: Reply with quote
try this:
Code:
f1= File.csv
f2 = NewFile.csv
filedelete, %F2%
loop,read,%F1%
{
stringsplit,BX,A_LoopReadLine,`,
fileappend, %bx1%`,%bx2%`,%bx4%`n, %F2%
}


(tested)
Back to top
Shalow
Guest
PostPosted: Tue Jul 01, 2008 4:15 pm    Post subject: Reply with quote
I'm trying to understand the structure of the coding.

why not filedelete, f2.
why the %% ?

also i understand that the program should loop read the file so that every line is read and should write the new file.

the %bx4%`n (the n is to have a line break if the original file does not have any, right ?)

also, I don't understand this part :
A_LoopReadLine,`,

where do we set the delimiter ? let's say it's a TAB, or a ; will this line change to

A_LoopReadLine,;, ?
Back to top
SKAN



Joined: 26 Dec 2005
Posts: 6084
PostPosted: Tue Jul 01, 2008 4:21 pm    Post subject: Reply with quote
Quote:
why not filedelete, f2.
why the %% ?


If not for %%, FileDelete command would look for a file named F2 ( without extension ),


Quote:
also, I don't understand this part :
A_LoopReadLine,`,

where do we set the delimiter ? let's say it's a TAB, or a ; will this line change to

A_LoopReadLine,;, ?


Code:
stringsplit,BX,A_LoopReadLine, %A_Tab%


Smile
_________________
Back to top
View user's profile Send private message
Shalow
Guest
PostPosted: Tue Jul 01, 2008 4:24 pm    Post subject: Reply with quote
Also, is there a way to "Clean" a CSV file ?

meaning, I have :

CSV TITLE
Break
1,2,3,4
1,2,3,4
Break

and I want to remove the First and last break, and remove anything that doesn't have the 1,2,3,4 scheme, (CSV TITLE) here.
Back to top
SKAN



Joined: 26 Dec 2005
Posts: 6084
PostPosted: Tue Jul 01, 2008 4:25 pm    Post subject: Reply with quote
Could you post a small sample of the real CSV ?
_________________
Back to top
View user's profile Send private message
Shalow
Guest
PostPosted: Tue Jul 01, 2008 4:26 pm    Post subject: Reply with quote
thank you SKAN, so when the delimiter is a "," you should place a ' in front of it ? so that the script doesn't recognize it as a function ?
Back to top
SKAN



Joined: 26 Dec 2005
Posts: 6084
PostPosted: Tue Jul 01, 2008 4:30 pm    Post subject: Reply with quote
Quote:
thank you SKAN, so when the delimiter is a "," you should place a ' in front of it ? so that the script doesn't recognize it as a function ?


You have used single quote: '
whereas, it should be back-tick: `

Back-tick is usually located right below the <Esc> key in normal keyboards.

and yes ... you need to use the backtick for comma else it would be interpreted as empty parameter.

Smile
_________________
Back to top
View user's profile Send private message
Shalow
Guest
PostPosted: Tue Jul 01, 2008 4:30 pm    Post subject: Reply with quote
Also, will this work ?

fileappend, %bx1%`,%bx2%`,%bx4%`n, newfile.csv

I don't see the necessity to have two variables here, f1 and F2, when you can play with the files directly.

And last, but not least, will

fileappend, %bx1%`,%bx2%`,%bx4%,NewEntry,`n, newfile.csv

will this append a NewEntry value to all the end of my Csv file ?
Back to top
SKAN



Joined: 26 Dec 2005
Posts: 6084
PostPosted: Tue Jul 01, 2008 4:33 pm    Post subject: Reply with quote
Shalow wrote:
Also, will this work ?

Code:
fileappend, %bx1%`,%bx2%`,%bx4%`n, newfile.csv


Yes it would.. but for sake of clarity, I would do it as

Code:
fileappend, % bx ","  bx2 "," bx4 "`n", newfile.csv


Quote:
And last, but not least, will

fileappend, %bx1%`,%bx2%`,%bx4%,NewEntry,`n, newfile.csv

will this append a NewEntry value to all the end of my Csv file ?


I do not get it.. Can you rephrase your question?

Smile
_________________
Back to top
View user's profile Send private message
Shalow
Guest
PostPosted: Tue Jul 01, 2008 4:38 pm    Post subject: Reply with quote
I don't understand how this is more vivid, why is it clearer ?

SKAN wrote:
Yes it would.. but for sake of clarity, I would do it as

Code:
fileappend, % bx ","  bx2 "," bx4 "`n", newfile.csv


what I want is to remove the number 3 value, but also ADD some text at the end of each line. Here, I wanted to add "NewEntry"
Back to top
Shalow
Guest
PostPosted: Tue Jul 01, 2008 4:43 pm    Post subject: Reply with quote
SKAN wrote:
Could you post a small sample of the real CSV ?


=======================================
Compte Commun solde 13/06/08 267,19

13/06/08;PRELEVEMENT;-61,96
14/06/08;facture;-32,67

=======================================

there, I would like to remove the first two lines, and the last break. but sometimes the title is on the third line, and sometimes, there is no break at the end. So I cannot linedelete the first two and last one because sometimes, this would remove valuable data.
Back to top
SKAN



Joined: 26 Dec 2005
Posts: 6084
PostPosted: Tue Jul 01, 2008 4:53 pm    Post subject: Reply with quote
Shalow wrote:
I don't understand how this is more vivid, why is it clearer ?


Too much of % symbols and the tiny backticks affects readability, atleast for me. You may use whatever style you deem fit. Smile

Shalow wrote:
what I want is to remove the number 3 value, but also ADD some text at the end of each line. Here, I wanted to add "NewEntry"


Sure .. you have applied it correct!

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



Joined: 26 Dec 2005
Posts: 6084
PostPosted: Tue Jul 01, 2008 5:00 pm    Post subject: Reply with quote
Shalow wrote:
I would like to remove the first two lines, and the last break. but sometimes the title is on the third line, and sometimes, there is no break at the end. So I cannot linedelete the first two and last one because sometimes, this would remove valuable data.


You could ignore those line by checking whether:
A_LoopReadline contains a common phrase like Compte
String Length of A_LoopReadLine is lesser than two characters

for simulation sake, I am using a parsing loop in the following code and you have to adapt it for your needs:

Code:
CSV=
(
Compte Commun solde 13/06/08 267,19

13/06/08;PRELEVEMENT;-61,96
14/06/08;facture;-32,67
)

Loop, Parse, CSV, `n, `r
{
   If ( InStr( A_LoopField, "Compte" ) OR StrLen( A_LoopField ) < 3 )
      Continue
   MsgBox, % A_LoopField
}



Smile
_________________
Back to top
View user's profile Send private message
Shalow
Guest
PostPosted: Tue Jul 01, 2008 6:47 pm    Post subject: Reply with quote
hummm... you have to incorporate the hole csv file into the script like above ?

also, what if one of the lines contain the word "compte" ?

can't we exclude the lines that do not have 4 commas in them ? bx1,bX2,bx3,bx4,NewText ?
Back to top
Display posts from previous:   
Post new topic   Reply to topic    AutoHotkey Community Forum Index -> Ask for Help All times are GMT
Goto page 1, 2  Next
Page 1 of 2

 
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