Jump to content

Write inputs values into Colum and Raw in CSV

SOF-TECH
 Share Followers 0

Recommended Posts

SOF-TECH

SOF-TECH 0

Posted
Posted

Dear all,

Can someone show  me how to en hance the below function to write in CSV  into column  and rows the input values ? 

I am getting this result: 

image.png.2e82e90226750cb7316d1b0fbbd19e88.png

I would like the result to be as this 

image.png.3f5a787395de64c88672baabac1a07de.png

From A1:C1 is for headers

From A2:C2 is for input Data 

Global Const $GUI_EVENT_CLOSE = -3

$sDataFilePath = @ScriptDir & "\Records.csv"

#region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Demo1: New Record", 580, 115)
$Input1 = GUICtrlCreateInput("", 10, 30, 270, 21)
$Input2 = GUICtrlCreateInput("", 300, 30, 270, 21)
$Input3 = GUICtrlCreateInput("", 10, 80, 270, 21)
$Label1 = GUICtrlCreateLabel("Name:", 10, 10, 35, 17)
$Label2 = GUICtrlCreateLabel("ID:", 300, 10, 18, 17)
$Label3 = GUICtrlCreateLabel("Phone No:", 10, 60, 55, 17)
$Button1 = GUICtrlCreateButton("Save to CSV", 450, 70, 120, 30)
GUISetState(@SW_SHOW)
#endregion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            _ExportData()
            MsgBox(64, @ScriptName, "Record Saved.")
    EndSwitch
WEnd

Func _ExportData()
    If Not FileExists($sDataFilePath) Then
        FileWriteLine($sDataFilePath, "Name;ID;Phone No.;")
    EndIf
    For $i = $Input1 To $Input3
        FileWrite($sDataFilePath, GUICtrlRead($i) & ";")
    Next
    FileWriteLine($sDataFilePath, "")
EndFunc   ;==>_ExportData

May be Excel UDF has be to be added but I can manage that my self  

Thank you in advance

Link to post
Share on other sites
  • Moderators
JLogan3o13

JLogan3o13 1,765

Posted
  • Moderators
Posted (edited)

First off, you will not get the pretty  column separation in a CSV, you will need to save to Excel instead. For your _ExportData function you could do something like this: 

Func _ExportData()
    Local $oExcel = _Excel_Open()
    Local $oWorkBook

    If Not FileExists($sDataFilePath) Then
        $oWorkBook = _Excel_BookNew($oExcel)
    Else
        $oWorkBook = _Excel_BookOpen($oExcel, $sDataFilePath)
    EndIf

    _Excel_RangeWrite($oWorkBook, Default, GUICtrlRead($Input1), "A1")
    _Excel_RangeWrite($oWorkBook, Default, GUICtrlRead($Input2), "B1")
    _Excel_RangeWrite($oWorkBook, Default, GUICtrlRead($Input3), "C1")

    If FileExists($sDataFilePath) Then
        _Excel_BookSave($oWorkBook)
    Else
        _Excel_BookSaveAs($oWorkBook, $sDataFilePath)
    EndIf

    _Excel_BookClose($oWorkBook)
    _Excel_Close($oExcel)
EndFunc   ;==>_ExportData

This gives you a rough idea; you would have to work out cycling through the lines as you add further entries, but you should be able to do that simply enough.

The problem with this approach would be the constant opening and closing of Excel (or leaving it open and running into an issue if the script fails). It would be a better practice to save all of your entries in an array until you're ready to do a single commit. Something like this: 

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

Global Const $GUI_EVENT_CLOSE = -3
Local $aArray[1][3] = [["Name","ID","Phone"]]
Local $sDataFilePath = @ScriptDir & "\Records"

#region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Demo1: New Record", 580, 115)
$Input1 = GUICtrlCreateInput("", 10, 30, 270, 21)
$Input2 = GUICtrlCreateInput("", 300, 30, 270, 21)
$Input3 = GUICtrlCreateInput("", 10, 80, 270, 21)
$Label1 = GUICtrlCreateLabel("Name:", 10, 10, 35, 17)
$Label2 = GUICtrlCreateLabel("ID:", 300, 10, 18, 17)
$Label3 = GUICtrlCreateLabel("Phone No:", 10, 60, 55, 17)
$Button1 = GUICtrlCreateButton("Add Record", 450, 70, 120, 30)
$Button2 = GUICtrlCreateButton("Save to Excel", 300, 70, 120, 30)

