Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: FDDataMove from CSV to SQLite


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 3 - Last Post: Feb 9, 2015 11:50 AM Last Post By: Joe Pasquariello
Larry X

Posts: 15
Registered: 6/29/06
FDDataMove from CSV to SQLite  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 7, 2015 1:32 PM
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?
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FDDataMove from CSV to SQLite  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 9, 2015 1:24 AM   in response to: Larry X in response to: Larry X
Please provide a sample CSV file and simple test application. I will see what may be done.

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Joe Pasquariello

Posts: 37
Registered: 6/14/00
Re: FDDataMove from CSV to SQLite  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 9, 2015 11:47 AM   in response to: Larry X in response to: Larry X
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
Joe Pasquariello

Posts: 37
Registered: 6/14/00
Re: FDDataMove from CSV to SQLite  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 9, 2015 11:50 AM   in response to: Larry X in response to: Larry X
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
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02