I have CSVDocument working, but something odd is happening.

Everything is coming out as I expect, Except for the first cell.. it has some garbage at the beginning of it that I don't understand. I'm on a Windows 10 64bit PC running the Win32 FPC Trunk installed today with fpcdeluxe.

Here's my code:

Uses Math,sysutils,Classes,CSVDocument;
Var
   LoopRow,LoopCol:DWord;
   CSVData   :  TCSVDocument;
Begin
   If ParamCount>=1 Then
      Begin
         Writeln('Input From File: ',Paramstr(1));
         If FileExists(ParamStr(1)) Then
            Begin
               CSVData:=TCSVDocument.Create;
               CSVData.LoadFromFile(ParamStr(1));
               Writeln('Read In: ',CSVData.RowCount,' Rows and ',CSVData.MaxColCount,' Columns');
               Writeln('Press Enter to Process');
               Readln;
               If CSVData.RowCount >0 Then
                  Begin
                     For LoopRow := 0 to CSVData.RowCount-1 Do
                        Begin
                           Write('| ');
                           For LoopCol := 0 to CSVData.ColCount[LoopRow]-1 Do
                              Begin
                                 Write(CSVData.Cells[LoopCol,LoopRow]+' | ');
                              End;
                           Writeln;
                        End;
                  End;
               CSVData.Free;
            End
         Else
            Begin
               Writeln('File Not Found');
            End;
      End;
End.

Here's the output:
Input From File: x:\all_2x4s.csv
Read In: 88 Rows and 3 Columns
Press Enter to Process
| Divisions 2x4 CBC B-C1 | 30.56 | 50 |
| Divisions 2x4 CBC B-C2 | 30.56 | 50 |
| Divisions 2x4 CBC C1-C2 | 28.2 | 50 |
| Divisions 2x4 CDF C2-D1 | 34.89 | 40 |
| Divisions 2x4 CDF C2-F | 30.42 | 40 |
| Divisions 2x4 CDF D1-F | 33.47 | 40 |

Here's the top of my CSV File as created by Excel: Divisions 2x4 CBC B-C1,30.56,50
Divisions 2x4 CBC B-C2,30.56,50
Divisions 2x4 CBC C1-C2,28.2,50
Divisions 2x4 CDF C2-D1,34.89,40
Divisions 2x4 CDF C2-F,30.42,40
Divisions 2x4 CDF D1-F,33.47,40

I don't understand where the  came from or how to get rid of it. I've tried several CSV files, and even tried just typing a sample out with a text editor.. all have  in the first cell

Anyone have any ideas?

James

Hello,

Sounds like you have a BOM marker in the file.

Set
   CSVDocument.DetectBOM := True;

Michael.

I tried this:

               CSVData:=TCSVDocument.Create;
               CSVData.DetectBOM := True;
               CSVData.LoadFromFile(ParamStr(1));
               Writeln('Read In: ',CSVData.RowCount,' Rows and ',CSVData.MaxColCount,' Columns');
               Writeln('Press Enter to Process');
               ...  same as before...
And I get 1d.pas(165,28) Error: Identifier not found "DetectBOM"
I also tried:
               CSVDocument.DetectBOM := True;
               CSVData:=TCSVDocument.Create;
               CSVData.LoadFromFile(ParamStr(1));

And I also get Error: Identifier not found "DetectBOM"

It does seem like there is something there that I can't see with Notepad++ I copied everything, created a new file, Pasted everything in and saved it, and the mysterious characters are not there anymore.

James

Strange, I have it here ? Maybe it was introduced later...

Notepad++ detects the BOM Marker but obviously does not show it.

Michael.

Strange, I have it here ? Maybe it was introduced later...

Maybe I need to use another unit?

Notepad++ detects the BOM Marker but obviously does not show it.

I even tried going to View and Show Symbol and Show All Characters in Notepadd ++ so I see all the CRs and LFs etc.. but nothing is shown at the beginning of the file.

James

James Richters wrote

> Strange, I have it here ? Maybe it was introduced later... Maybe I need to use another unit?

