• Registered: 2018-07-07
  • Posts: 7

Topic: CSV and several data type conversion errors

I have several data in Excel/OOA spreadsheet to import to be managed by the DB I am designing using MyVisualDatabase. I am encounttering several problems with some data types not well managed somewhere in MVD.
This is my conversion procedure:
1. open the spreadsheet, select the sheet to convert in CSV, Save As... and settting the CSV conversion to use ' as separator for text, ; separattor for fields, converting reals and currencies ttto use . as decimal separattor and no thousand separator.
2. I inspect the CSV by eyes and it looks good.
3. Testing the import using SQLite Studio:
3.1 examining the structure of my table the fields I designed as DATE in MVD look as TEXT for SQLite Studio anyway importing data works and I correctly see every data of every field in the DB table.
4. I use the importt button on my MVD application. It imports tthe data but the DATE fields are not imported correctly and they display 0.

Similar problems for other data types as DATE/TIME, TIME and CURRENCY.

I attached a zip containing everything needed to follow my tests.

Post's attachments

Attachment icon Banka.zip 428.05 kb, 425 downloads since 2018-07-14 

Massimo.Manca's Website

2 Reply by domebil (edited by domebil 2018-07-14 15:35:28)

  • Registered: 2014-11-13
  • Posts: 370

Re: CSV and several data type conversion errors

try now

Post's attachments

Attachment icon bank ok.rar 393.75 kb, 608 downloads since 2018-07-14 

Domebil

domebil's Website

  • Registered: 2018-07-07
  • Posts: 7

Re: CSV and several data type conversion errors

Thanks, your modification works well. It is a specialized import. To implement a more generic one I need to understand why for instance DATE type field (selected designing the table using MVD) in SQLite studio looks as a TEXT field. Same problem with other data types. The idea was to detect the data type from the table field and convert the 'text' coming from CSV file to the right type of the table field. But if the type is wrong... difficult to detect and convert automatically, I would need a specialized converter as yours.

Massimo.Manca's Website

  • Registered: 2014-02-14
  • Posts: 10,294

Re: CSV and several data type conversion errors

Here you can read more about date type in SQLite DBMS
https://sqlite.org/datatype3.html

"2.2. Date and Time Datatype"

Dmitry.