Working with CSV files

Post your Gambas programming questions here.
User avatar
stevedee
Posts: 518
Joined: Monday 20th March 2017 6:06pm

Re: Working with CSV files

Post by stevedee »

01McAc wrote: Thursday 25th February 2021 6:22pm ...Too late to change it...
...then I think you will just have to access the headers like this:-
  strHeaders = File.Load("/home/steve/Gambas/camera_or_whatever.csv")
  strHeaders = Mid(strHeaders, 1, InStr(strHeaders, "\n"))
...then carry on using the CSV code.

For File.Load you will need gb.Utils
01McAc
Posts: 75
Joined: Sunday 24th January 2021 8:25pm

Re: Working with CSV files

Post by 01McAc »

Thank you, works like a charm.
01McAc
Posts: 75
Joined: Sunday 24th January 2021 8:25pm

Re: Working with CSV files

Post by 01McAc »

One more question re the format.

the original CSV file looks like this, text is always in double quotes:
ID,ID_Lens,Purchase_date,Purchase_price,Purchase_serial_no,Purchase_from,Purchase_lens_colour,Purchase_lens_condition,Purchase__comment,DateCreated,DateChanged
14,100,2019-04-25,"700€",159357,"Fotoshop Berlin",Black,Rosetta,,"2021-02-25 10:41:37","2021-02-25 10:42:51"


When I read the lines with ..
Do Until myCSVfile.Eof
            colRecords[myCSVfile.Line] = myCSVfile.Read()  'read every line into a collection
Loop
..there are obvious no more double quotes in colRecords[myCSVfile.Line]. Can I get a line of the CSV file with the original double qoutes?
User avatar
stevedee
Posts: 518
Joined: Monday 20th March 2017 6:06pm

Re: Working with CSV files

Post by stevedee »

01McAc wrote: Saturday 27th February 2021 5:35pm ...Can I get a line of the CSV file with the original double qoutes?
I haven't looked into this in any detail, but I don't think you can preserve quotes ( " ) using the CsvFile class. If you simply read the file using the File.Load method, you could work through the data looking for quotes, but its starting to become a Horrible Hack rather than a nicely designed program.

If I were you I would take a second look at the database, and assess how much work would be required to correct the problems there. How did you populate it with quoted strings? Did you first create the data in a spreadsheet, then export as a quoted string csv file?

My other suggestion is that you take a look at the Quote() method. Maybe if you know which fields are quoted, you could re-quote them before sending them back to the database....but that also sounds like a pretty dreadful idea to me.
01McAc
Posts: 75
Joined: Sunday 24th January 2021 8:25pm

Re: Working with CSV files

Post by 01McAc »

stevedee,
thanks for your time and effort. A short follow up to describe the intention: the simple idea behind is to export new and changed data from tables into a csv file using sqlite3 DB syntax beginning from a specific date because a new version of my little project Lens Register has been released.

My code generates the following shell script ...
cmdLine = "sqlite3 -header -csv \"" & myDBLocation & "\"  \"SELECT * FROM " & sElement & " WHERE DateChanged > '" 
Shell cmdLine
... and then exports the data into a csv file with quotes. This is a very simple and fast method to generate a csv file. Unfortunately, the structure of the csv file (e.g. quotes, headers etc) is static and cannot be changed in sqlite.

After installing a new release of a Lens Register release which includes the standard database users can easily import their own data they added to the database since the last release. But this turned out it is not so simple :shock:
sqlite provides an (not standard SQL) UPSERT syntax. An UPSERT is an ordinary INSERT statement that is followed by the special ON CONFLICT clause. It inserts a record and in case of a PK violation it updates the record. Exactly what I need but the fields and values needs to be assigned one by one, depending on values (integer or string) they need quotes, e.g.:
INSERT INTO Lenses (id, focallength, lensmaker_id)
VALUES(7, '50mm', 3)
ON CONFLICT(id)
DO UPDATE SET
focallength=excluded.focallength
lensmaker_id=excluded.age;
I presume that I can do a lookup to the table (Lenses), check field names and types (string or integer) and save them in a local array. In case of field type string I manually add quotes to the UPSERT statement.
User avatar
Quincunxian
Posts: 171
Joined: Sunday 25th June 2017 12:14am
Location: Western Australia

Re: Working with CSV files

Post by Quincunxian »

Extract data to CSV file with table field names as headers.

KeyTable is the name of the target table in the database
AG.DateFormatFiles = "YYYYMMDD"
The rest should be fairly obvious.
Public Sub ExtractDataAsCSV()
  Dim OutFile As File
  Dim TmpLine As String
  Dim Delimiter As String = ","
  Dim $Rec As Result
  Dim TmpTable As Table
  Dim TmpField As Field
  
  OutFile = Open AG.ApplicationFolder &/ KeyTable & "_" & Format(Now, AG.DateFormatFiles) & ".csv" For Create

  'Use the Field names As The Column headers   
  TmpTable = DB.$Con.Tables[KeyTable]
  For Each TmpField In Tmptable.Fields  
  TmpLine &= TmpField.Name & Delimiter
  Next
  Print #OutFile, TmpLine
  
  $Rec = DB.$Con.Exec("SELECT * FROM " & KeyTable)
  'Write the records
  If Not IsNull($Rec) And $Rec.available Then
    For Each $Rec
      TmpLine = Str($Rec!Id) & Delimiter
      TmpLine &= Chr(34) & $Rec!LinkTypeName & Chr(34) & Delimiter  ' Enclose in Quotes so that any text comma's are managed.
      TmpLine &= Chr(34) & $Rec!Information & Chr(34) & Delimiter
      TmpLine &= Str($Rec!DateCreated) & Delimiter
      TmpLine &= Str($Rec!DateChanged)
      Print #OutFile, TmpLine
    Next
  Endif
  Close #Outfile
  
End
Cheers - Quin.
I code therefore I am
01McAc
Posts: 75
Joined: Sunday 24th January 2021 8:25pm

Re: Working with CSV files

Post by 01McAc »

an export into a csv file is not the problem since the database comes with a build-in solution. The import (insert if the record does not exist; update if exists) is imho quite tricky.
If someone is interested in the details: my use case is
1) the database is released with a default set of lens records as part of the application. Data baseline is let's say 1-1-2021 but could be any other date
2) user adds new and personal data about lenses, details etc. while using the app
3) a new version of the software Lens Register has been released (by me) with the default database (see #1) - means: no personal data in there
4) the user exports the new and personal data since 1-1-2021 up to today into csv files (form based in the app)
5) the user installs the new release at home (including the default database)
6) the user imports the CSV-files (insert if the record does not exist; update if exists) in order to get back his added personal data about lenses etc.

