Write inputs values into Colum and Raw in CSV

[SOF-TECH]

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: 

I would like the result to be as this 

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

[JLogan3o13]

Moved to the appropriate forum. Please pay attention to where you post in the future.

[SOF-TECH]

Sorry for the inconvinience caused

[Juvigy]

Read the csv file row by row using FileReadLine. Then split the result to an array using StringSplit. Then write the array to excel using _Excel_RangeWrite. Simple task.

[SOF-TECH]

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

[FrancescoDiMuro]

Good morning @SOF-TECH
You destination file is always in .CSV format?

Edit: Yes, I didn't pay attention reading your post, sorry.

Little question: which is the separator character set in your PC?

Edited May 3, 2018 by FrancescoDiMuro

[SOF-TECH]

Hi FrancescoDiMuro

Actually the location of the file csv will be @WorkingDir & "\" & @ComputerName & "_INFO.csv  in the future.

the above code is just simulation of the real one. I do nott know how to write into excel. that's it.

Thank you

[JLogan3o13]

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:

expandcollapse popup

#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 May 3, 2018 by JLogan3o13

[Juvigy]

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?

[SOF-TECH]

Hi JLogan3o13

Amazing, I followed your method and worked like o'clock. And as you said .xlsx is better than CSV. 

I really appreciate it. 

Thank you all by the way

 

[SOF-TECH]

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:

expandcollapse popup

#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:

expandcollapse popup

#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 

[JLogan3o13]

@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?

[SOF-TECH]

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 

[Juvigy]

Have you tried with #requireadmin ?

[SOF-TECH]

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.

 

[JLogan3o13]

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