No. :-(

It exists on TCSVParser only, not on TCSVDocument. That's a pity, because TCSVDocument should to pass it on to the TCSVParser.

I have fixed this in trunk.

> Notepad++ detects the BOM Marker but obviously does not show it. I even tried going to View and Show Symbol and Show All Characters in Notepadd ++ so I see all the CRs and LFs etc.. but nothing is shown at the beginning of the file.

I don't think it will do this, as the BOM Marker is not a character per se...

The presence of a BOM marker is shown in the Encoding menu - the menu item corresponding to the file type is checked: UTF-8-BOM for example.

Michael.

I have fixed this in trunk.

I updated to the current trunk and indeed your fix has solved the issue... for excel files, however if I try it with a text file.. something that I type in from scratch with a text editor, it crashes with:

Running "i:\programming\gcode\1d cut\1d.exe x:\text.CSV" Input From File: x:\text.CSV
An unhandled exception occurred at $0041F2C8:
EStreamError: Invalid stream operation TReadBufStream.Seek $0041F2C8
$00430BBB
$0041F01C
$0042D6E2
$0042CDFF
$0042CD6B

It seems to be CSVData.LoadFromFile(ParamStr(1));
that causes the crash, because I have a writeln right under that and that never happens.  I don't know why I'm not getting the backtrace, it is turned on.

But if I remove
CSVData.DetectBOM := True;
Then it's fine.... for text files.. still garbage in Excel files.

Apparently DetectBOM requires for it to be there? Seems like if it's detecting, it should detect it's absence and it should be fine.

Is there a way around this? Maybe there is a way I can check for this BOM thing and activate DetectBOM if it's there?

I would like my program to function for an exported CSV file from excel or from something input into a text editor either way and just work.

Thank you for your help

James

Thanks for the link and the work around idea.

I really don't know what settings I had for the CSV file, I just clicked Save-As and changed it to CSV... I didn't even know there was an ASCII option.  The newer versions of Excel make you jump though hoops just to see where on your hard drive you are saving the thing.. never mind what the setting are...  And I guess that's the point... no one knows anything about BOM and that there are some CSV files that have it and Some CSV files that don't and setting DetectBOM := True; should not cause a fatal crash if your user tries to use an ASCII CSV file, and the user should not get garbage if he uses a UTF-8 CSV File... the real problem is that programmers may not know this, and may not have an issue at all.. maybe they happen to set DetectBOM := True; and never think to test it with an ASCII CSV file.. why would they?  But later on some other user keeps getting crashes and no one has any idea why it's crashing... the files look the same in a text editor after all.   So I Really think that DetectBOM should also detect ASCII and no BOM..  and then DetectBOM should be set to TRUE by default... then the entire CSVDocument system always works for everyone and we never will hear about BOM issues ever again...

I really like the way CSVDocument works, and I'll stick with it now that I have it figured out, it's just a shame that all this BOM stuff made it much more complicated than it needed to be.

Anyway, I fixed the issue as suggested like this:

Uses sysutils,CSVDocument;
Var
   LoopRow,LoopCol:DWord;
   CSVData        : TCSVDocument;
   CheckFile      : File of Byte;
   CheckByte      : Array[1..4] of Byte;
Begin
   If ParamCount>=1 Then
      Begin
         Writeln('Input From File: ',Paramstr(1));
         If FileExists(ParamStr(1)) Then
            Begin
               CSVData:=TCSVDocument.Create;
               Checkbyte[1]:=$55;
               Checkbyte[2]:=$55;
               Checkbyte[3]:=$55;
               Checkbyte[4]:=$55;
               Assign(CheckFile,ParamStr(1));
               Reset(CheckFile);
               If Not(Eof(CheckFile)) Then
                  Begin
                     Read(Checkfile,Checkbyte[1]);
                     Writeln('$'+HexStr(Checkbyte[1],2));
                     If Not(Eof(CheckFile)) Then
                        Begin
                           Read(Checkfile,Checkbyte[2]);
                           Writeln('$'+HexStr(Checkbyte[2],2));
                           If Not(Eof(CheckFile)) Then
                              Begin
                                 Read(Checkfile,Checkbyte[3]);
                                 Writeln('$'+HexStr(Checkbyte[3],2));
                                 If Not(Eof(CheckFile)) Then
                                    Begin
                                       Read(Checkfile,Checkbyte[4]);
                                       Writeln('$'+HexStr(Checkbyte[4],2));
                                    End;
                              End;
                        End;
                  End;
               If (CheckByte[1]=$EF) AND (CheckByte[2]=$BB) AND (CheckByte[3]=$BF)  Then
                  Begin
                     Writeln('UTF-8 Byte Order Mark Detected');
                     CSVData.DetectBOM := True;
                  End;
               If ((CheckByte[1]=$FE) AND (CheckByte[2]=$FF)) OR ((CheckByte[1]=$FF) AND (CheckByte[2]=$FE)) Then
                  Begin
                     Writeln('UTF-16 Byte Order Mark Detected');
                     CSVData.DetectBOM := True;
                  End;
               If ((CheckByte[1]=$00) AND (CheckByte[2]=$00) AND (CheckByte[3]=$FE) AND (CheckByte[4]=$FF)) OR ((CheckByte[1]=$FF) AND (CheckByte[2]=$FE) AND (CheckByte[3]=$00) AND (CheckByte[4]=$00)) Then
                  Begin
                     Writeln('UTF-32 Byte Order Mark Detected');
                     CSVData.DetectBOM := True;
                  End;
               Close(CheckFile);
               CSVData.LoadFromFile(ParamStr(1));
               Writeln('Read In: ',CSVData.RowCount,' Rows and ',CSVData.MaxColCount,' Columns');
               Writeln('Press Enter to Process');
               Readln;
               If CSVData.RowCount >0 Then
                  Begin
                     For LoopRow := 0 to CSVData.RowCount-1 Do
                        Begin
                           Write('| ');
                           For LoopCol := 0 to CSVData.ColCount[LoopRow]-1 Do
                              Begin
                                 Write(CSVData.Cells[LoopCol,LoopRow]+' | ');
                              End;
                           Writeln;
                        End;
                  End;
               CSVData.Free;
            End;
      End;
End.

The part of my program that checks for the BOM is bigger than the actual CSV read and write. Please let me know if the crash issue with no BOM gets fixed so I can delete all this stuff that checks for it.

James

PM James Richters wrote

The part of my program that checks for the BOM is bigger than the actual CSV read and write. Please let me know if the crash issue with no BOM gets fixed so I can delete all this stuff that checks for it.

That part can be a lot shorter:
Open the file as a TFileStream
Read the amount of bytes the UTF8-BOM has int an appropriate buffer. Check you really read that amount of bytes.
Compare the buffer to the expected UTF8-BOM.

This is a snippet from a function I have lying around:

    FS := TFileStreamUtf8.Create(Fn, fmOpenRead or fmShareDenyNone);
    try
      Len := FS.Read({%H-}Buf[0], BufLen);
      if (Len > 2) and (Buf[0]=$EF) and (Buf[1]=$BB) and (Buf[2]=$BF) then
      begin
        //UTF8 BOM