Re #6 I need a so called upsert clause in sqlite. It looks like this:
INSERT INTO Lenses(
ID,MakerId,MountId,MaxAperture,MinAperture,FocalLength,LensType,LensLabel,Order_No,Production_era,LensVariants,Number_of_lenses_groups,MFD,Diaphragm_setting_type,Angle_of_view,Filter_type,Accessories,Materials,Dimensions_diameter,Weight,Inscriptions,Designer,Information,DateCreated,DateChanged,Aperture,Smallest_object_field,Largest_reproduction,Position_of_entrance_pupil,Scales,Viewfinder,Length_to_bayonet_flange,Largest_diameter,Tech_Data,Focusing_range,Lens_mount_description,Maximum_magnification,Compatibility,Bayonet,Dimension,Special_editions,Known_as,Lens_Coding,FocalLength_sort,Focus_throw,Type_of_focus_ring,R_lens_spec)
VALUES(104,6,1,'1.4','16.0','35mm',NULL,'35mm ZEISS Distagon T* 1.4/35 ZM',NULL,NULL,NULL,'10 | 7','0,61 m (24.01") - ∞',NULL,'Angular field** (diag. horiz. vert.): 62° | 53° | 37°','Filter thread:

M49 x 0.75',NULL,NULL,'43 mm (1.69") / Diameter max.
63 mm (2.49") / Diameter of focusing ring:
60 mm (2.39")','381 g (13.4 oz)',NULL,NULL,'Coverage at close range (MOD): 609 x 406 mm (24.00 x 16.00")
Flange focal distance: 28 mm (1.09")','2021-02-03 13:52:46','2021-02-24 15:48:04','f/1.4 – f/16',NULL,NULL,'63 mm (2.48")',NULL,NULL,NULL,NULL,'https://www.zeiss.com/consumer-products ... ies','0,70 m (27.56") - ∞',NULL,NULL,'Full Frame',NULL,'Length (with lens caps): 87 mm (3.30") / Length (without lens caps):
65 mm (2.57")',NULL,'35mm Distagon, The Fat',NULL,35,'Rotation angle of focusing ring: 90°',NULL,0)
ON CONFLICT (ID) DO UPDATE SET
MakerId = 6,
MountId = 1,
MaxAperture = '1.4',
MinAperture = '16.0',
FocalLength = '35mm',
LensType = NULL,
LensLabel = '35mm ZEISS Distagon T* 1.4/35 ZM',
Order_No = NULL,
Production_era = NULL,
LensVariants = NULL,
Number_of_lenses_groups = '10 | 7',
MFD = '0,61 m (24.01") - ∞',
Diaphragm_setting_type = NULL,
Angle_of_view = 'Angular field** (diag. horiz. vert.): 62° | 53° | 37°',
Filter_type = 'Filter thread:

M49 x 0.75',
Accessories = NULL,
Materials = NULL,
Dimensions_diameter = '43 mm (1.69") / Diameter max.
63 mm (2.49") / Diameter of focusing ring:
60 mm (2.39")',
Weight = '381 g (13.4 oz)',
Inscriptions = NULL,
Designer = NULL,
Information = 'Coverage at close range (MOD): 609 x 406 mm (24.00 x 16.00")
Flange focal distance: 28 mm (1.09")',
DateCreated = '2021-02-03 13:52:46',
DateChanged = '2021-02-24 15:48:04',
Aperture = 'f/1.4 – f/16',
Smallest_object_field = NULL,
Largest_reproduction = NULL,
Position_of_entrance_pupil = '63 mm (2.48")',
Scales = NULL,
Viewfinder = NULL,
Length_to_bayonet_flange = NULL,
Largest_diameter = NULL,
Tech_Data = 'https://www.zeiss.com/consumer-products ... ccessories',
Focusing_range = '0,70 m (27.56") - ∞',
Lens_mount_description = NULL,
Maximum_magnification = NULL,
Compatibility = 'Full Frame',
Bayonet = NULL,
Dimension = 'Length (with lens caps): 87 mm (3.30") / Length (without lens caps):
65 mm (2.57")',
Special_editions = NULL,
Known_as = '35mm Distagon, The Fat',
Lens_Coding = NULL,
FocalLength_sort = 35,
Focus_throw = 'Rotation angle of focusing ring: 90°',
Type_of_focus_ring = NULL,
R_lens_spec = 0
WHERE ID = 104
My Sub ImportCSVFile which generates the upsert clause above looks quite nasty but it works.
Public Sub ImportCSVFile()
  
  Dim DBS As New Cls_SQL_DataBase
  Dim $Rec As Result
  Dim sSQL As String
  Dim aFields, sFilenamepart As String[]
  Dim iRecords, i, ii, iii As Integer
  Dim colRecords As New Collection
  Dim sFile, sHeader, sTablename, sUpsert, sAddon, sUpdate, sWhere As String
  Dim sPattern As String = "*.exp"
  
  
  For Each sFile In Dir(DirChooser1.SelectedPath, sPattern).Sort()
    If Stat(DirChooser1.SelectedPath &/ sFile).Size <> 0 Then   
          sHeader = File.Load(DirChooser1.SelectedPath &/ sFile)
          sHeader = Mid(sHeader, 1, InStr(sHeader, "\n") - 1)
          aFields = Split(sHeader, ",")
          
          sFilenamepart = Split(sFile, "-")
          sTablename = sFilenamepart[1]
          getFields(sTablename)

          i = 0
          iRecords = 0
          Try myCSVfile.Close
          myCSVfile = New CsvFile(DirChooser1.SelectedPath &/ sFile)
          
          Do Until myCSVfile.Eof
            colRecords[myCSVfile.Line] = myCSVfile.Read()  'read every line into a collection
            Inc iRecords                                    'count the number of records for later usage
          Loop
          
          
          For ii = 2 To iRecords + 1 'colRecords.Count + 1  --> colRecords.Count shows false values here!!   ' go through every line 
              sUpdate = ""
              sUpsert = "INSERT INTO " & sTablename & "(" & gb.Lf & sHeader & ") " & gb.Lf
              sUpsert &= "VALUES(" 
              
              For i = 0 To myCSVfile.Fields.count - 1 
                  sAddon = FieldIsTypeOf(aFields, colRecords[CStr(ii)][aFields]) 
                  If isnull(colRecords[CStr(ii)][aFields]) Or colRecords[CStr(ii)][aFields] = "" Then
                    sUpsert &= "NULL" & ","
                  Else
                    sUpsert &= sAddon & colRecords[CStr(ii)][aFields] & sAddon & ","
                  Endif
                  
                  If i = 0 Then ' ID (alway zero!) doesn't need an update but needed for WHERE clause
                      sWhere = "WHERE " & aFields & " = " & colRecords[CStr(ii)][aFields] & gb.Lf
                  Else                  
                      sUpdate &= aFields & " = " 
                      If IsNull(sAddon) And colRecords[CStr(ii)][aFields] = "" Then 
                          sUpdate &= "NULL" & "," & gb.Lf
                        Else
                          sUpdate &= sAddon & colRecords[CStr(ii)][aFields] & sAddon & "," & gb.Lf
                      Endif
                  Endif
              Next
              sUpsert = Left(sUpsert, -1) & ")" & gb.Lf
              sUpsert &= "ON CONFLICT (ID) DO UPDATE SET" & gb.Lf
              
              sUpdate = Left(sUpdate, -2)
              sUpsert &= sUpdate & gb.Lf & sWhere
              File.Save(User.home &/ sFile & "-" & Format(Now, "dd/mm/yyyy hh-nn-ss-uu") & "-upsert.sql", sUpsert)
              $Rec = DBS.$Con.Exec(sUpsert) 
          Next  'colRecords.Count + 1 
           
     Endif    ' File Size <> 0
  Next
      
  DBS.$Con.Commit
End
cage
Posts: 123
Joined: Monday 2nd September 2019 5:47am
Location: Phoenix Arizona

Re: Working with CSV files

Post by cage »

How can you break cvs data down to individual parts and assign them to strings? I have tried different ways with split but never get the results needed. For example:
Dim String1 as new [100]
Dim String2 as new [100]
Dim String3 as new [100]

"Data1", "Data2" , "Data3"

String1[] = Data1
String2[] = Data2
String3[] = Data3
Listbox,add String1[]
This the basics of what I need. Thanks in advance for any assistance.
User avatar
Quincunxian
Posts: 171
Joined: Sunday 25th June 2017 12:14am
Location: Western Australia

Re: Working with CSV files

Post by Quincunxian »

Extract any database table to CSV with fields as headers - Works well with SQLite but have not tried with MySql but should be generic enough to work.
Must have a connection to your database open ($Con in the example below)
Public Sub ExtractTableToCSV(InSavePath As String, InTable As String, AutoOpen As Boolean)

  Dim TmpLine As String
  Dim TmpTable As Table
  Dim TmpField As Field
  Dim FileCSV As File
  Dim FileName As String = InSavePath &/ InTable & "_" & Format(Now, "ddmmyyyy") & ".csv"
  Dim Delimiter As String = ","
  Dim $Rec As Result

  FileCSV = Open FileName For Create
  TmpTable = $Con.Tables[InTable]
  '------------Writte the Headers------------
  For Each TmpField In TmpTable.Fields
    TmpLine &= String.UCaseFirst(TmpField.Name) & Delimiter
  Next
  Print #FileCSV, TmpLine

  '------------Write the records-------------------

  $Rec = $Con.Exec("SELECT * FROM " & INTable)
  If $Rec.Available Then
    For Each $Rec
      TmpLine = ""
      For Each TmpField In TmpTable.Fields
        TmpLine &= Chr(34) & $Rec[TmpField.Name] & Chr(34) & Delimiter
      Next
      Print #FileCSV, TmpLine
    Next
  Endif

   Close #FileCSV 

  '------------Open the file for review if desired-------------------
  If AutoOpen then Exec ["xdg-open",FileName]

Catch
  Message(Error.Text)

End
Cheers - Quin.
I code therefore I am
cage
Posts: 123
Joined: Monday 2nd September 2019 5:47am
Location: Phoenix Arizona

Re: Working with CSV files

Post by cage »

Thanks Quincunxian. SQL is a bit of over kill for what I needed. I found the solution in another post. I had to modify it to make it work for my needs. Thanks again for quickly responding to my question.
Post Reply