Jump to content
Sign in to follow this  
Jewtus

Filewriteline creating extra lines

Recommended Posts

Jewtus

I'm trying to do a few queries and have them record the results into a CSV file but I'm running into an unusual error. When I had my code write a line at the end of all the queries, I got a ton of duplicated data, so I tried to tweak when it writes to the file to eliminate redundancies. I changed the code so now it doesn't do a @CRLF until the end of the set of queries and it looks perfect on the console output, but when I open the file itself, it has all sorts of spacing.

Here is the code:

$sqlRs2.open ($Query2, $sqlCon)
While Not $sqlRs2.EOF
    $Field1= $sqlRs2.Fields ('MPR').Value
    $Field2 = $sqlRs2.Fields ('ENT').Value
    $Field3 = $sqlRs2.Fields ('ROD').Value
    $Field4 = $sqlRs2.Fields ('ONTR').Value
    $Field5 = $sqlRs2.Fields ('OCAL').Value
    $EndDate = $sqlRs2.Fields ('End').Value
    $StartDate = $sqlRs2.Fields ('Start').Value
    ConsoleWrite($Field1 & "|" & $Field2 & "|" & $Field3 & "|" & $Field4 & "|" & $Field5 & "|" & DTFormat($StartDate) & "|" & DTFormat($EndDate) & "|") ; Write results to file
    FileWriteLine($fOutFile, $Field1 & "|" & $Field2 & "|" & $Field3 & "|" & $Field4 & "|" & $Field5 & "|" & DTFormat($StartDate) & "|" & DTFormat($EndDate) & "|") ; Write results to file
    $sqlRs6.open ($Query6, $sqlCon)
    While Not $sqlRs6.EOF
        $ID = $sqlRs6.Fields ('ERS').Value
        $Type = $sqlRs6.Fields ('ERS1').Value
        $DBRelationship = $sqlRs6.Fields ('ERT').Value
        DBRelationship()
        If $Type = 'F' Then
            $sqlRs7.open ($query7, $sqlCon)
            $Value1 = StringReplace($sqlRs7.Fields ('RIN' ).Value," ","")
            $Value2 = StringReplace($sqlRs7.Fields ('R70' ).Value," ","")
            $Value3 = StringReplace($sqlRs7.Fields ('OM' ).Value," ","")
            $Other = $Value & " " & $Value2 & " " & $Value3
            ConsoleWrite($Other & " " & $DBRelationship & "; ") ; Write results to file
            FileWriteLine($fOutFile, $Other & " " & $DBRelationship & "; ") ; Write results to file
            $sqlRs7.close
        EndIf
        $sqlRs6.MoveNext
    WEnd
    ConsoleWrite(@CRLF) ; Write results to file
    FileWriteLine($fOutFile, @CRLF)
    $sqlRs6.close
    $sqlRs2.MoveNext

The console out looks like this:

 

Example|Data|Is|Easy|Guys|1/1/2014|1/2/2014|This Should Work OWNER; So Should This NONOWNER; 

 

and in the file itself, this is what I get:

 

Example|Data|Is|Easy|Guys|1/1/2014|1/2/2014|

This Should Work OWNER; 

So Should This NONOWNER;

 

 

I tried opening it in notepad++ and I can confirm that there is a @CRLF at the end of each of the lines.

Does filewriteline only create a new line? How would I get my file to look like the console output?

Share this post


Link to post
Share on other sites
BrewManNH

FileWriteLine automatically adds the @CRLF to every line you write with it. It's in the help file. If you want to combine the lines into one, you should only write it after you've combined the text and then write it once instead of every line.

  • Like 1

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites
Jewtus

FileWriteLine automatically adds the @CRLF to every line you write with it. It's in the help file. If you want to combine the lines into one, you should only write it after you've combined the text and then write it once instead of every line.

I had actually realized that right as you posted (I came on to post a facepalm, but you had already gotten to it :P)