GUISetState(@SW_SHOW)
#endregion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            _ArrayAdd($aArray, GUICtrlRead($Input1) & "|" & GUICtrlRead($Input2) & "|" & GUICtrlRead($Input3))
            MsgBox($MB_OK, "Demo", "Record Added")
        Case $Button2
            _ExportData($aArray)
    EndSwitch
WEnd


Func _ExportData($aArray)
    Local $oExcel = _Excel_Open()
    Local $oWorkBook

    If Not FileExists($sDataFilePath) Then
        $oWorkBook = _Excel_BookNew($oExcel)
    Else
        $oWorkBook = _Excel_BookOpen($oExcel, $sDataFilePath)
    EndIf

    _Excel_RangeWrite($oWorkBook, Default, $aArray, Default)

    $oWorkBook.Worksheets("Sheet1").Columns("A:C").AutoFit

    If FileExists($sDataFilePath) Then
        _Excel_BookSave($oWorkBook)
    Else
        _Excel_BookSaveAs($oWorkBook, $sDataFilePath)
    EndIf

    _Excel_BookClose($oWorkBook)
    _Excel_Close($oExcel)
EndFunc   ;==>_ExportData

Any questions please ask :)

Edited by JLogan3o13

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to post
Share on other sites
Juvigy

Juvigy 50

Posted
Posted
23 hours ago, SOF-TECH said:

Can you demonstate it please. I still can not get your point :)

Those functions have very good documentation and examples. You can jut replace your filename in the examples and it will work. What more demonstration would you ever need?

Link to post
Share on other sites
  • 3 weeks later...
SOF-TECH

SOF-TECH 0

Posted
  • Author
Posted
On 2018/5/3 at 8:58 PM, JLogan3o13 said:

First off, you will not get the pretty  column separation in a CSV, you will need to save to Excel instead. For your _ExportData function you could do something like this: 

Func _ExportData()
    Local $oExcel = _Excel_Open()
    Local $oWorkBook

    If Not FileExists($sDataFilePath) Then
        $oWorkBook = _Excel_BookNew($oExcel)
    Else
        $oWorkBook = _Excel_BookOpen($oExcel, $sDataFilePath)
    EndIf

    _Excel_RangeWrite($oWorkBook, Default, GUICtrlRead($Input1), "A1")
    _Excel_RangeWrite($oWorkBook, Default, GUICtrlRead($Input2), "B1")
    _Excel_RangeWrite($oWorkBook, Default, GUICtrlRead($Input3), "C1")

    If FileExists($sDataFilePath) Then
        _Excel_BookSave($oWorkBook)
    Else
        _Excel_BookSaveAs($oWorkBook, $sDataFilePath)
    EndIf

    _Excel_BookClose($oWorkBook)
    _Excel_Close($oExcel)
EndFunc   ;==>_ExportData

This gives you a rough idea; you would have to work out cycling through the lines as you add further entries, but you should be able to do that simply enough.

The problem with this approach would be the constant opening and closing of Excel (or leaving it open and running into an issue if the script fails). It would be a better practice to save all of your entries in an array until you're ready to do a single commit. Something like this: 

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

Global Const $GUI_EVENT_CLOSE = -3
Local $aArray[1][3] = [["Name","ID","Phone"]]
Local $sDataFilePath = @ScriptDir & "\Records"

#region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Demo1: New Record", 580, 115)
$Input1 = GUICtrlCreateInput("", 10, 30, 270, 21)
$Input2 = GUICtrlCreateInput("", 300, 30, 270, 21)
$Input3 = GUICtrlCreateInput("", 10, 80, 270, 21)
$Label1 = GUICtrlCreateLabel("Name:", 10, 10, 35, 17)
$Label2 = GUICtrlCreateLabel("ID:", 300, 10, 18, 17)
$Label3 = GUICtrlCreateLabel("Phone No:", 10, 60, 55, 17)
$Button1 = GUICtrlCreateButton("Add Record", 450, 70, 120, 30)
$Button2 = GUICtrlCreateButton("Save to Excel", 300, 70, 120, 30)

GUISetState(@SW_SHOW)
#endregion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            _ArrayAdd($aArray, GUICtrlRead($Input1) & "|" & GUICtrlRead($Input2) & "|" & GUICtrlRead($Input3))
            MsgBox($MB_OK, "Demo", "Record Added")
        Case $Button2
            _ExportData($aArray)
    EndSwitch
WEnd


