CSV File format

Help with the POS Pizza databases, Items + Toppings, Employees, Customers, etc
Post Reply
EdM
Member
Member
Posts: 29
Joined: Tue Mar 15, 2005 9:31 am
Location: United Kingdom

CSV File format

Post by EdM »

Hi,

I can export the customer database into a CSV file without any trouble. The CSV file loads into the editor and displays perfectly. However, if I load the file into Excel, some of the date formats are displayed as dates eg mm/dd/yyyy and are treated as dates by Excel, whilst other dates are loaded as mm-dd-yyyy and are treated as text fields. Why?

Is there another CSV editor that I could use to do the data crunching to avoid this issue??

Thanks for your help,
Ed
User avatar
Scott
Site Admin
Site Admin
Posts: 2566
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Post by Scott »

Can you force the fields in Excel to go to text? I don't know much about Excel. You could use the built-in CSV editor, however it is very basic. Also Quatro or most other spread-sheet programs should also work.

Scott
EdM
Member
Member
Posts: 29
Joined: Tue Mar 15, 2005 9:31 am
Location: United Kingdom

CSV File format

Post by EdM »

Thanks Scott, I ended up using MS Access. Unfortunately the date format comes through as text and then has to be converted manually to a date format before I can use it.

Got though,
Cheers, Ed
chrispizza
Active Member
Active Member
Posts: 108
Joined: Mon May 12, 2003 12:24 am
Location: Nagasaki, Japan

Post by chrispizza »

Same problem here. I use excel to filter out my database mailers and wish there was something that coould be done about the dates. The biggest problem with them as test is that say I wanted to get all the orders from Jan 2005 that were still in the history. They are all grouped with the 2006 orders.

1/5/2005
1/5/2006
1/6/2005

and so on. Dont soppose there is any way to fix it is there?
User avatar
Scott
Site Admin
Site Admin
Posts: 2566
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Post by Scott »

The problem is that Excel auto-formats what it thinks are dates into that format. I guess we could output a raw date # field (days since 2000) and you could sort on that. The only problem would be is you would need to know what ranges to filter from.

We are in the process of releasing an update so any quick suggestions might make the cut.

Scott
EdM
Member
Member
Posts: 29
Joined: Tue Mar 15, 2005 9:31 am
Location: United Kingdom

Date Format

Post by EdM »

Hi Scott,

Personally I would like the same starting point as Excel, eg Day 1 is January 1st 1900, and then the date is expressed as days since then. This would make it very easy from any stand point as it can be formatted in any way by any application.

Is anyone else happy with this solution??
Cheers, Ed
User avatar
Scott
Site Admin
Site Admin
Posts: 2566
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Post by Scott »

I could also encode them in a Unix format. Eg. 03/12/2005 would show as 20050312 in another field. Let me know what you all thiink, because I'll try to get this in the update coming out later this week.

Scott
chrispizza
Active Member
Active Member
Posts: 108
Joined: Mon May 12, 2003 12:24 am
Location: Nagasaki, Japan

Post by chrispizza »

Scott - As long as the year is first that would be great, and solve my problem. It corrently just reads from the first number, and i can't for the life of me get it to do anything else. So the unix format would be great, or just the current format with the yer first would be fine.

-Chris
EdM
Member
Member
Posts: 29
Joined: Tue Mar 15, 2005 9:31 am
Location: United Kingdom

Date Format

Post by EdM »

I would have to say that my preference would be to have a day count from 1900 onwards, solely because this is how Excel handles dates, and I use excel to do all my number crunching.
Cheers, Ed
User avatar
Scott
Site Admin
Site Admin
Posts: 2566
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Post by Scott »

I think using the current date format if the slashes (/) were replaced with dashes (-), Excel would see these as dates since this is the MS VB date format. I will look into having several different date formats within the CSV files.

Scott
Post Reply