Using OS X Version – CSV Import

[Yogie]

Hi Brendan,

I switched from Bento to Tap Forms.

I exported a Bento table to CSV in the table I do have a currency column and also date and date & time columns.

When I import the CSV the dates never get imported and the currency values do not have the amount after the decimal point.

I have set the date form to look exactly as the example records that are showing up at the import dialog.

I also tried to set up a Tap Forms form prior importing to have the column types the imported data is having but also this is not going to work.

Cheers
Yogie

[Yogie]

Hi Brendan,

I think I identified the problem …

for the date field I didn’t need the time stamp, so I went into my CSV and deleted the T12:12:12 part so I simply had 2013-01-01 left for the date and then the import worked.

For the currency I simply deleted the currency symbol out of the CSV but as I’m in Germany the format of the numbers are different. We use 1.123,00 so again I used grep in Textwrangler and replaces my currency to look 1,123.00 and then the import brought the right results. So at the end from 1.123,00 € I changed it to look like 1,123.00 instead.

Is there a way you can fix such issues so that we do not need to edit a CSV prior importing?

Cheers
Yogie

[Brendan]

Hello Yogie,

Thanks for the feedback. What you did is correct. Sometimes it just takes a little massaging of the data to make it import correctly. But I agree it would be better to have more options on importing for dealing with these kind of issues.

Thanks,

Brendan

[Yogie]

Thank you Brendan!

I was just lucky enough that I did not need to have the times in my date field. Because although I have had the format for the date set to 2013-01-01T12:12:12 for importing dates into Tap Forms I was not able to import the dates from my CSV all three columns were left empty. But I bet this was because I had three date columns. Two were having times and one was just having a date. Anyhow if I would have to rely on the times in two columns I would have been in trouble to import the data. So there is room for improvement ;)

Many thanks again!
Cheers
Yogie

[Brendan]

Yes, it seems that the proper way to handle this would be to have a different date formatter available for each field. The same with number formatters.

[johnmcboston]

Former Bento convert here as well.

Although all my my databases are already imported – on the Import window, it would be nice if you could chose which fields to import or not import. Perhaps a future feature.

thanks

– J

(btw – great YT videos – helped me a lot)

[Brendan]

Thanks John. I’ll pass that along to my son who created them for me.

Thanks!

Brendan

[tambor]

hi,
i also to need to import exported CSV tables from Bento.
on a previous post on this thread, Yogie wrote:
“again I used grep in Textwrangler and replaces my currency to look 1,123.00 and then the import brought the right results. So at the end from 1.123,00 € I changed it to look like 1,123.00 instead”

i may need to change date and currencies formats too. but i’ve no idea what “grep” is.
i know TextWrangler though…
please let me know.

thanks

[Yogie]

Hi Tambor,

I used the grep within the search and replace of TextWrangler to search a comma in numbers to let it replace by a period. Her is a link where I found how to use the grep expression: http://forums.adobe.com/thread/901976

Hope this helps!

Cheers Yogie

[tambor]

Hi Yogie!

Thanks for the very useful explanation and link.

I had no idea on how to use this grep feature for searching in TextWrangler. Seems very powerful indeed.

Have a nice day.

[Yogie]

tambor,

I had trouble to make the grep expressions work in TextWrangler but this is what I did:

at the search box

(\d+)\.(\d+)\,(\d+)

at the replace box

\1,\2.\3

As an example this grep expression would find any number in this format

1.123,34

and re-format it to

1,123.34

I also used a grep expression to re-format my date & time fields (I simply cut off the times as I could not make these to get imported).

Cheers
Yogie

[tambor]

Hi again Yogie,

Thanks very much for pointing this out. It’ll probably save me a lot of time. I take good note of it for further reference.

Cheers,

[Brendan]

Tap Forms will use the Date Format and the Time Format specified with a space character in between to import Date & Time fields. Bento puts the letter T between the Date and the Time. Just replace the T with a space character in your CSV file and it should import.

[tambor]

Thanks Brendan

That’s very useful indeed.
Thanks for your help :D

[Author]

Posts