Strip @CRLF from csv in certain situations

[realshyfox]

Hy,

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

lines|4412145215456

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

[Subz]

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

expandcollapse popup

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

_GetCsvData()

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)]
                _ArrayTranspose($aFileLine)
                _ArrayAdd($aCsvData, $aFileLine)
            EndIf
        Next
        FileMove($aInbox[$i], StringReplace($aInbox[$i], $sInbox, $sOutbox), 9)
    Next

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

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

Edited February 25, 2020 by Subz

[realshyfox]

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.

[Subz]

Did you use my script above?  Because it worked fine for me.

[Nine]

There is a bug in that script.  Replace IsNumber(Number($aFileLine[0]))

by StringIsDigit($aFileLine[0])

Should work after that...

Edited February 25, 2020 by Nine

[mikell]

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)

 

[Nine]

There is also a small bug in that script too.  You may end up with a sequence of 2 or more spaces.  StringStripWS could solve the problem

[mikell]

This one works with lines beginning with any number of digits followed by a pipe 

$txt = FileRead("test.csv")
$txt = StringRegExpReplace($txt, '(?m)\h+|\R+(?!^\d+\|)', " ")
Msgbox(0,"", $txt)

Edit
added : reduce 1 or more horizontal ws to one only

Edited February 25, 2020 by mikell

[realshyfox]

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!

[mikell]

1 minute ago, realshyfox said:

I just tried your version

The 2nd one is better

[junkew]

As you use vbs make the whole solution in vbs. 

http://eddiejackson.net/wp/?p=8619

https://www.devguru.com/content/technologies/VBScript/regexp-replace.html

[realshyfox]

12 hours ago, junkew said:

As you use vbs make the whole solution in vbs. 

http://eddiejackson.net/wp/?p=8619

https://www.devguru.com/content/technologies/VBScript/regexp-replace.html

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!