williamk

Creating CSV File

14 posts in this topic

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

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

If $array[0] = 13 Then

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#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

Share this post


Link to post
Share on other sites

#7 ·  Posted (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 by Squirrely1

Das Häschen benutzt Radar

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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:

#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

Share this post


Link to post
Share on other sites

#12 ·  Posted (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 by czardas

Share this post


Link to post
Share on other sites

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._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

OMG, I noticed several people looking at this same thread (online list) and assumed it was a hot topic. Apologies.

Edited by czardas

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now