Func _ExportData($aArray)
    Local $oExcel = _Excel_Open()
    Local $oWorkBook

    If Not FileExists($sDataFilePath) Then
        $oWorkBook = _Excel_BookNew($oExcel)
    Else
        $oWorkBook = _Excel_BookOpen($oExcel, $sDataFilePath)
    EndIf

    _Excel_RangeWrite($oWorkBook, Default, $aArray, Default)

    $oWorkBook.Worksheets("Sheet1").Columns("A:C").AutoFit

    If FileExists($sDataFilePath) Then
        _Excel_BookSave($oWorkBook)
    Else
        _Excel_BookSaveAs($oWorkBook, $sDataFilePath)
    EndIf

    _Excel_BookClose($oWorkBook)
    _Excel_Close($oExcel)
EndFunc   ;==>_ExportData

Any questions please ask :)

On 2018/5/3 at 8:58 PM, JLogan3o13 said:

First off, you will not get the pretty  column separation in a CSV, you will need to save to Excel instead. For your _ExportData function you could do something like this: 

Func _ExportData()
    Local $oExcel = _Excel_Open()
    Local $oWorkBook

    If Not FileExists($sDataFilePath) Then
        $oWorkBook = _Excel_BookNew($oExcel)
    Else
        $oWorkBook = _Excel_BookOpen($oExcel, $sDataFilePath)
    EndIf

    _Excel_RangeWrite($oWorkBook, Default, GUICtrlRead($Input1), "A1")
    _Excel_RangeWrite($oWorkBook, Default, GUICtrlRead($Input2), "B1")
    _Excel_RangeWrite($oWorkBook, Default, GUICtrlRead($Input3), "C1")

    If FileExists($sDataFilePath) Then
        _Excel_BookSave($oWorkBook)
    Else
        _Excel_BookSaveAs($oWorkBook, $sDataFilePath)
    EndIf

    _Excel_BookClose($oWorkBook)
    _Excel_Close($oExcel)
EndFunc   ;==>_ExportData

This gives you a rough idea; you would have to work out cycling through the lines as you add further entries, but you should be able to do that simply enough.

The problem with this approach would be the constant opening and closing of Excel (or leaving it open and running into an issue if the script fails). It would be a better practice to save all of your entries in an array until you're ready to do a single commit. Something like this: 

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

Global Const $GUI_EVENT_CLOSE = -3
Local $aArray[1][3] = [["Name","ID","Phone"]]
Local $sDataFilePath = @ScriptDir & "\Records"

#region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Demo1: New Record", 580, 115)
$Input1 = GUICtrlCreateInput("", 10, 30, 270, 21)
$Input2 = GUICtrlCreateInput("", 300, 30, 270, 21)
$Input3 = GUICtrlCreateInput("", 10, 80, 270, 21)
$Label1 = GUICtrlCreateLabel("Name:", 10, 10, 35, 17)
$Label2 = GUICtrlCreateLabel("ID:", 300, 10, 18, 17)
$Label3 = GUICtrlCreateLabel("Phone No:", 10, 60, 55, 17)
$Button1 = GUICtrlCreateButton("Add Record", 450, 70, 120, 30)
$Button2 = GUICtrlCreateButton("Save to Excel", 300, 70, 120, 30)

GUISetState(@SW_SHOW)
#endregion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            _ArrayAdd($aArray, GUICtrlRead($Input1) & "|" & GUICtrlRead($Input2) & "|" & GUICtrlRead($Input3))
            MsgBox($MB_OK, "Demo", "Record Added")
        Case $Button2
            _ExportData($aArray)
    EndSwitch
WEnd


Func _ExportData($aArray)
    Local $oExcel = _Excel_Open()
    Local $oWorkBook

    If Not FileExists($sDataFilePath) Then
        $oWorkBook = _Excel_BookNew($oExcel)
    Else
        $oWorkBook = _Excel_BookOpen($oExcel, $sDataFilePath)
    EndIf

    _Excel_RangeWrite($oWorkBook, Default, $aArray, Default)

    $oWorkBook.Worksheets("Sheet1").Columns("A:C").AutoFit

    If FileExists($sDataFilePath) Then
        _Excel_BookSave($oWorkBook)
    Else
        _Excel_BookSaveAs($oWorkBook, $sDataFilePath)
    EndIf

    _Excel_BookClose($oWorkBook)
    _Excel_Close($oExcel)
EndFunc   ;==>_ExportData

Any questions please ask :)

Hi Glogan

Quoting on this, I compiled the script and run it from C Drive, every thing wornking fine and file excel is saved on script directory. however, when I run out of C drive (E or USB) Excel file is created but could not saved. even the above demo script you wrote has the same effect when run from out of C home drive. could explain or help for this matter

 

Thank you 

Link to post
Share on other sites
  • Moderators
JLogan3o13

JLogan3o13 1,765

Posted
  • Moderators
Posted