Thanks!

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  

  • Similar Content

    • MrCheese
      By MrCheese
      hi all,
      reviewing the forum, this thread is applicable: 
       
       
      I wanted to know if there is now a better way to do this?
      In essence, I load a tab delimited txt file into an array (works well). I used tab, as some fields in the original csv contains commas.
      However, I needed autoit to manipulate this array, and output it as a csv.
      IF my array contains items with a comma, without double quotes around the field, then how best do I get a csv out of this?
      My current workaround is to filewritefromarray tab delimited, then open it in excel and save as a csv. I will need to check this to see how the address fields behave that contain a comma.
       
      Any thoughts would be appreciated.
       
    • SOF-TECH
      By 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
    • ahha
      By ahha
      It took a month of cutting down a big program to find it and here's the code to illustrate "Error allocating memory".
      The first while using the filename in the fileopen, etc. shows the error.  The second while example does not have the problem (so I switched to using it).
      ;shows "Error allocating memory" ;Autoit v3.3.14.5 #AutoIt3Wrapper_run_debug_mode=Y ; use this to debug in console window <--- LOOK #include <FileConstants.au3> ;--- shows error allocating memory - is it Autoit or SciTE ? Try compiling <-- makes no difference so probably not SciTE. Normally error at index 50K-100K $fnINDEX = @ScriptDir & "\__index.txt" $i = 0 ;#cs While 1 $i = $i + 1 FileOpen($fnINDEX, $FO_OVERWRITE) ;overwrite as we just need last one processed FileWriteLine($fnINDEX, $i) FileClose($fnINDEX) If $i/1000 = Int($i/1000) Then MsgBox(0, "count", "$i = " & $i, 1) ;give indication of count, hangs when memory allocation error imminent WEnd ;#ce ;comment out the above loop and this works without an allocation error - looks like the above code leaking memory? While 1 $i = $i + 1 $fh = FileOpen($fnINDEX, $FO_OVERWRITE) ;overwrite as we just need last one processed FileWriteLine($fh, $i) FileClose($fh) If $i/1000 = Int($i/1000) Then MsgBox(0, "count", "$i = " & $i, 1) ;give indication of count WEnd Exit ;program  
    • ShawnW
      By ShawnW
      I have a script that takes a large excel file, pulls out and reorganizes certain information I need, and spits out a trimmed down csv file which I uses to upload the information on my website. Some of this information contains characters with accents or em dashes. By default it would create a csv file in ANSI which I then uploaded but had to tell my website import system it was windows-1252 in order for it to look correct.
      This was all working fine except now I need to add in a non-breaking space and non-breaking hyphen into parts of my output. At first I tried using ChrW(0xA0) and ChrW(0x2011) as replacements. A quick test in the console looked correct, however opening the csv output in notepad++ showed the space correctly but a ? for the hyphen and the file was still encoded as ANSI. I tried to view it as UTF-8 instead but this just made the space appear as xAO and also other characters appeared that way like my em dashes appeared as x97 and another symbol as xA7 etc.
      If I instead do a convert to UTF-8 from notepad++ then those problems go away except the hyphen still displays as ?. I then noticed on the page I linked for the non-breaking hyphen it lists the UTF-8 hex as 0xE2 0x80 0x91 (e28091). I was unsure how to enter this in autoit but several things i tried all failed to get the hyphen inserted.
      I need a way to get both the space and hyphen added correctly as either ANSI or UTF-8, but if it is UTF-8 then I need a way to convert all of the other data I extracted from the excel file.
      I've included a test excel file with a single line and test script to create a csv demonstrating the problem.
      test.xlsx
      test.au3
    • fopetesl
      By fopetesl
      I have a csv file with delimiters "," and @CRLF
      After trying several different examples (simple ones!) I still haven't resolved an answer
      #include <Array.au3> #include <File.au3> ;#include "ArrayMultiColSort.au3" Global $BatchDir = "C:\ncat\" FileChangeDir($BatchDir) $sFile = "mod1.csv" ; Read file into a 2D array Global $aArray _FileReadToArray($sFile, $aArray, $FRTA_NOCOUNT, ",") ; And here it is _ArrayDisplay($aArray, "Original", Default, 8) Firstly it throws a MsgBox error "No array variable passed to function" _ArrayDisplay(), so no displayed data
      Second and probably more important how do I get _FileReadToArray() to split the imported array[][] into rows and columns?
      I tried "," & @CRLF without success.
×