GAMBAS BOOK 3.19.1

In many programs it is possible to save data in a csv file for further processing in another program. You probably know this from working with a calculation program. Problems with importing data from a csv file always occur when you use

• is not sure which text and field separators were used for data export,
• does not know if the first record in the csv file contains field names or
• a text and field separator is contained in a field. This is the case, for example, if the system localization is set to German and real numbers are stored with a comma as decimal separators.

The first variant for importing data from a csv file is based on an (English) localization in which real numbers with a dot are stored as decimal separators. The project consists of the main program from which a form for determining the text and field separators can be called and a module. The module provides the program with a data matrix via 2 functions in which all field contents from the csv file are stored. Ideal conditions for inserting and displaying the data'en bloc' in the GridView at lightning speed.

Figure 17.7.7.1: Main program with GridView

First select the csv file (file filter) in a dialog:

Figure 17.7.7.2: FileOpen dialog

Figure 17.7.7.3: Defining the separators

In the form above, only the first two lines of the csv file are displayed so that you can recognize and select:

• which text and field separators were used in the csv file andwhether the first record in the csv file contains field names.

Finally, the csv file is opened and its field contents and possibly also the field names are saved in a data matrix and their contents are immediately inserted and displayed in the GridView:

Figure 17.7.7.4: Display of fields - here with field names - in the GridView

Of particular interest is the source code of the module, which is completely specified, while only 2 procedures are commented by the main program:

[1]PUBLIC hFile AS File
[2]PUBLIC sFeldTrennzeichen AS String
[3]PUBLIC sTextTrennzeichen AS String
[4]PUBLIC bExistTitel AS Boolean
[5]PUBLIC bFileOpenCancel AS Boolean
[6]PUBLIC iFieldCount AS Integer
[7]PUBLIC aPreView AS Variant[]
[8]PUBLIC aMatrix AS Variant[]
[9]
[10]PUBLIC FUNCTION FileOpen() AS Boolean
[11]  DIM sLine AS String
[12]  
[13]  Dialog.Title = "Importieren Sie eine csv-Datei!"
[14]  Dialog.Filter = ["*.csv", "csv-Dateien"]
[15]  IF Dialog.OpenFile(FALSE) = TRUE THEN ' Multiselect=False (Standard)
[16]     Message.Info("Das Öffnen der csv-Datei wurde abgebrochen!")
[17]     RETURN FALSE ' Cancel-Button gedrückt
[18]  ELSE 
[19]     hFile = OPEN Dialog.Path FOR INPUT 
[20]     IF Lof(hFile) = 0 THEN 
[21]        Message.Info("Die csv-Datei ist leer!")
[22]        hFile.Close
[23]        RETURN FALSE
[24]     ELSE 
[25]        aPreView = NEW Variant[]    
[26]        WHILE NOT Eof(hFile)
[27]          LINE INPUT #hFile, sLine
[28]          aPreView.Add(sLine)
[29]        WEND 
[30]     ENDIF ' Lof(File) = 0?     
[31]  ENDIF ' Dialog.OpenFile(FALSE) = TRUE?  
[32]' Wenn KEIN Fehler eintrat, dann ...
[33]  RETURN TRUE   
[34]END ' FileOpen()
[35]
[36]PUBLIC FUNCTION ImportCSV2Matrix(pTextTrenner AS String, pFeldTrenner AS String) AS Boolean
[37]  DIM sLine AS String
[38]  DIM iCount AS Integer
[39]  DIM aFieldList AS String[] ' Der Typ ist durch den Funktionswert von Split() festgelegt
[40]  
[41]  aMatrix = NEW Variant[]  
[42]  seek">SEEK #hFile, 0 ' Notwendig, weil das existierende Datei-Handle genutzt wird   
[43]  WHILE NOT Eof(hFile)
[44]    LINE INPUT #hFile, sLine
[45]    sLine = Replace(sLine, pTextTrenner, "")
[46]    aFieldList = NEW String[] ' Für jede Zeile wird ein neues Array genutzt
[47]    IF iCount = 0 THEN 
[48]       iFieldCount = Split(sLine, pFeldTrenner).Count
[49]       INC iCount
[50]    ENDIF ' iCount = 0
[51]    aFieldList = Split(sLine, pFeldTrenner) ' Lokalisation englisch!
[52]    aMatrix.Add(aFieldList)
[53]  WEND ' NOT Eof(hFile)    
[54]' Wenn KEIN Fehler eintrat, dann ...
[55]  RETURN TRUE   
[56]END ' ImportCSV2Matrix

