Jump to content
Sign in to follow this  
Bry

This should be pretty easy XLS or CSV get data and save to another CSV file

Recommended Posts

Bry

I have been trying to figure this out for acouple of days. I need to get some data from a .XLS that has then been saved as a .CSV file. The data isn't all on the same line as see in the supplied sample data below. What i am trying to do is open a .CSV file then get 3 pieces of information that are on different lines put them together seperated by a comma and write that information to another .CSV file which another programs takes and imports into a database. My thinking was either to use Excel or a Array and also a INI file (to store settings), but I am not sure how to cycle through each line to get the information and put it together when the data I need is not on the same line. I am currently able to use some of the Excel Management Functions and go so many rows and columns and get the cell value, but so far down the file it changes by adding 2 more rows per Item and throws off the row count. I hope this make some sense fo what I am trying to do. It should be pretty easy, I just haven't figured it out so it works. I was trying to use a array, but not sure how to set that up either. I ahve used AutoIt for a while but never done anything like this.

Here is what the sample data looks like if opened in notepad:

----Sample Data Starts Here-----

Report By Customer,,,,,,

ABC #: ********* Item #: *****9 ,,,,,,

From 3/1/2009 to 3/31/2009,,,,,,

Date: 3/1/2009,,,,,Detail Date: 03/01/2009,

Terminal ID,Description,Display,,,,Fee

P02862 ,Funds ,"$2,300.00 ",,,,$0.00

,Total:,"$2,300.00 ",,,,$0.00

,,,,,"Cost for 3/1/2009 - $2,300.00",

Date: 3/2/2009,,,,,Detail Date: 03/02/2009,

Terminal ID,Description,Display,,,,Fee

P02862 ,Funds ,$720.00 ,,,,$0.00

,Total:,$720.00 ,,,,$0.00

,,,,,Cost for 3/2/2009 - $720.00,

Date: 3/3/2009,,,,,Detail Date: 03/03/2009,

Terminal ID,Description,Display,,,,Fee

P02862 ,Funds ,"$1,320.00 ",,,,$0.00

,Total:,"$1,320.00 ",,,,$0.00

,,,,,"Cost for 3/3/2009 - $1,320.00",

The Bold Text is the data I just need so in the final .CSV file it would look like this:

P02862,03/01/2009,2300

P02862,03/02/2009,720

P02862,03/03/2009,1320

-----Sample Data Ends Here-----

Sorry for the long post just wanted to get as much detail in the 1st post as possible.

Any insight or assistance would greatly be appreciated.

Edited by Bry

Share this post


Link to post
Share on other sites
Melba23

Bry,

First, welcome to the AutoIt forums.

When you post here it always helps if you have had a go at solving your problems beforehand. Having some code to work on is a great help - and no-one here is too keen to help the "code it for me" brigade.

Reading the Help file (at least the first few sections - Using AutoIt, Tutorials and the first couple of References) will help you enormously. You should also look at the excellent tutorials that you will find here and here. Using the Search facility is also a good tip as there is a pretty good chance your question, or something like it, has been asked before. Look for the "Search" button to the right in the title bar.

Anyway, you were right - your solution is pretty easy. :-) So.....

#include <Array.au3>

$sFull_File = FileRead("path to the original file")
$hFile = FileOpen("Path to the new file", 2)

$aArray_Sections = StringSplit($sFull_File, "Detail Date:", 1)

For $i = 2 To $aArray_Sections[0]

    $aArray_Details = StringSplit($aArray_Sections[$i], "Fee", 1)
    _ArrayDisplay($aArray_Details)
    $aArray_Details = StringSplit($aArray_Details[2], ",")
    _ArrayDisplay($aArray_Details)
    $sResult = StringTrimRight($aArray_Details[1], 1) & ","
    $sResult &= StringMid($aArray_Sections[$i], 2, 10) & ","
    $aArray_Details = StringSplit($aArray_Sections[$i], "$")
    _ArrayDisplay($aArray_Details)
    $sResult &= StringReplace(StringLeft($aArray_Details[2], StringInStr($aArray_Details[2], ".") - 1), ",", ""); I am sure you will have fun with this line!
    FileWrite($hFile, $sResult)
    
Next

FileClose($hFile)

