Date variable after exporting data to CSV file

Support
1

What is the problem? Please be detailed.
I have collected data about date of vaccination but all date data are shown as 00:00:0000 after exporting the data to CSV file. I have 3000 observation and it's hard to re-enter the date again .. is there any way to solve this ?

What ODK tool and version are you using? And on what device and operating system version?
tablet

What steps can we take to reproduce the problem?

let me know please if there is any way I can have these dates in my exported file
What you have you tried to fix the problem?

nothing
Anything else we should know or have? If you have a test form or screenshots or logs, attach here.
.

2

Could you share sample output with as and also sample form.

Narendra

3

Hello,

Sure, Please see the attached

Thanks a ODK form Q.xlsx (9.3 KB)
ODK Q.csv (130 Bytes)
lot
Abrar

4

How did you export csv file?

1 Like
5

Are you opening the .csv file in Excel? There's a chance that it could be a formatting issue and that Excel just isn't displaying all the date data. It could be helpful to open the raw .csv file in a text editor so you can see whether the date data is there. This forum thread seems possibly related: Date format.

3 Likes
6

Yes I open it using excel . What should I use to open my data other then excel ?!

7

Normally from the account but using excel when I open the data

8

I was able to fill out your form, on ODK Collect v1.11.1, send it ODK Aggregate v1.4.15, download it as a CSV with the following data.

"pilgrim_id","airlines","meningococcal_status","vaccination_date","meta:instanceID"
"12","312","not_vaccinated_fake_card","Wed Nov 15 00:00:00 UTC 2017","uuid:a210a087-0450-4564-af32-363ddf234e74"

Open the CSV in a text editor and see what the date looks like. https://notepad-plus-plus.org is a free and good text editor. If the date looks like the text above, the problem is with Excel. If the date does not look like the text above, then the problem is with either the client (e.g., ODK Collect) or the server (e.g., ODK Aggregate) you are using. Telling us what versions of clients and servers you are using would then be the next step.

2 Likes
9

Thanks a lot yanokwa,

I have tried the test editor and it shows like what you have attached so probably the problem with the excel.

How can I solve this. I am using STATA and need to import data in that software

Thanks again

1 Like
10

Regarding the clients and servers I use lshtm server .

11

Excel is definitely able to manage date data, and it should be able to import date data from a .csv file. It may be that Excel is importing the data correctly, but not displaying it correctly. If that's the case, you would just need to select a date format for the date column. There are a lot of Excel resources out there, but here's one article I found on date formats in Excel:

Importing to Stata is a different process from Excel. One option is odkmeta, of which I am an author. You can download odkmeta from SSC by typing within Stata:

ssc install odkmeta

Once it's installed, type help odkmeta to view the help file. One thing that odkmeta does automatically is format date, time, and datetime variables.

To use odkmeta, you first need to export your data from your server using ODK Briefcase or an equivalent process. odkmeta expects to receive raw data: don't clean the data in Excel first, as that may alter the format of the data from what odkmeta expects.

For a simple form, you might also consider importing and formatting the data yourself using built-in Stata commands.

1 Like
12

Thanks Matthew, I have tried the steps in the article you have attached and didn't work. It shows a date 00/01/1900 after using these steps.

Not really sure where is the problem and how to solve this ?

Thanks
Abrar

13

first export your csv into txt format and save it.
Then open STATA , import this txt data as unformatted text data

Narendra

1 Like
14

I am attaching a picture how it look like in my odk account before exporting, it shows the real date and below 00;00;00 . Could it be the problem and why the real date was not exported and shown

file
file2138×2851 2.43 MB
?

Thanks I will try your method and let you know
Abrar

15

This method did not work , it just read the file and still the date is 00;00;00 . it also deleted all other variables .

16

Good , I have solved it ..let me explain here so others can see when they face the same issue..

First , when I exported the data from the odk account, I first used the steps in the article about excel date that Mathhews have attached then used the (saved as) option. My issue was that I have saved the file before doing these steps so when I tried to use the excel steps it did not work

Thanks a lot ODK group you all are amazing and so helpful

Abrar

4 Likes
17

That’s spirit good I appreciate your hard work and dedication.

Narendra

2 Likes