Problem in datetime parsing while loading data into table from csv

Community Support
1

Hi,

I am trying to load the data from csv file to a table. One of the column in the file is having datetime column with below sample values.

| number | duration | u_resolved | u_resolved_by | counts | dummy2 | dummy3 |
| C0010837 | 86400 | 11/29/2012 9:14 | abc | 249494 | 0.001 | 249494.001 |
| C0010838 | 57600 | 11/28/2012 7:24 | scv | 162227 | 0.00001 | 162227 |
| C0010839 | 57600 | 11/28/2012 11:45 | abc | 161791 | 0.00002 | 161791 |
| C0010840 | 57600 | 11/28/2012 8:37 | xyz | 157746 | 0.00003 | 157746 |
| C0010843 | 86400 | 11/29/2012 12:21 | abc | 242492 | 0.00004 | 242492 |

Data loading happens successfully but q loads only those values where hour is specified in 2 digits. rest of the values are coming blank.

tbl:("sizcjef"; enlist",") 0: c:/temp/inc.csv5#tbl number duration u_resolved u_resolved_by counts dummy2 dummy3 C0010837 86400 249494 0.001 249494 C0010838 57600 162227 1.00E-05 162227 C0010839 57600 2012.11.28T11:45:00.000 161791 2.00E-05 161791 C0010840 57600 157746 3.00E-05 157746 C0010843 86400 2012.11.29T12:21:00.000 242492 4.00E-05 242492 `

I tried to set the datetime format for parsing before loading the data, but it didn’t work. Any suggestion?

Thank you.

2

Looks like you’ll have to read it in as a string column first and then apply a function to fix them.

Something like:

tab:(“s*”;enlist csv)0:enlist “col1,col2\na,11/28/2012 9:14\nb,11/28/2012 11:14”

q)update {“Z”$" " sv’.[" " vs’x;(::;1);“0”^-5$]}col2 from tab

col1 col2

a    2012.11.28T09:14:00.000
b    2012.11.28T11:14:00.000

Assuming the data is always in that same format, this should cover all cases.

Terry

3

nice

q)t:1000000#tab

q)\ts update {“Z”$" " sv’.[" " vs’x;(::;1);“0”^-5$]}col2 from t

2834 168389504

another approach

q)\ts update (sum"DT"$" "vs)each col2 from t

1916 28583664

q)\ts update sum"DT"$(11#'col2;11_'col2) from t

924 80778032

Cheers,

  Attila

4

Very nice, the split/cast/sum is cleaner.

I think the 11 take/drop would depend on how the single digit days and single digit months are represented (whether or not they have leading zeros).

5

How can I make this faster? Can I combine the 8 and 15 in 1 go?

\ts:100 “P”$.[; (::; 8); :; “D”] .[; (::; 15); :; “.”] 10000#enlist “20140102_063002_882000”;

6

Any ideas on a fast *inverse function* for printing out timestamps at microsec precision at 8_6_6 format? Thx.

7

On Tuesday, September 8, 2015 at 11:59:11 PM UTC+1, Yan Yan wrote:

How can I make this faster? Can I combine the 8 and 15 in 1 go?

\ts:100 “P”$.[; (::; 8); :; “D”] .[; (::; 15); :; “.”] 10000#enlist “20140102_063002_882000”;

Here’s a couple:

q)t:10000#enlist “20140102_063002_882000”;

q)\ts:100 “P”$.[; (::; 8); :; “D”] .[; (::; 15); :; “.”] t

685 1411632

q)\ts:100 “P”${.[x;(::;y);:;z]}/[t;8 15;“D.”]

617 1411632

q)\ts:100 “P”$sums[count each t] _ @[raze t;8 15+:22*til count t;:;“D.”]

415 1099296

Remember you can use both like below, it just won’t be faster faster..

q)\ts:100 “P”$@[;8 15;:;“D.”]each t

707 836864

8

If you capture .z.p at the start(when you want), then you can play with it assuring you have the microsecond you want…

q)f:{foo:string .z.p;"_"sv 3#0 8 14 20 _ foo where foo in .Q.n};

q)f

HTH,

Sean

9

sum 1 1 1000i*(“D T I”;8 1 6 1 6)0: