FDDataMove from CSV to SQLite

FDDataMove does great from SQLite to CSV.  The other way around it has problems recognizing dates and reals.  I use the following:

ShortDateFormat = m/d/yyyy    (which is what's used in the CSV file)
TextAnalyzeSample = 3000  (so FDDataMove goes deep enough into the CSV file import to realize that all numeric fields have to be real:  fractional and possibly negative)

And yet it persists at importing dates as VARCHAR (10) and reals as integers.  I can get it to import reals by manually setting each numeric field in the first row as -0.5 but that seems silly (and impractical).

What's the trick?
0
Larry
2/7/2015 9:32:19 PM
embarcadero.delphi.firedac 822 articles. 2 followers. Follow

3 Replies
1897 Views

Similar Articles

[PageSpeed] 2

Please provide a sample CSV file and simple test application. I will see what may be done.

-- 
With best regards,
Dmitry Arefiev / FireDAC Architect
0
Dmitry
2/9/2015 9:24:39 AM
On 2/7/2015 1:32 PM, Larry X wrote:
> FDDataMove does great from SQLite to CSV.  The other way around it has problems recognizing dates and reals.  I use the following:
>
> ShortDateFormat = m/d/yyyy    (which is what's used in the CSV file)
> TextAnalyzeSample = 3000  (so FDDataMove goes deep enough into the CSV file import to realize that all numeric fields have to be real:  fractional and possibly negative)
>
> And yet it persists at importing dates as VARCHAR (10) and reals as integers.  I can get it to import reals by manually setting each numeric field in the first row as -0.5 but that seems silly (and impractical).
>
> What's the trick?
>

Hi Larry,

It will be interesting to see what comes back from Dmitry, but I'll 
share my experience with you.

Since SQLite does not have a native type for DATETIME, date/time values 
imported from a CSV file will be stored as TEXT. The SQLite users 
mailing list has had many threads discussing best practices for DATETIME 
storage. The options seem to come down to these:

TEXT	in ISO 8601 format (eg 1997-07-16T19:20:30.45+01:00)
FLOAT	Julian date/time values
INTEGER	UNIX date/time values

FLOAT is a double-precision real, so you get resolution of about 1 ms, 
which is pretty good.

INTEGER is a 64-bit integer, so you can use standard UNIX date/time 
values, which have 1-second resolution, or you can store whole seconds 
in the upper 32 bits and use the lower 32 bits as fractional seconds.

I use INTEGER if 1-second resolution is adequate and FLOAT if I need 
higher resolution. Sorting and searching on numeric values is faster 
than on TEXT. The reason they recommend ISO 8601 if you use TEXT is that 
the format must be very regular for the sorted TEXT values to be in 
proper chronological order. For example, 1997-12-31 would follow 
1997-02-01, but it would precede 1997-2-1, which is probably not what 
you would want.

The experienced folks usually say that if you use a numeric format, 
always store the value for GMT, and convert to/from local timezone in 
the UI.

Joe
0
Joe
2/9/2015 7:47:36 PM
On 2/7/2015 1:32 PM, Larry X wrote:
> FDDataMove does great from SQLite to CSV.  The other way around it has problems recognizing dates and reals.  I use the following:
>
> ShortDateFormat = m/d/yyyy    (which is what's used in the CSV file)
> TextAnalyzeSample = 3000  (so FDDataMove goes deep enough into the CSV file import to realize that all numeric fields have to be real:  fractional and possibly negative)
>
> And yet it persists at importing dates as VARCHAR (10) and reals as integers.  I can get it to import reals by manually setting each numeric field in the first row as -0.5 but that seems silly (and impractical).
>
> What's the trick?
>

Also, regarding AnalyzeSample, I asked a similar question last week, and 
Dmitry's advice was to set it to a very large value, i.e. greater than 
the number of rows in your file. I use 1,000,000,000. My files are 
typically 500 to 50,000 rows, and the difference in input time is a 
small fraction of a second.

Joe
0
Joe
2/9/2015 7:50:12 PM
Reply:

Similar Artilces:

Delphi FireDAC SQLite RecNo = -1
RecNo for a FireDAC FDTable in an SQLite database equals -1 even though the cursor is on the last of 1,200,000 records. What is that all about? http://docwiki.embarcadero.com/Libraries/XE7/en/FireDAC.Stan.Option.TFDFetchOptions.LiveWindowParanoic -- With best regards, Dmitry Arefiev / FireDAC Architect ...

Delphi FireDAC SQLite RecNo = -1
RecNo for a FireDAC FDTable in an SQLite database equals -1 even though the cursor is on the last of 1,200,000 records. What is that all about? http://docwiki.embarcadero.com/Libraries/XE7/en/FireDAC.Stan.Option.TFDFetchOptions.LiveWindowParanoic -- With best regards, Dmitry Arefiev / FireDAC Architect ...

Where can I find a Delphi XE4 FireDac SQLite for IOS?
Where can I find a good example of using Delphi XE4 FireDac SQLite for IOS? I am unable to use append and edit without getting strange data corruptions. An append is overwriting the last appended or editted record and then all other records are corrupted with foreign characters. Just need a good solid example to get started. Create the database and tables on the device. Do not want to use inserts or updates. Want to use the append, edit, and delete functionality from the client dataset components. Maintain storage on device. Strange that no SQLite examples with IOS are with s...

FireDAC mobile app error as sqlite as delphi XE7
i im trying of connect my app as FireDAC but i have some errors. I follor all steps of Mobile_Tutotils_en_pdf file for delphi XE7. First i add one FireDAConnection compoent, after i in FireDAConnection Edito i set DriverID as Sqlite, in Database parameter i set this existing sqlite database "shoplist.s3db i create this database and table as other tool, and i can to add this to DBExpres in data exploer and i can to use it in one mobile delphi project", and i set lockingmode to normal as this manual tell. After as tell in page 269 of this manual when i click over test button, i ...

FireDAC mobile app error as sqlite as delphi XE7 [Edit]
i im trying of connect my app as FireDAC but i have some errors. I follor all steps of Mobile_Tutotils_en_pdf file for delphi XE7. First i add one FireDAConnection compoent, after i in FireDAConnection Edito i set DriverID as Sqlite, in Database parameter i set this existing sqlite database "shoplist.s3db i create this database and table as other tool, and i can to add this to DBExpres in data exploer and i can to use it in one mobile delphi project", and i set lockingmode to normal as this manual tell. After as tell in page 269 of this manual when i click over test button, i ...

FireDAC mobile app error as sqlite as delphi XE7 [Edit] #2
i im trying of connect my app as FireDAC but i have some errors. I follow all steps of Mobile_Tutotils_en_pdf file for delphi XE7. First i add one FireDAConnection compoent, after i in FireDAConnection Edit i set DriverID as Sqlite, in Database parameter i set this existing sqlite database "shoplist.s3db , and i set lockingmode to normal as this manual tell. I After i follow steps 1,2,3,4 and in the step 5 of the page 265 when i try of to set Connected property to true, i get this error: 1. [FireDac][Comp][Clnt]-340. Driver ID is not defined. Set TFDConnection.DriverName or Add D...

Delphi XE6 FireDAC SQLite copy a table from one database to another
How do I copy a single table from one SQLite database to another? Is there a simple command to do this? Thanks! > How do I copy a single table from one SQLite database to another? Is there a simple command to do this? Thanks! You should use ATTACH command. For example: {code} ATTACH 'c:\secondDB.sdb' AS sec; INSERT INTO SomeTab SELECT * FROM sec.SomeTab; {code} -- With best regards, Dmitry Arefiev / FireDAC Architect Thanks! > {quote:title=Dmitry Arefiev wrote:}{quote} > > How do I copy a single table from one SQLite database to another? Is there a...

FireDAC and Delphi Pro XE4... firedac tab not visible?
I am moving from a very old Borland product (2006) to Delphi XE4 Professional with FireDAC addon. I purchased both from Embarcadero, and have downloaded both. The documentation says the FireDAC components should automagically appear in the tabset for Delphi. They do not. I don't want to fool around too much as my environment is strictly controlled by IT, and they are very slow to do anything. Is there something that I should have done, but did not do? I installed XE4 first, registered it, then used the link in my email from Embarcadero to download the FireDAC components. Then I ra...

How to setup FireDAC SQLite Sample DB for Getting Started SQLite Example
When i run the Getting Started FireDAC SQLite example I got a "stock Price" notification error. I copied the database (fddemo.sdb) to the project directory but the same error pops up after you selet the DB at Runtime. [FireDAC][Phys][SQLite] ERROR: no such function: StockPrice I noticed in Samples\Object Pascal\Database\FireDAC\Bin there are multiple utilities that appear to be used to create teh database CreateSQLite.bat and FDExecutor.exe When i run CreateSQLite it appears to be grabbing CSV data and building a DB, although I cannot tell where it is being built ...

How to setup FireDAC SQLite Sample DB for Getting Started SQLite Example
When i run the Getting Started FireDAC SQLite example I got a "stock Price" notification error. I copied the database (fddemo.sdb) to the project directory but the same error pops up after you selet the DB at Runtime. [FireDAC][Phys][SQLite] ERROR: no such function: StockPrice I noticed in Samples\Object Pascal\Database\FireDAC\Bin there are multiple utilities that appear to be used to create teh database CreateSQLite.bat and FDExecutor.exe When i run CreateSQLite it appears to be grabbing CSV data and building a DB, although I cannot tell where it is being built ...

FireDac Delphi Xe2
Hello, I have FireDac components installed to Delphi Xe2, My version of FireDac is 8.0.1 but in this version I have probelm with TAdoquery with StrsEmpty2Null param. It doesn't work. Maybe someone know what I have to change in FiredAc source to remove this issue. To set the Param on NULL value before executing the query I do: {code} with qryExample.ParamByName('Example_id') do Begin DataType:=ftInteger; Clear; // Bound := True; End; {code} > {quote:title=Robert Triest wrote:}{quote} > To set the Param on NULL value before executing the query I do: >...

No Delphi support for SQLite?
SQLite is a very popular open-source database, well-suited for applications where mySQL or Firebird might be overkill. I am looking at SQLite for a project, and noted that D2010 doesn't include it in the list of supported database engines. Any plans to add support (eg in an Update)? For Pro-level Delphi users it could be very useful. Michael It's not in Delphi itself, but there are a few solutions out there both free and commercial. Don't know if I'm spelling them correctly or not, but two of the ones I saw when I was investigating SQLite were DISQLite3 a...

Delphi + SQLite 3
What Delphi component would you recommend to work with SQLite 3 files? Thank you. Brent Smith wrote: > What Delphi component would you recommend to work with SQLite 3 files? I've been happy with Audcom's SQLite components. Unfortunately it's not compatible with Delphi 2009 yet, and the task seemed more daunting than a simple "search and replace all string/char/pchar w/ AnsiString/AnsiChar/PAnsiChar" Also available are the Zeos Components, and DISQLite3, a dbExpress Driver (http://www.bcp-software.nl/artikelen/sqlite.html), an ODBC Driver (http://www...

SQLite In Delphi 2010
Problem with SQLite in Delphi 2010. For connection to a DB sqlite I use components Zeos of last version (version is the alpha, but earlier versions under дельфу 2010 do not work). The problem that when i try connect to base, takes off error "Library routine called out of sequence". Who can in a course what is the matter? P.S. It is possible to offer other components, but not paid. You might try the components of www.aducom.com albert <Anton Pavlov> wrote in message news:259552@forums.embarcadero.com... > Problem with SQLite in Delphi 2010. For connection to a DB sq...

Web resources about - FDDataMove from CSV to SQLite - embarcadero.delphi.firedac

Resources last updated: 12/26/2015 5:10:27 PM