williamk Posted January 28, 2008 Share Posted January 28, 2008 I have a large file that I need to pull data out of a txt file and put in into a csv file. The lines in the file I need to pull all have exactly 13 semi-colons and the dilimeter is a semi colon. There are other various lines some with more semi-colons, some with less, and some with no semi-colons at all. How could I do this? An example of the file contents is below. The lines that start CREMTC are examples of what I would be pulling. EXAMPLE DATA: ********* 4 TR7010389 TR7010442 TR7010427 TR7010400 17 CREMTC;;;;;;;;;;;; CREMTG;;;;;;;;;;;; CREMTG;Royal Slope;B0725 Honey Crisp;APL;Picking PCErate;R25.00;7;8;9;10;11;12; CREMTG;Royal Slope;B0725 Honey Crisp;APL;Picking PCErate;R25.00;H2A;;;;;; CREMTJ;;;;;;;;;;;; RSR;GIBBONS;BLK C3 GALA;APL;HARVEST PC RATE;R22.00;;;;;;; RSR;GIBBONS;BLK C3 GALA;APL;HARVEST PC RATE;R22.00;H2A;;;;;; CREMTJ;GINOCHIO;209BA7 GALA;APL;Picking PCErate;R18.00;;;;;;; CREMTJ;GINOCHIO;209BA7 GALA;APL;Picking PCErate;R18.00;H2A;;;;;; CREMTC;GINOCHIO;B2006 Gala;APL;Picking PCErate;R18.00;;;;;;; CREMTC;GINOCHIO;B2006 Gala;APL;Picking PCErate;R18.00;H2A;;;;;; CREMTC;GINOCHIO;B2007 Gala;APL;Picking PCErate;R18.00;;;;;;; CREMTC;GINOCHIO;B2007 Gala;APL;Picking PCErate;R18.00;H2A;;;;;; CREMTG;Royal Slope;B0725 Honey Crisp;APL;Picking PCErate;R25.00;;;;;;; CREMTJ;GINOCHIO;209BA7 GALA;APL;Picking PCErate;R18.00;;;;;;; CREMTC;GINOCHIO;B2006 Gala;APL;Picking PCErate;R18.00;;;;;;; CREMTC;GINOCHIO;B2007 Gala;APL;Picking PCErate;R18.00;;;;;;; ;+RECORD;;;;;;;;;;;MTC;;;;;;8.00;;l*A/r;;;;;0;P=1?"H2A":"";;;;;;;;;;; 1 TR7010389 0616260 0 ;+RECORD;;;;;;;;;;;MTG;;;;;;8.00;;l*A/r;;;;;0;P=1?"H2A":"";;;;;;;;;;; 1 TR7010427 0630310 0 Link to comment Share on other sites More sharing options...
weaponx Posted January 28, 2008 Share Posted January 28, 2008 I only count 12 delimiters (I spaced them out in pairs): CREMTC;; ;; ;; ;; ;; ;; You will need the following ingredients: _FileReadToArray() StringSplit() Basically you will check the return from StringSplit() to ensure it equals 13 (# delimiters + 1). $array = StringSplit($line, ";") If $array[0] = 13 Then ;Success Else ;Not success EndIf Link to comment Share on other sites More sharing options...
williamk Posted January 28, 2008 Author Share Posted January 28, 2008 I only count 12 delimiters (I spaced them out in pairs): Hey weaponx, kk you are correct it is 12. So I have been playing with it. The code I have so far is: #include <file.au3> Dim $line DIM $array If Not _FileReadToArray("20070912.PRO",$line) Then MsgBox(4096,"Error", " Error reading log to Array error:" & @error) Exit EndIf For $x = 1 to $line[0] Msgbox(0,'Record:' & $x, $line[$x]) $array = StringSplit($line,";") IF $array = 13 THEN MsgBox(4096,"Success", " This line has 13 delimeters") Endif Next This shows me each line in the file but it is not picking out the lines with only 12 delimeters. How do I declare the array with the split string statement? CREMTC;; ;; ;; ;; ;; ;; You will need the following ingredients: _FileReadToArray() StringSplit() Basically you will check the return from StringSplit() to ensure it equals 13 (# delimiters + 1). $array = StringSplit($line, ";") If $array[0] = 13 Then ;Success Else ;Not success EndIf Link to comment Share on other sites More sharing options...
weaponx Posted January 28, 2008 Share Posted January 28, 2008 If $array[0] = 13 Then Link to comment Share on other sites More sharing options...
williamk Posted January 28, 2008 Author Share Posted January 28, 2008 If $array[0] = 13 Then Nope, that didn't do it. I think I need to somehow tell it to evaluate each line in the array to see if it has the 12 delimters, but exactly how I don't know. Link to comment Share on other sites More sharing options...
weaponx Posted January 28, 2008 Share Posted January 28, 2008 #include <file.au3> Dim $line Dim $array If Not _FileReadToArray("20070912.PRO", $line) Then MsgBox(4096, "Error", " Error reading log to Array error:" & @error) Exit EndIf For $x = 1 To $line[0] ;MsgBox(0, 'Record:' & $x, $line[$x]) $array = StringSplit($line[$x], ";") If $array[0] = 13 Then MsgBox(4096, "Success", " This line has 13 delimeters") EndIf Next Link to comment Share on other sites More sharing options...
Squirrely1 Posted January 28, 2008 Share Posted January 28, 2008 (edited) I have a large file that I need to pull data out of a txt file and put in into a csv file. The lines in the file I need to pull all have exactly 13 semi-colons and the dilimeter is a semi colon.I think that what you must mean is that each Line has 13 Fields - there seem to be only multiples of 12 semi-colons on each line. So maybe count over to just before the 13th semi-colon and put in a carriage return -programmatically - to fix that problem. But first you need to use one of the functions under the UDF > File Management section of the Help file to remove all the blank lines. There is a function somewhere on the forums to do that, if it's not in the Help file. But before you do that, you should remove all of the lines that do not have at least 12 semi-colons.Then, of course, you just rename the resulting file, to "whatever.csv" Edited January 28, 2008 by Squirrely1 Das Häschen benutzt Radar Link to comment Share on other sites More sharing options...
weaponx Posted January 28, 2008 Share Posted January 28, 2008 I think that what you must mean is that each Line has 13 Fields - there seem to be only multiples of 12 semi-colons on each line. So maybe count over to just before the 13th semi-colon and put in a carriage return -programmatically - to fix that problem. But first you need to use one of the functions under the UDF > File Management section of the Help file to remove all the blank lines. There is a function somewhere on the forums to do that, if it's not in the Help file. But before you do that, you should remove all of the lines that do not have at least 12 semi-colons.Then, of course, you just rename the resulting file, to "whatever.csv"He only wants the lines with 13 elements and the code reflects that. Link to comment Share on other sites More sharing options...
williamk Posted January 28, 2008 Author Share Posted January 28, 2008 #include <file.au3> Dim $line Dim $array If Not _FileReadToArray("20070912.PRO", $line) Then MsgBox(4096, "Error", " Error reading log to Array error:" & @error) Exit EndIf For $x = 1 To $line[0] ;MsgBox(0, 'Record:' & $x, $line[$x]) $array = StringSplit($line[$x], ";") If $array[0] = 13 Then MsgBox(4096, "Success", " This line has 13 delimeters") EndIf Next Thanks much that works. I have been able to pull that data along with some other stuff and write it to seperate files. There is one other part of the file that I need to extract, but it is a bit trickier. It looks like below. So basically each element in the split line array will consist of the first line with 2 of the ; delimeters, plus the 15 lines (@CRLF) that follow that line (ie $array = StringSplit($desc[$x], ";" + 15 @CRLF ). Is there a way to do that? SAMPLE DATA 147150;RICO, MIGUEL ANGEL CONSEBI;RIC501 0630000 1315000 0615000 0030000 0000000 0000000 0000000 0000000 0000000 0000000 4.000000 0.0 0.0 100.000000 15.999974 147090;CRUZ, GABRIEL ROMERO;CRU301 0630000 1315000 0615000 0030000 0000000 0000000 0000000 0000000 0000000 0000000 3.000000 0.0 0.0 75.000000 11.999981 Link to comment Share on other sites More sharing options...
weaponx Posted January 28, 2008 Share Posted January 28, 2008 This will work only if you are sure 15 lines follow the delimited string. #include <file.au3> #include<array.au3> Dim $array ;Read file to array If Not _FileReadToArray("test.txt", $array) Then MsgBox(4096, "Error", " Error reading log to Array error:" & @error) Exit EndIf _ArrayDisplay($array) For $x = 1 To $array[0] If StringInStr($array[$x],";") Then ConsoleWrite($array[$x] & @CRLF) For $Y = 1 to 15 ConsoleWrite($array[$x + $y] & @CRLF) Next ConsoleWrite(@CRLF) EndIf Next Link to comment Share on other sites More sharing options...
williamk Posted January 30, 2008 Author Share Posted January 30, 2008 This will work only if you are sure 15 lines follow the delimited string. #include <file.au3> #include<array.au3> Dim $array ;Read file to array If Not _FileReadToArray("test.txt", $array) Then MsgBox(4096, "Error", " Error reading log to Array error:" & @error) Exit EndIf _ArrayDisplay($array) For $x = 1 To $array[0] If StringInStr($array[$x],";") Then ConsoleWrite($array[$x] & @CRLF) For $Y = 1 to 15 ConsoleWrite($array[$x + $y] & @CRLF) Next ConsoleWrite(@CRLF) EndIf Next OK guys, So I've run into another problem and it is giving me a headache. Here is the code: expandcollapse popup#include <file.au3> #include <array.au3> #include <Date.au3> ; If the; delimited files I want to create exist, then delete them. If FileExists("pfdescrip.txt") Then FileDelete ("pfdescrip.txt") EndIf If FileExists("lundetail.txt") Then FileDelete ("lundetail.txt") EndIf If FileExists("empdetail.txt") Then FileDelete ("empdetail.txt") EndIf If FileExists("pfdetail.txt") Then FileDelete ("pfdetail.txt") EndIf $iWeekday = _DateToDayOfWeek (@YEAR, @MON, @MDAY); get the weekday this will act as part of the profile ID number Dim $desc Dim $detail Dim $lunch Dim $emp DIM $array DIM $dirpath DIM $sfilter $dirpath = "C:\Datatrack\Data\";set the path for the folder where the data extraction files are $sfilter = "20080122.PRO"; set the filter for the files you want to extract data from $FileList = _FileListToArray($dirpath, $sfilter, 1);get the list of files you want to extract data from If @Error=1 Then MsgBox (0,"","No Files\Folders Found.") Exit EndIf FOR $L = 1 to $FileList[0]; increment the counter for the files in the array so you can process each file in the filelist array $pfdetail = FileOpen ( "pfdetail.txt", 1) $empdetail = FileOpen("empdetail.txt",1) If Not _FileReadToArray($dirpath & $FileList[$L],$detail) Then;open the next file in the file array and read it into the $detail array MsgBox(4096,"Error", " Error reading log to Array error:" & @error) Exit EndIf $c = 0; set the sequence counter for array line writes. This number will also be used in profile id number For $x = 1 to $detail[0]; start on the first line of the File array and increment each time the FOR loops $array2 = StringSplit($detail[$x], ";"); split the current line into $array2 using ";" as delimeter IF $array2[0] = 38 THEN; If there are 38 delimeters on line then-- $c = $c + 1; increment the sequence counter by 1 FileWrite ($pfdetail, $detail[$x] & ";" & $iWeekday & $c & @CRLF);write line +; + weekday + sequence number + carriage return $gd = $detail[$x + 4];get number of employee detail records for this profile (will be on 4th line after line you have just written) MsgBox(4096, "emp detail records", "Number of emp detail records for profile is:" & $gd) if $gd <> 0 Then;if not zero employees then get employee detail records MsgBox(4096, "Now in If ", "Am in the if statement"& $gd) $edc = 0;This counts the number of times you read in employee detail records $z = $x; While $edc < $gd ;loop through this to get correct number of employee detail records MsgBox(4096, "Now in while ", "Am in the while loop"& $gd) For $z = 1 to $detail[$z] $emptable=Stringsplit($detail[$z],";");split the current line into $emptable array using ";" as delimeter If $emptable[0] = 3 Then;if line has 3; then write it to MsgBox(4096, "emp detail records", "line array reads:" & $emptable) Filewrite($empdetail, $detail[$z] & ";") For $Y = 1 to 15 FileWrite ($empdetail, $detail[$z + +$y] & ";") MsgBox(4096, "Now in For ", "Am in the For loop"& $gd) Next FileWrite($empdetail, @CRLF) $edc = $edc +1 MsgBox(4096, "Number of Loops", "Loops are:" & $edc) EndIf Next wend Else Endif Endif Next FileClose($pfdescrip) FileClose($empdetail) Next If I have a file with the below data, what this code should do is ouput three profile detail lines with profile_id number of 31, 32, 33 (ie:) ;+RECORD;;;;;;;;;;;RSRC;;;;;;8.00;;l*A/r;;;;;0;P=1?"H2A":"";;;;;;;;;;;31 ;+RECORD;;;;;;;;;;;RSRC;;;;;;8.00;;l*A/r;;;;;0;P=1?"H2A":"";;;;;;;;;;;32 150;+0NOH2A;;;;;;;;;1.50;;RSRC;;;"P";28;;8.00;A;l*A/r;1.50;;^0;;0;P=1?"H2A":"";;;;;;;;;;;33 200;+0NOH2A;;;;;;;;;2.00;;RSRC;;;"P";39;;8.00;A;l*A/r;2.00;;^0;;0;P=1?"H2A":"";;;;;;;;;;; 34 It should also ouput the 3 employee detail records to a seperate file with a profile_id of 33. I know I am getting stuck in my While loop, but can't figure out why. For some reason "If $emptable[0] = 3" never seems to be true. I think where I am getting confused is with my arrays and what line is being read in the file. I've been staring at this for 4 hours now. Help!!! Sample Data: ;+RECORD;;;;;;;;;;;RSRC;;;;;;8.00;;l*A/r;;;;;0;P=1?"H2A":"";;;;;;;;;;; TR7010372 0943510 0 ;+RECORD;;;;;;;;;;;RSRC;;;;;;8.00;;l*A/r;;;;;0;P=1?"H2A":"";;;;;;;;;;; 1 TR7010372 0944510 0 150;+0NOH2A;;;;;;;;;1.50;;RSRC;;;"P";28;;8.00;A;l*A/r;1.50;;^0;;0;P=1?"H2A":"";;;;;;;;;;; 1 TR7010372 0945090 3 111805;VADOVINOS FARIAS, SERGIO;FAR801 0945000 0945000 0000000 0000000 0000000 0000000 0000000 0000000 0000000 0000000 0.0 0.0 0.0 0.000000 602144;ROSALEZ MARTINEZ, VICTORINO;ROS881 0945000 0945000 0000000 0000000 0000000 0000000 0000000 0000000 0000000 0000000 0.0 0.0 0.0 0.000000 111059;FIGUEROA, ANGEL;FIG101 0945000 0945000 0000000 0000000 0000000 0000000 0000000 0000000 0000000 0000000 0.0 0.0 0.0 0.000000 200;+0NOH2A;;;;;;;;;2.00;;RSRC;;;"P";39;;8.00;A;l*A/r;2.00;;^0;;0;P=1?"H2A":"";;;;;;;;;;; --ID4 --PDETAIL4 1 TR7010372 0948328 0 Link to comment Share on other sites More sharing options...
czardas Posted January 11, 2016 Share Posted January 11, 2016 (edited) I don't know if this helps. The UDF is here : https://www.autoitscript.com/forum/topic/155748-csvsplit/#include <Array.au3> #include 'CSVSplit.au3' Local $sCSV = _ ';+RECORD;;;;;;;;;;;RSRC;;;;;;8.00;;l*A/r;;;;;0;P=1?"H2A":"";;;;;;;;;;;31' & @CRLF & _ ';+RECORD;;;;;;;;;;;RSRC;;;;;;8.00;;l*A/r;;;;;0;P=1?"H2A":"";;;;;;;;;;;32' & @CRLF & _ '150;+0NOH2A;;;;;;;;;1.50;;RSRC;;;"P";28;;8.00;A;l*A/r;1.50;;^0;;0;P=1?"H2A":"";;;;;;;;;;;33' & @CRLF & _ '200;+0NOH2A;;;;;;;;;2.00;;RSRC;;;"P";39;;8.00;A;l*A/r;2.00;;^0;;0;P=1?"H2A":"";;;;;;;;;;; 34' & @CRLF Local $aArray = _CSVSplit($sCSV, ';') _ArrayDisplay($aArray, "parsing double quotes - col 26") ; Or perhaps you prefer something like this: Local $aArray = _CSVSplit(StringReplace($sCSV, '"', '""'), ';') _ArrayDisplay($aArray, "ignoring double quotes - col 26") Edited January 11, 2016 by czardas operator64 ArrayWorkshop Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted January 11, 2016 Moderators Share Posted January 11, 2016 czardas,I take it you did not look too closely at the date of the post above yours?M23 Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area Link to comment Share on other sites More sharing options...
czardas Posted January 11, 2016 Share Posted January 11, 2016 (edited) OMG, I noticed several people looking at this same thread (online list) and assumed it was a hot topic. Apologies. Edited January 11, 2016 by czardas operator64 ArrayWorkshop Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now