@SOF-TECH i just tested running this from a number of network drives E:, P: etc. and it saved just fine. I don't have a USB stick to try it with at the moment, but will test that out when I get a chance. Are you sure the user you are running the script under has write access to the directory you're trying to save the excel file to?

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to post
Share on other sites
SOF-TECH

SOF-TECH 0

Posted
  • Author
Posted

My PC is under domain of company and I have admin rights. Frankly, I have to check with domain policy first regarding the point you mentionned. I will try the script on my personal Machine and update you.

Thank you for the reply and support :)

Link to post
Share on other sites
SOF-TECH

SOF-TECH 0

Posted
  • Author
Posted

Sorry for replying with delay. as I mentionned if run from usb excel could not be save after creation. the script have to be saved in 2 files, 1 is txt that there is no problem in saved it from any path in which the soft is run and 1xls file to store diffrent data where the probelm of saving occurs. Here is the globals to create the files : 

Global $ExcelData = @ScriptDir & "\" & @ComputerName & "_Statistics.xlsx" 
Global $NotePadData = @ScriptDir & "\" & @ComputerName & "_Specifications.txt

I have also changed  to @WorkingDir and added #requireadmin reslult is :

Run from C Drive: txt & xlsx files got created 

Run from external Drive: Only txt. Execel file is created but could not be saved.

 

Link to post
Share on other sites
  • Moderators
JLogan3o13

JLogan3o13 1,765

Posted
  • Moderators
Posted

@SOF-TECH please post the entirety of the code you are using, so we can test out USB scenarios.

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

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
 Share