The #include <Array.au3> and _ArrayDisplay(...) lines are only there so you can follow what is going on, you do not need to keep them.

Ask if anything is unclear.

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
Bry

Thank you Melba23

Here is my testing code that I have been messing with for the pass few days. I have been using AutoIt for years, but never for anything like this. I didn't post code as it was pretty ugly. I always do search, but didn't find anything that explains how to really use Arrays. I look for tutorials and such. I had found a few samples of code from the forum, but nothing that was close to doing what I was trying to do. I don't what others to write the code, otherwise I won't learn the systax and flow of logic...etc. I do thank you for the sample code you posted, but I was looking for was more explation of code or how to verses rather then just having somebody code it. I think the only reason I finally posted as I am under a timing issue to get this done.

Here is my ugly code I have been testing with:

#include <Excel.au3>

#include <Date.au3>

#include <File.au3>

#include <Array.au3>

If FileExists(@WorkingDir & "\My.ini") Then

$AppTitle = IniRead(@WorkingDir & "\My.Ini", "Settings", "Title", "")

$TIDOffset= IniRead(@WorkingDir & "\My.ini", "TID", "RowOffset", "Data Not Found In The INI For TID")

$DATEOffset= IniRead(@WorkingDir & "\My.ini", "Date", "RowOffset", "Data Not Found In The INI For DATE")

$AMOUNTOffset= IniRead(@workingDir & "\My.ini", "Amount", "RowOffset", "Data Not Found In The INI For AMOUNT")

;MsgBox(4096, $AppTitle, "TID Offset: " & $TIDOffset & @CR & "Date Offset: " & $DATEOffset & @CR & "Amount Offset: " & $AMOUNTOffset)

;Calculate Date for current import day

$sImportdate = _DateAdd( 'd', -1, _NowCalcDate())

$sMonth = StringMid($sImportdate,6,2)

$sDay = StringMid($sImportdate,9,2)

$sYear = StringMid($sImportdate,1,4)

$filedate = $sMonth & $sDay & $sYear

If FileExists(@WorkingDir & "\Arch March.csv") Then

$sFilePath1 = @ScriptDir & "\Arch March.csv" ;This file should already exist

$oExcel = _ExcelBookOpen($sFilePath1)

$sTIDRowCounter = 6

Run("notepad.exe")

WinWaitActive("Untitled - Notepad")

Do

;For $i = 1 To 5

;Local $avArray[1]

$sTIDCellValue = StringStripWS(_ExcelReadCell($oExcel,$sTIDRowCounter + 2 ,1),8)

$sDateCellValue = StringMid(_ExcelReadCell($oExcel,$sTIDRowCounter,1),11,9)

$sAmountCellValue = _ExcelReadCell($oExcel,$sTIDRowCounter + 2,3)

If $sAmountCellValue = 0 Then

;Skip wirtting to Notepad

Else

WinActive("Untitled - Notepad")

Send($sTIDCellValue & "," & $sDateCellValue & "," & $sAmountCellValue)

Send(@CR)

;_ArrayAdd($avArray,$sTIDCellValue)

;MsgBox(4096, "Information", "TID: " & $sTIDCellValue & @CR & "Date: " & $sDateCellValue & @CR & "Amount: " & $sAmountCellValue)

;MsgBox(4096, "Information", "TID: " & $sTIDCellValue & " " & $sTIDRowCounter)

$sTIDRowCounter = $sTIDRowCounter + $TIDOffset

;_ArrayDisplay($avArray, "test")

EndIf

;Next

Until $sTIDCellValue = ""

Sleep(5000)

_ExcelBookClose($oExcel, 1, 0) ;This method will save then Close the file, without any of the normal prompts, regardless of changes

Else

MsgBox(4096, "Information", "File: Arch March .csv" @CR "Could Not Be Found, Please Check For The File" @CR "or" @CR "Chcek the File Name.")

EndIf

Else

MsgBox(4096, "Information", "Ini File Is Missing.")

EndIf

Some of this was so I could see what the code was doing. Pretty rough ugh! :D

Thank you again.

Edited by Bry

Share this post


Link to post
Share on other sites
Melba23

Bry,

Congratulations on wanting to do it yourself - and apologies if I came over as a bit condescending.

Here is a commented version of the code to help you understand it better:

#include <Array.au3>

; Read in the original CSV file
$sFull_File = FileRead("path to the original file")
; Create the output file
$hFile = FileOpen("Path to the new file", 2)

; Split the original file using "Detail Date:" as the split point
$aArray_Sections = StringSplit($sFull_File, "Detail Date:", 1)
; Show the split file
_ArrayDisplay($aArray_Sections)

; Take each of the useful sections of the array in turn
For $i = 2 To $aArray_Sections[0]

; Split each of the sections on "Fee"
    $aArray_Details = StringSplit($aArray_Sections[$i], "Fee", 1)
; Show the result
    _ArrayDisplay($aArray_Details)
; Now split the second element again using ","
    $aArray_Details = StringSplit($aArray_Details[2], ",")
; Show the result
    _ArrayDisplay($aArray_Details)
; Create a string with the date followed bya comma
    $sResult = StringTrimRight($aArray_Details[1], 1) & ","

; Go back to original array and extract the date - add to result string followed by a comma
    $sResult &= StringMid($aArray_Sections[$i], 2, 10) & ","
    
; Split the original array on "$"
    $aArray_Details = StringSplit($aArray_Sections[$i], "$")
; Show the result
    _ArrayDisplay($aArray_Details)

; Let us break this line down
    $sResult &= StringReplace(StringLeft($aArray_Details[2], StringInStr($aArray_Details[2], ".") - 1), ",", "")
#cs
; Find the decimal point in this element
    StringInStr($aArray_Details[2], ".")
; Extract the figures preceding the decimal point
    StringLeft($aArray_Details[2], (What we just did) - 1)
; Remove the thousands comma and add the result to the string
    $sResult &= StringReplace((What we just did), ",", "")
#ce
; Write result string to the output file
    FileWrite($hFile, $sResult)
    
Next

; Close output file
FileClose($hFile)

I hope that is clearer. Please ask if anything is unclear.

M23

Edit: Speeling

Edited by Melba23

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
SmOke_N

Ya'll are making it way harder than need be. It's really just two lines of RegEx code and then concat the array to the specified string.

Here's a full function for it:

_Write_MyCSVData("testing.csv")

Func _Write_MyCSVData($s_file_to_read, $s_out_file = "", $s_read_delim = ",", $s_out_delim = ",")
    
    If FileExists($s_file_to_read) = 0 Then Return SetError(1, 0, 0)
    Local $s_data = FileRead($s_file_to_read)
    
    ; If no out file is specified, use file to read as the write to file
    If $s_out_file = "" Then $s_out_file = $s_file_to_read
    
    Local $s_pattern = "(?i)(?s)(?:\A|\n)Date:\s+(\d+/\d+/\d+).+?" & $s_read_delim & _
                        "Fee\s+(\w+)\s+" & $s_read_delim & _
                        "Funds\s+" & $s_read_delim & "\x22?\$(.+?)\s\x22?"
                        
    Local $a_sre = StringRegExp($s_data, $s_pattern, 3)
    If @error Then Return SetError(2, 0, 0)
    Local $s_out_string = ""
    
    ; Concat strings in specific order
    For $i = 0 To UBound($a_sre) - 1 Step 3
        $s_out_string &= $a_sre[1] & $s_out_delim & $a_sre[0] & $s_out_delim & $a_sre[2] & @CRLF
    Next
    
    ; Make sure you have a clean file to write to, or there is a cr/lf at the end of the file
    If StringRegExp($s_out_file, "^\z|[\r\n]+\z") = 0 Then $s_out_string = @CRLF & $s_out_string
    
    Return FileWrite($s_out_file, StringTrimRight($s_out_string, 2))
EndFunc
Should be pretty self explanatory.

Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.

Share this post


Link to post
Share on other sites
Melba23

SmOke_N,

I was waiting for a RegEx coder to pop up! I freely admit that nothing involving that function is "pretty self explanatory" to me - but I will keep trying to get my head around it.

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
Bry

Melba23 and SmOke_N,

Just wanted to say thank you both for the information and the example code. I never used the stringregexp before. I have been reading up on regular expression patterns an WOW! :D Open my eyes to a new way at to look for data in files...etc. I going to look at using it for future projects.

Thank you to both of you again. :o

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
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.