• In lines 25 to 29, a matrix aPreView is filled with the original data records from the csv file. This matrix is used in the CSV file2Matrix form to preview the first two lines in the csv file.
• In line 48, the number of fields is determined and required for determining the number of columns in the GridView. The number of rows in the GridView corresponds to the number of elements in the matrix with the name aMatrix.The division of a data set into line 51 using the field separator returns individual strings that are inserted as elements into a matrix aFieldList of type String[]. * If no errors occurred in either function, the GridView is immediately filled with the data from aMatrix, as can be seen in Figure 17.7.7.4. The proven GridView_Data event is also used here:

PUBLIC SUB GridView1_Data(Row AS Integer, Column AS Integer)
  GridView1.Data.Text = MCSV.aMatrix[Row][Column]
  IF Row MOD 2 = 1 THEN 
     GridView1.Data.Background = Color.RGB(224, 224, 224) ' hellgrau
  ENDIF ' MOD 2 = 0?  
END ' GridView1_Data(..)

In the main program, it is the function ImportCSVFile() and the procedure DisplayGridView() with which you implement the FileOpen dialog, saving the imported field data in a matrix, setting the GridView properties and displaying the field contents in the GridView:

PUBLIC FUNCTION ImportCSVFile() AS Boolean
  IF MCSV.FileOpen() = FALSE THEN 
     'Message.Warning("Fehler beim Öffnen der CSV-Datei!")
     RETURN FALSE
  ENDIF ' MCSV.FileOpen() = FALSE?  
  FImport.ShowModal()
  IF MCSV.bFileOpenCancel = TRUE THEN
     Message.Warning("Daten-Import abgebrochen!")
     RETURN FALSE
  ENDIF ' MCSV.bFileOpenCancel = TRUE?   
  IF MCSV.ImportCSV2Matrix(MCSV.sTextTrennzeichen, MCSV.sFeldTrennzeichen) = FALSE THEN 
     Message.Warning("Fehler beim Daten-Import aus der CSV-Datei!")
     RETURN FALSE
  ENDIF ' Fehler beim Auslesen der csv-Datei?  
' Wenn KEIN Fehler eintrat, dann ...
  RETURN TRUE  
END ' ImportCSVFile()
 
PUBLIC SUB DisplayGridView()
  IF ImportCSVFile() = TRUE THEN 
     SetGridProperty(MCSV.iFieldCount, MCSV.bExistTitel, MCSV.aMatrix[0])
     IF MCSV.bExistTitel = TRUE THEN MCSV.aMatrix.Remove(0)    
     GridView1.Rows.Count = MCSV.aMatrix.Count ' Erst jetzt wird das Gitter mit Werten gefüllt!
     GridView1.SetFocus
     GridView1.MoveTo(0, 0)
  ELSE 
    Message.Warning("Es ist ein Fehler aufgetreten!")
  ENDIF ' ImportCSVFile() = FALSE?  
END ' DisplayGridView

The data import from a csv file with the content below works without error messages and results in the display inserted afterwards in a GidView, because the number contains a field separator:

"Zahl","Wahrheitswert","Zeichenkette","Datum"
"83,21","False","Anna","14.01.2015"
"8,015","False","Hans","24.06.2014"

Figure 17.7.7.5: Incorrect display of fields in the GridView

On a system with German localization you will always have to deal with this problem. The solutions can be manifold, because there can be very different approaches. One would be to use a different field separator, although the default is the comma.

In the presented solution a DEA (Deterministic Finitestate Automat) is used in the module. Therefore you only need to exchange the function ImportcSV2Matrix from the previous project for the following one:

