Separate CSV values with count from text file process variable

[randallf]

Hello fellow .MEXiCANs...

 

I am using text file process to pull data, I have several formats I can save the data in and the resulting lines are something like this:

 

18172865-0413,owner,Open-Dispatch,OPN-Pending,Help Desk Level 1,Site Name,P5-NoRush@Cust Reqst,P4 - Standard,Userlast,Userfirst,BIG LONG CASE TITLE STRING,90111116,,693379621,R045824020

 

What I want to do is be able to grab individual segments out of this. Currently I have tried using non-csv (just text) formatting and the following sort of loop:

 

<REM2:Case ID><TMVAR2:10:02:01:001:013:><REM2:Status><TMVAR2:10:03:01:023:013:><REM2:Priority><IVAR2:04:13:1:Namename><NMVAR:01:04:1:0000004:2:0000008><TMVAR2:10:04:01:N04:002:>

 

// Case ID

Variable Modify String: Copy Part of %T1% to %T2%

// Status

Variable Modify String: Copy Part of %T1% to %T3%

// Priority

Variable Set Integer %N4% from Position of Text in Variable %T1%

Variable Modify Integer: %N4% = %N4% + 8

Variable Modify String: Copy Part of %T1% to %T4%

 

The problem is with the normal formatting that it comes out with no easy way to scan for the data that I want, however, in CSV format you can see (or already know) that every record has a , (or ,, if the record is blank) and I need a way to count out to a certain , placing and grab the text placed thereafter.

 

Is there a way to use increment/decrement to do this?

 

Has anyone else had success processing variables out of CSV files based on placement?

[randallf]

To clarify a bit more I want to be able to make a loop as follows:

 

Process this CSV(text) file

Go to line 2 and get records 1, 2, 3, 7, 14 and 17

Save these records into variables

Move to another program and enter the data

Text file end process (do it all again until no more lines are available)

 

I am trying to move away from screen controls to do my data mining as it's so much faster with text file process...

[randallf]

hrm... Is there just a way to take a line in a CSV such as:

 

data1,data2,data3,data four4,,more data,,,stuff,lol = lulz

 

And copy each value into T2, T3, T4, T5, T6, etc?

 

I was working on something weird like (VERY unfinished)

 

 

Text file begin process (line 2 into T1)

Set integer from position of text in variable: , (N1) [this gives us the position of the first comma]

Variable modify string: copy part of T1 into T21(case number) from pos zero to N1

N2 = N1

Repeat until N1 > 200 [sets the limit for how far down a line it should scan]

LET N1 = N1 + 1

Variable Modify String: Copy part of T1 (single char @ pos.N1) into T2

IF VAR T2 = ,

	LET N3 = N2 	[this gives us the stopping point, N3 is always the last stopping point]

	Variable Modify String: Copy part of T1 into some variable (from 

	[this moves us to the next position after the comma]

ELSE

	LET N2 = N2 + 1

 

 

 

And then I got lost

 

I think I could write the loop to continously push things into a new variable but how do I get it to step the variable up each time it runs the loop, like use T1 and then T2 and then T2...

 

:unsure:

 

I need a brilliant guru programmer to help me out here! Huge thanks in advance to anyone who takes the time here...

 

-Rand

[kevin]

Check out the ASCII File Begin Process and ASCII File End Process commands. There is an example in the help.

[stevecasper]

hrm... Is there just a way to take a line in a CSV such as:

 

data1,data2,data3,data four4,,more data,,,stuff,lol = lulz

 

And copy each value into T2, T3, T4, T5, T6, etc?

 

I was working on something weird like (VERY unfinished)

 

 

Text file begin process (line 2 into T1)

Set integer from position of text in variable: , (N1) [this gives us the position of the first comma]

Variable modify string: copy part of T1 into T21(case number) from pos zero to N1

N2 = N1

Repeat until N1 > 200 [sets the limit for how far down a line it should scan]

LET N1 = N1 + 1

Variable Modify String: Copy part of T1 (single char @ pos.N1) into T2

IF VAR T2 = ,

	LET N3 = N2 	[this gives us the stopping point, N3 is always the last stopping point]

	Variable Modify String: Copy part of T1 into some variable (from 

	[this moves us to the next position after the comma]

ELSE

	LET N2 = N2 + 1

 

 

 

And then I got lost

 

I think I could write the loop to continously push things into a new variable but how do I get it to step the variable up each time it runs the loop, like use T1 and then T2 and then T2...

 

:unsure:

 

I need a brilliant guru programmer to help me out here! Huge thanks in advance to anyone who takes the time here...

 

-Rand

First, definitely do what Kevin suggested.

 

Second, I found ASCII File Processes to make a lot more sense after I played around with them a little while, creating unimportant documents in different formats, processing them as comma delimited and then as tab delimited. Make some simplistic files only 2 or 3 lines long, use lots of Text Box displays in your test macro and get a really good feel for how ASCII File Processes work, and that will probably really help you out.

 

Based on what I understand you to have said, you're probably going to benefit most from using Comma Delimited.

 

Each piece of data on each line of your document will get saved automatically to the next variable. Example:

 

Assuming your .txt file looks something like this:

 

data1,data2,data3

data4,data5,data6

 

Using an ASCII File Process loop to process this, and assuming you set %T1% as the variable to receive the information, the first time through the loop your variables will look like this:

 

T1 = data1

T2 = data2

T3 = data3

 

The next time through the loop:

 

T1 = data4

T2 = data5

T3 = data6

 

I hope this helps!

[randallf]

HAH! I had no idea that ascii file process had CSV in it... going to be a huge help. I know what you mean by text box displays, I use those all the time to test things out and show me how the var's are moving along.

 

Thanks for pointing me in the right direction!!! I think this nearly completes my basic knowledge of ME... maybe not my skill in writing things or using it but I've touched nearly everything now in some form or other and written hundreds of macros, what a great program this is... I probably literally don't have CTS because of MEX.

 

I wish I could get a job doing this

[randallf]

Again HAH... so easy! Thanks everyone!

 

Text Type: <ALTD>f<ALTU>v

Text Type: c:\macro temp\csv.csv

Text Type: <TAB>c<TAB><SPACE>

Wait for File Ready: "csv.csv"

ASCII File Begin Process: "csv.csv" (Comma Delimited Text )

Text Box Display: tester

ASCII File End Process

Delete File or Files: "csv.csv"

 

<TEXTTYPE:<ALTD>f<ALTU>v><TEXTTYPE:c:\macro temp\csv.csv><TEXTTYPE:<TAB>c<TAB><SPACE>><WFREADY:000010:000000:000000c:\macro temp\csv.csv><ADFBEG:F10:001:000002:000000:C:\Macro Temp\csv.csv><TBOX4:T:1:CenterCenter000278000200:000:testerT1 %T1%
T2 %T2%
T3 %T3%
T4 %T4%
T5 %T5%
T6 %T6%
T7 %T7%
T8 %T8%
T9 %T9%
T10 %T10%
T11 %T11%
T12 %T12%
T13 %T13%
T14 %T14%
T15 %T15%
T16 %T16%
T17 %T17% ><ADFEND><DOFILE:08:NN:C:\Macro Temp\csv.csv>>

[randallf]

As a note I have to use tab delimited because people use commas in comments. Frustrating but works just as well, or at least for this purpose. Luckily I have different formats available for my source data.

[stevecasper]

I wish I could get a job doing this

Amen to that.