Followers 0
Go to topic listing

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • JohnnyTries
      aArray headers Always transferred to existing Excel spreadsheet.
      By JohnnyTries
      Hi Fellow Automators,
      Long time listener, first time caller. I've resisted posting on the forums as long as possible for fear of public lynching, but I'm stuck and could really use some help.
      Note: If this is the wrong side of the forum for this topic, I apologize.
      I've built a GUI and script to make our lab data collection easier. The app has a number of input boxes and a 'record' button. The user fills out the input boxes with various notes and then presses 'record', which in turn presses 'record' on two other, separate apps simultaneously, pulls those recordings together into one folder, and then takes the text from the input boxes and adds it in a new row at the bottom of an existing .xlsx spreadsheet. Everything works great, except that every time I open the app to start collecting data for the day, the 'headers' for the $aArray are added to a new row and then the text is added below it. Now, if I don't close the app between collections, subsequent 'recordings' are added to the spreadsheet as expected. If I close the app and open it, the 'first' recording of the day adds the headers to a new row. I don't need new 'header' info because I've already got that in row 1 of the spreadsheet.
      If someone could tell me where I'm !#$%ing up, I would greatly appreciate it. 
       
      #RequireAdmin #include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <MsgBoxConstants.au3> #include <WindowsConstants.au3> #include <String.au3> #include <Process.au3> #include <FileConstants.au3> #include <WinAPIFiles.au3> #include <DirConstants.au3> #include <Array.au3> #include <AutoItConstants.au3> #include <File.au3> #include <WinAPIShPath.au3> #include <Excel.au3> ;Declaring the $aArray and location of the speadsheet at the top of the script Local $aArray[1][9] = [["TestID","DateTimeStamp","Tamb_C","BGTemp_C", "GasType", "TrueFlow_slm", "Lens-BGDist_in", "Lens-LeakDist_In", "AddNotes"]] Local $sDataFilePath = @ScriptDir & "\Notes\DualCaptureNotes.xlsx" Func CaptureVideo() Global $TestID = GuiCtrlRead($TestIDInput) Global $timestamp = @YEAR & "-" & @MON & "-" & @MDAY & "-" & @HOUR & "-" & @MIN & "-" & @SEC Global $Cam1Dir = "C:\Archive" Global $Cam2Dir = "C:\ALD" ;Capture Cam1 Data WinActivate("Cam1 App") ControlFocus("Cam1 App", "Save",'WindowsForms10.Window.8.app.0.2099316_r7_ad114') ControlSend("Cam1 App", "Save", 'WindowsForms10.Window.8.app.0.2099316_r7_ad114', "{SPACE}") ;Capture Cam2 Data WinActivate("Cam2 App") ControlClick("Cam2 App", "", 'WindowsForms10.Window.8.app.0.1b0ed41_r7_ad122', '', 1, 10, 10) ;Wait to ensure data files have been fully written to their default locations Sleep(2000) ;Self-explanatorily named functions MoveData() RecordNotesToArray() RecordArraytoExcel() WinActivate("DualCapture") EndFunc ;==>CaptureVideo ;Skipping ahead to the .xlsx part.... Func RecordNotesToArray() _ArrayAdd($aArray, GUICtrlRead($TestIDInput) & "|" & $timestamp & "|" & GUICtrlRead($Tamb_CInput) & "|" & GUICtrlRead($BGTemp_CInput) & "|" & GUICtrlRead($GasTypeInput) & "|" & GUICtrlRead($TrueFlow_slmInput) & "|" & GUICtrlRead($Dist_BG_inInput) & "|" & GUICtrlRead($Dist_Leak_inInput) & "|" & GUICtrlRead($AddNotesInput)) EndFunc ;==>RecordNotesToArray() Func RecordArraytoExcel() Local $oExcel = _Excel_Open() Local $oWorkBook If Not FileExists($sDataFilePath) Then $oWorkBook = _Excel_BookNew($oExcel) Else $oWorkBook = _Excel_BookOpen($oExcel, $sDataFilePath) EndIf $oWorkBook.Worksheets("DataTable").Columns("A:I").AutoFit $LastRow = $oWorkbook.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).Row $Rowrange = "A"&$LastRow+1 Consolewrite($Rowrange & @crlf) _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray, $Rowrange) If FileExists($sDataFilePath) Then _Excel_BookSave($oWorkBook) Else _Excel_BookSaveAs($oWorkBook, $sDataFilePath) EndIf _Excel_BookClose($oWorkBook) _Excel_Close($oExcel) EndFunc ;==>RecordArrayToExcel() I appreciate your time and any help you can provide.
       
      Best,
      Johnny
       
    • goku200
      Seperating filename into coumns in Excel
      By goku200
      I have an Autoit script that lists files from a folder into an array list. Is there a way to separate the filenames by an underscore and include the id, version, name and date into separate columns in Excel.
      Example of filename:
      12345_v1.0_TEST Name [12345]_01.01.2022.html
      12345 would be in one column
      v1.0 would be in another column
      TEST Name [12345] would be in another column
      01.01.2022 would be in another column
      .html would be in another column
      Note: filenames always change each day.
      Here is my code that lists the files into column C and then writes the column Headers into Column D, E, F, G. Just need some help with separating them into columns by the _ delimiter
       
    • SkysLastChance
      _Excel_RangeWrite Formula with "" and '' included
      By SkysLastChance
      I am having a issue of whenever I try to _Excel_RangeWrite a formula that references another workbook I am getting an error @4 and @extended -2147352567

      #include <Excel.au3> #include <MsgBoxConstants.au3> Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookNew($oExcel) _Excel_RangeWrite($oWorkBook,Default,"=IF(D2=D1,"",VLOOKUP(D2,'J:\Temporary Files\FolderName\FileName.xlsx'!$A:$B,2,0))","W2",False) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "String successfully written.") I am not a 100% sure, but I am guessing this is because I have "" and '' in the formula. However, I am not sure how this can best be resolved. 
       
       
    • SkysLastChance
      Autofill a range in excel
      By SkysLastChance
      I am trying to autofill a range. I am getting stuck and I don't understand what I am doing wrong. 
      My goal is to auto fill some formulas that are next to a pivot table in columns A-C. 
      _Excel_RangeWrite($oNewWorkBook,Default,"2000","D3") _Excel_RangeWrite($oNewWorkBook,Default,"=(B3-D3)","E3") _Excel_RangeWrite($oNewWorkBook,Default,"100","F3") _Excel_RangeWrite($oNewWorkBook,Default,"=(C3-F3)","G3") $oNewWorkbook.ActiveSheet.Range("D3:G3").Select With $oNewWorkbook .Selection.AutoFill(.Range("D3:G77"),0) EndWith The data is not auto filling. 
      Hoping someone can point me in the right direction. 
    • Rskm
      Help with pasting data to excel from notepad - (Moved)
      By Rskm
      Hi, I have 5 notepad files with lot of data in each of it. The data are arranged in lines and i wish to get it pasted/copied into excel.  Say, i need to read notepad1 and paste the contents into sheet1 of excel and notepad2 to sheet2 and so on.  If i read the notepad and paste it line by line, it is taking lot of time.  Is there a way by which i can paste the whole of notepad file into excel sheet(and get it pasted line by line as shown in the attached excel), instead of using code to write it line by line?.. I was using Filewriteline(data, line i) initially to write to excel.  The 'i' value was incremented with for loop and the excel was updated, but this takes lot of time.  The expected excel format is attached here.  any help is appreciated.  thanks
      Tmp.xls
×
×
  • Create New...