[1]PUBLIC FUNCTION ImportCSV2Matrix(pTextTrenner AS String, pFeldTrenner AS String) AS Boolean
[2]  DIM sLine, sZustand, sField, sMitteilung, sLeer AS String
[3]  DIM sEingabeZeichen AS String
[4]  DIM iCount, iZeile, k, i AS Integer
[5]  DIM aFieldList AS Variant[] 
[6]  DIM aFieldsList AS Variant[] 
[7]  
[8]  aFieldList = NEW Variant[]
[9]  aFieldsList = NEW Variant[]
[10]  aMatrix = NEW Variant[]
[11]  
[12]  sZustand = "z0"
[13]  sField = ""  
[14]  seek">SEEK #hFile, 0
[15]
[16]  WHILE NOT Eof(hFile)
[17]    INC iZeile
[18]    LINE INPUT #hFile, sLine
[19]    sLine = Trim(sLine)
[20]    IF NOT Eof(hFile) THEN     
[21]       sLine &= Chr(30) ' Chr(30)=Record Separator
[22]    ELSE 
[23]       sLine &= Chr(3) & Chr(30) ' Chr(3)= ETX (End of text)
[24]    ENDIF ' NOT Eof(hFile)?
[25]
[26]  ' Anfang Deterministischer Endlicher Automat  
[27]    FOR iCount = 1 TO Len(sLine)
[28]        sEingabeZeichen = Mid(sLine, iCount, 1)
[29]        SELECT sZustand
[30]        '-------------------------------------------------------------------------------
[31]        CASE "z0"
[32]            IF sEingabeZeichen = pTextTrenner THEN 
[33]               sZustand = "z1"
[34]            ELSE 
[35]               sZustand = "zF"
[36]            ENDIF 
[37]        '-------------------------------------------------------------------------------
[38]        CASE "z1"            
[39]            IF sEingabeZeichen <> pTextTrenner THEN sField &= sEingabeZeichen            
[40]            IF sEingabeZeichen = pTextTrenner AND icount < Len(sLine) THEN
[41]               aFieldList.Add(sField)
[42]               sField = ""
[43]               sZustand = "z2"
[44]            ENDIF 
[45]        '-------------------------------------------------------------------------------
[46]        CASE "z2"
[47]            IF sEingabeZeichen = Chr(30) THEN 
[48]               sZustand = "z0"
[49]               aFieldList.Add(sField)
[50]               aFieldsList.Add(aFieldList)
[51]               aFieldList = NEW Variant[]
[52]            ENDIF             
[53]            IF sEingabeZeichen = pFeldTrenner THEN sZustand = "z0"
[54]            IF sEingabeZeichen = Chr(3) THEN sZustand = "z3"
[55]            IF sEingabeZeichen <> pFeldTrenner AND sEingabeZeichen <> Chr(30) AND /
[56]               sEingabeZeichen <> Chr(3) THEN 
[57]               sZustand = "zF"
[58]            ENDIF 
[59]        '-------------------------------------------------------------------------------
[60]        CASE "z3"
[61]             IF sEingabeZeichen = Chr(30) THEN 
[62]              ' Message.Info("<center><font color='DarkGreen'><b>Der Endzustand ist erreicht!</b><hr>Fehler = NULL</font></center>")
[64]                aFieldsList.Add(aFieldList)
[65]                aMatrix = aFieldsList
[66]             ENDIF 
[67]        '-------------------------------------------------------------------------------
[68]        CASE "zF" 
[69]            sMitteilung = "<font color='Red'><b>Fehler!</b></font><br />"
[70]            sMitteilung &= "<hr>Zeile = " & Str(iZeile) & "<br />" 
[71]            sMitteilung &= "Zeichen = " & Str(iCount - 1)
[72]            Message.Error(sMitteilung) 
[73]            hFile.Close
[74]            RETURN FALSE            
[75]        END SELECT 
[76]    NEXT ' iCount    
[77]' Ende Deterministischer Endlicher Automat   
[78]  WEND ' NOT Eof(hFile)
[79]  
[80]  hFile.Close  
[81]  iColumnCount = aFieldList.Count
[82]' Wenn KEIN Fehler eintrat, dann ...
[83]  RETURN TRUE
[84]END ' ImportCSV2GridView(...)  

• In lines 20 to 24, the end of each data record and the end of the last data record are marked in a special way.
• The DEA is used in lines 26 to 77. You can see how a new state is assumed depending on the input character and the current state and something may also be output.
• The error state is taken if a line from the CSV file is syntactically incorrect. The function value FALSE is returned and the import is aborted.

Figure 17.7.7.6: Correct display of fields in the GridView (use DEA)

Let nothing and no one stop you to find your own solution in this case!