Announcement

Collapse
No announcement yet.

Excel csv versus MS DOS csv

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • #41
    Hi Dean,
    For the fun of it, I did have a look at this Excel "CSV" file.
    Code produced have no much comments, and need double checking.
    Set $FileNameNix and $FileNameWin with full name if there not in the same folder as the exe.
    Feel free to ask question if any interest.

    Tested quickly, conversion from Unicode to Ansi is automated, preserving the original file.
    Then the data is divided between 3 edit controls.
    There is a main info section.
    There is 9 groups made of two different type of data. They will be enumerated sequentially.
    In the second edit control field are separated by ";", one record by line.
    In the third edit control fields have an header and are separated by TAB, one record by line.
    An info static label will tell which group is currently shown.
    Have fun !

    Click image for larger version  Name:	csvExcel.png Views:	0 Size:	78.7 KB ID:	786970

    Code:
    'Set $FileNameNix and $FileNameWin with full name if there not in the same folder as the exe.
    
    #COMPILE EXE '#Win#
    #DIM ALL
    #INCLUDE "Win32Api.inc"
    #INCLUDE "CommCtrl.inc"
    #INCLUDE "RichEdit.inc"
    '#RESOURCE MANIFEST, 1, "XPTheme.xml"
    '#RESOURCE "x.pbr"
    
    $AppName     = "Read Excel file saved as CSV"
    $FileNameNix = "Gneiss cut block - Veined_20191125_171832.csv"
    $FileNameWin = "Gneiss cut block - Veined_20191125_171832-Ansi.csv"
    
    %Edit01      = 101
    %Edit02      = 102
    %Edit03      = 103
    %Label01     = 201
    
    GLOBAL hDlg    AS DWORD
    GLOBAL hEdit01 AS DWORD
    GLOBAL hEdit02 AS DWORD
    GLOBAL hEdit03 AS DWORD
    '_____________________________________________________________________________
    
    FUNCTION FileExist(sFileNameTry AS STRING) AS LONG
    
     '%INVALID_HANDLE_VALUE = &hFFFFFFFF???
     'FUNCTION = (GetFileAttributes(BYVAL STRPTR(sFileNameTry)) AND &hFF000010???) = %FILE_ATTRIBUTE_DIRECTORY 'Folder excluding file
     FUNCTION = (GetFileAttributes(BYVAL STRPTR(sFileNameTry)) AND &HFF000010???) = %FALSE 'File excluding folder
     'FUNCTION = (GetFileAttributes(BYVAL STRPTR(sFileNameTry)) < %INVALID_HANDLE_VALUE) 'File and folder
    
    END FUNCTION
    '______________________________________________________________________________
    
    SUB TextDel(BYVAL hEdit AS DWORD)
    
     'Erase all, Microsoft recommended way
      SendMessage(hEdit, %WM_SETTEXT, 0, BYVAL 0)
    
    END SUB
    '_____________________________________________________________________________
    
    SUB TextAdd(BYVAL hEdit AS DWORD, BYVAL sText AS STRING)
    
     'Move the caret to the end of text.
     SendMessage(hEdit, %EM_SETSEL, -1, -1)
    
     sText = sText & $CRLF 'Add a CRLF if needed
    
     'Insert the string at caret position.
     SendMessage(hEdit, %EM_REPLACESEL, %TRUE, BYVAL STRPTR(sText))
    
    END SUB
    '_____________________________________________________________________________
    
    FUNCTION FileConvert() AS LONG
     LOCAL sFileData AS STRING
     LOCAL LineCount AS DWORD
     LOCAL CrLfCount AS LONG
     LOCAL LfCount   AS LONG
     LOCAL CrCount   AS LONG
     LOCAL FileNo    AS LONG
    
     'Get data from file
     FileNo = FREEFILE
     OPEN $FileNameNix FOR BINARY AS FileNo
     SEEK FileNo, 1
     GET$ FileNo, LOF(FileNo), sFileData
     CLOSE FileNo
    
     'UNICODE to ANSI-EXTENDED conversion
     IF LEFT$(sFileData, 2) = CHR$(&HFF, &HFE) THEN
       sFileData = MID$(sFileData, 3) 'Remove BOM UNICODE indicator in text file - FF FE ÿþ
       sFileData = ACODE$(sFileData)  'UNICODE to ANSI
     END IF
    
     'CRLF conversion
     CrLfCount = PARSECOUNT(sFileData, $CRLF) - 1             'DOS/Windows file
     LfCount   = PARSECOUNT(sFileData, $LF)   - 1 - CrLfCount 'UNIX/Linux/BSD/MAC OS X
     CrCount   = PARSECOUNT(sFileData, $CR)   - 1 - CrLfCount 'Before MAC OS X
     'Convert to DOS/Windows file type
     IF CrLfCount = 0 THEN 'Make sure that file have only LF, if not re-evaluate the file...
       IF CrCount = 0 THEN
         IF LfCount > 0 THEN
           REPLACE $LF WITH $CRLF IN sFileData
         END IF
       END IF
     END IF
    
     LineCount = PARSECOUNT(sFileData, $CRLF)
    
     'Save new file to work with
     FileNo = FREEFILE
     OPEN $FileNameWin FOR BINARY AS FileNo
     SEEK FileNo, 1
     PUT$ FileNo, sFileData
     CLOSE FileNo
    
    END FUNCTION
    '____________________________________________________________________________
    
    FUNCTION FileRead() AS LONG
     LOCAL sDataLine    AS STRING
     LOCAL sDescription AS STRING
     LOCAL sInfo        AS STRING
     LOCAL sBuffer      AS STRING
     LOCAL sRow         AS STRING
     LOCAL FileNo       AS LONG
     LOCAL RowCount     AS LONG
     LOCAL index        AS LONG
     LOCAL Msg          AS TAGMSG
    
     'Get file data
     FileNo = FREEFILE
     OPEN $FileNameWin FOR INPUT AS FileNo
     DO
       LINE INPUT #FileNo, sDataLine
       IF sDataLine = "RAWDATA" THEN EXIT LOOP
       TextAdd(hEdit01, sDataLine)
     LOOP
     PostMessage(hEdit01, %EM_SETSEL, 0, 0) 'Deselect text
     PostMessage(hEdit01, %EM_SCROLLCARET, 0, 0)
    
     'Fill edit info
     LINE INPUT #FileNo, sDescription '> No. of Meas.    Meas.    Scan Position X [m]    Scan Position Y [m]    Signal Size    Signal ...
    
     DO 'Do all nine groups
       TextDel(hEdit02)
       TextDel(hEdit03)
    
       LINE INPUT #FileNo, sInfo
       sBuffer = MID$(sInfo,  INSTR(sInfo, "'") + 1) & $CRLF
       sInfo   = LEFT$(sInfo, INSTR(sInfo, "'") - 1)
    
       CONTROL SET TEXT hDlg, %Label01, "Meas. " & PARSE$(sInfo, ANY $TAB, 2) & " of " & PARSE$(sInfo, $TAB, 1) & _
       ", Scan Position X [m] " & PARSE$(sInfo, ANY $TAB, 3) & ", Scan Position Y [m] " & PARSE$(sInfo, ANY $TAB, 4) & _
       ", Signal Size " & PARSE$(sInfo, ANY $TAB, 5) & ", Signal " & PARSE$(sInfo, ANY $TAB, 6)
    
       DO
         LINE INPUT #FileNo, sDataLine
         IF LEFT$(sDataLine, 1) = $TAB THEN EXIT LOOP
         sBuffer = sBuffer & sDataLine '& $CRLF
       LOOP
    
       RowCount = PARSECOUNT(sBuffer, "'")
       FOR index = 1 TO RowCount
         sRow        = ">" & PARSE$(sBuffer, "'", index)
         TextAdd(hEdit02, sRow)
       NEXT
       PostMessage(hEdit02, %EM_SETSEL, 0, 0) 'Deselect text
       PostMessage(hEdit02, %EM_SCROLLCARET, 0, 0)
    
       DO
         sDataLine = LTRIM$(sDataLine, $TAB)
         TextAdd(hEdit03, sDataLine)
         LINE INPUT #FileNo, sDataLine
         IF LEFT$(sDataLine, 1) <> $TAB THEN EXIT LOOP
       LOOP
       PostMessage(hEdit03, %EM_SETSEL, 0, 0) 'Deselect text
       PostMessage(hEdit03, %EM_SCROLLCARET, 0, 0)
    
       FOR index = 0 TO 100
         DO WHILE PeekMessage(Msg, 0, 0, 0, %PM_NOREMOVE)
           DIALOG DOEVENTS 1 'Let DDT handle all pending messages
           SLEEP 200
         LOOP
       NEXT
    
       WinBeep(1500, 100)
       IF VAL(PARSE$(sInfo, ANY $TAB, 2)) = VAL(PARSE$(sInfo, $TAB, 1)) THEN
         WinBeep(1500, 100)
         EXIT LOOP
       END IF
       TextDel(hEdit02)
       TextDel(hEdit03)
     LOOP
    
     CLOSE FileNo
    
    END FUNCTION
    '______________________________________________________________________________
    
    CALLBACK FUNCTION DlgProc
     LOCAL  ClientWidth   AS LONG
     LOCAL  ClientHeight  AS LONG
     LOCAL  ControlHeight AS LONG
    
     SELECT CASE CBMSG
    
       CASE %WM_INITDIALOG
         PostMessage(hDlg, %WM_APP, 0, 0)
    
       CASE %WM_APP
         TextAdd(hEdit01, $AppName)
         TextAdd(hEdit01, $FileNameNix)
         TextAdd(hEdit01, $FileNameWin &  $CRLF)
    
         IF FileExist($FileNameWin) THEN
           FileRead()
         ELSE
           IF FileExist($FileNameNix) THEN
             CONTROL SET TEXT hDlg, %Label01, $FileNameNix & " to " & $FileNameWin & " convertion"
             FileConvert()
             FileRead()
           ELSE
             CONTROL SET TEXT hDlg, %Label01, "File not found!"
             TextAdd(hEdit02, $FileNameNix & " not found!")
           END IF
         END IF
    
       CASE %WM_SIZE
         IF CBWPARAM <> %SIZE_MINIMIZED THEN
           ClientWidth   = LOWRD(CBLPARAM)
           ClientHeight  = HIWRD(CBLPARAM)
           ControlHeight = ClientHeight / 3 - 15
           MoveWindow(hEdit01, 3, 3, ClientWidth - 6, ControlHeight, %TRUE)
           MoveWindow(GetDlgItem(hDlg, %Label01), 5, ControlHeight + 15, ClientWidth - 10, 20, %TRUE)
           MoveWindow(hEdit02, 5, ControlHeight + 40, ClientWidth - 6, ControlHeight, %TRUE)
           MoveWindow(hEdit03, 5, ControlHeight * 2 + 43, ClientWidth - 6, ControlHeight, %TRUE)
           InvalidateRect(hDlg, BYVAL %NULL, 0 ): UpdateWindow(hDlg)
         END IF
    
      END SELECT
    
    END FUNCTION
    '______________________________________________________________________________
    
    FUNCTION PBMAIN()
     LOCAL hIcon AS DWORD
     LOCAL hLib  AS DWORD
    
     DIALOG FONT "Segoe UI", 9
     DIALOG NEW %HWND_DESKTOP, $AppName, , , 450, 300, _
     %WS_CAPTION OR %WS_MINIMIZEBOX OR %WS_MAXIMIZEBOX OR %WS_SYSMENU OR %WS_THICKFRAME, 0 TO hDlg
    
     hIcon = ExtractIcon(GetModuleHandle(""), "C:\Windows\system32\moricons.dll", 109)
     SetClassLong(hDlg, %GCL_HICON, hIcon)
     SendMessage(hDlg, %WM_SETICON, %ICON_SMALL, hIcon)
    
     CONTROL ADD TEXTBOX, hDlg, %Edit01, "", 2, 2, 296, 100, %WS_CHILD OR %WS_VISIBLE OR _
     %WS_TABSTOP OR %ES_LEFT OR %ES_MULTILINE OR %ES_AUTOVSCROLL OR %WS_VSCROLL OR _
     %ES_NOHIDESEL OR %ES_WANTRETURN, %WS_EX_CLIENTEDGE OR %WS_EX_LEFT
     hEdit01 = GetDlgItem(hDlg, %Edit01)
    
     CONTROL ADD LABEL, hDlg, %Label01, "Please wait...", 2, 105, 50, 15, %SS_NOTIFY OR %SS_CENTER
    
     hLib = LoadLibrary("MsftEdit.dll") '4.1 Unicode
     CONTROL ADD "RichEdit50W", hDlg, %Edit02, "", 5, 5, 340, 230, _ 'RichEdit
     %WS_TABSTOP OR %WS_CHILD OR %WS_VISIBLE OR %ES_MULTILINE OR %WS_VSCROLL OR _
     %ES_SAVESEL OR %ES_EX_NOCALLOLEINIT OR %ES_AUTOVSCROLL OR %ES_AUTOHSCROLL OR _
     %WS_HSCROLL OR %ES_WANTRETURN OR  %ES_LEFT OR %ES_NOHIDESEL, %WS_EX_CLIENTEDGE
     hEdit02 = GetDlgItem(hDlg, %Edit02)
    
     CONTROL ADD "RichEdit50W", hDlg, %Edit03, "", 5, 5, 340, 230, _ 'RichEdit
     %WS_TABSTOP OR %WS_CHILD OR %WS_VISIBLE OR %ES_MULTILINE OR %WS_VSCROLL OR _
     %ES_SAVESEL OR %ES_EX_NOCALLOLEINIT OR %ES_AUTOVSCROLL OR %ES_AUTOHSCROLL OR _
     %WS_HSCROLL OR %ES_WANTRETURN OR %ES_LEFT OR %ES_NOHIDESEL, %WS_EX_CLIENTEDGE
     hEdit03 = GetDlgItem(hDlg, %Edit03)
    
     DIALOG SHOW MODAL hDlg CALL DlgProc
    
     FreeLibrary(hLib)
     DestroyIcon(hIcon)
    
    END FUNCTION
    '______________________________________________________________________________
    '
    Last edited by Pierre Bellisle; 1 Dec 2019, 07:51 PM.

    Comment

    Working...
    X