Jump to content

Strip @CRLF from csv in certain situations

Recommended Posts


I normaly resolve my own problems by searching it on help file but this is a matter that I didnt faced yet.

I have a csv with | as a delimiter.

0001|14442142|This is a simple description message||4412145215454

0010|85854542|A second description|The text that enforces the description|4412145215455
0212|35454159|A second description|This text is

formated so it has some end  of


0010|85854542|A third description||4412145215457

There are thousands of lines that are extracted everyday from a database and sent to me and I need to pass it to an excel.

Could you help me with this? Till now I used Notepad++ to replace the \n\r where I found that the next line didn´t start with the "xxxx|" pattern but everyday are more lines.

Any ideas are apreciated.

Thank you

Learn, learn and ... learn

Link to comment
Share on other sites

Have you tried _FileReadToArray?  You can then add the array directly into Excel, using the _Excel functions.

Here is a basic example:

  • Create a spreadsheet CsvData.xlsx in the root path of the script
  • Create two subfolders Inbox and Outbox
  • Add your csv files to be processed to Inbox folder
  • Run the script
  • Processed csv files are moved to Outbox folder
#include <Array.au3>
#include <Excel.au3>
#include <File.au3>


Func _GetCsvData()
    Local $aCsvData[0][0], $iColumns = 0
    ;~ XLSX Spreadsheet to hold CSV Data
    Local $sCsvData = @ScriptDir & "\CsvData.xlsx"
    ;~ Process files here
    Local $sInbox = @ScriptDir & "\Inbox"
    ;~ Move processed files here
    Local $sOutbox = @ScriptDir & "\Outbox"
    Local $aInbox = _FileListToArrayRec($sInbox, "*.csv", 1, 0, 0, 2)
    If @error Then Return SetError(1)
    Local $aFileData, $aFileLine
    For $i = 1 To $aInbox[0]
        _FileReadToArray($aInbox[$i], $aFileData)
            If @error Then ContinueLoop
        For $j = 1 To $aFileData[0]
            $aFileLine = StringSplit($aFileData[$j], "|", 2)
                If @error Then ContinueLoop
            If StringLen($aFileLine[0]) = 4 And StringIsDigit(($aFileLine[0]) Then
                If UBound($aFileLine) > $iColumns Then ReDim $aCsvData[UBound($aCsvData)][UBound($aFileLine)]
                _ArrayAdd($aCsvData, $aFileLine)
        FileMove($aInbox[$i], StringReplace($aInbox[$i], $sInbox, $sOutbox), 9)

    Local $oExcel = _Excel_Open()
    If @error Then Exit MsgBox(4096, "Excel Error", "Error Opening Excel")
    Local $oWorkbook = _Excel_BookOpen($oExcel, $sCsvData)
    If @error Then
        MsgBox(4096, "Excel Error", "Error Opening Excel Workbook : " & @CRLF & $sCsvData)
    Local $iLastRow = $oWorkbook.ActiveSheet.UsedRange.Rows.Count
    _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $aCsvData, "A" & $iLastRow)

Updated: IsNumber(Number($aFileLine[0])) with StringIsDigit(($aFileLine[0]) Thanks Nine

Edited by Subz
Link to comment
Share on other sites

Tried it Subz, still.... the @CRLF is treated as an end of line in all circumstances.  So the array is messed up

I need to find a way to verify if the next line its a pattern: "xxxx|" If it is then its a new line its not then I will replace it with "CRLF" and formate it further in the cell with a VBS script.

Learn, learn and ... learn

Link to comment
Share on other sites

Link to comment
Share on other sites

Link to comment
Share on other sites

8 minutes ago, mikell said:

Assuming that all the lines begin with 4 digits followed by a pipe, this should work

$txt = FileRead("test.csv")
$txt = StringRegExpReplace($txt, '\R+(?!\d{4}\|)', " ")
Msgbox(0,"", $txt)


I just tried your version and it works. It´s very simple but eficient! I will search more about regex because it seems I have more work to automate and this is the way to go for me.

I will try your script too subz . 

Thank you everybody!

Learn, learn and ... learn

Link to comment
Share on other sites

12 hours ago, junkew said:

Yes... That also helps but after the help given here I did it all in Autoit including the part of Excel VBS by merging the 2 solutions of mikell and Subz

I´m filling more confortable doing it with a generic interface in AutoIT and giving the options there.

Thank you all,  I really apreciate it!

Learn, learn and ... learn

Link to comment
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

  • Create New...