CSV File format

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

CSV File format

Postby EdM » Mon Feb 06, 2006 11:57 am

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

Site Admin
User avatar
Posts: 2185
Joined: Wed Apr 16, 2003 4:35 pm
Location: Western PA

Postby Scott » Tue Feb 07, 2006 8:41 am

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

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

CSV File format

Postby EdM » Wed Feb 08, 2006 7:36 am

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

Active Member
Posts: 108
Joined: Sun May 11, 2003 11:24 pm
Location: Nagasaki, Japan

Postby chrispizza » Sun Mar 12, 2006 7:03 am

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?

Site Admin
User avatar
Posts: 2185
Joined: Wed Apr 16, 2003 4:35 pm
Location: Western PA

Postby Scott » Mon Mar 13, 2006 9:11 am

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

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

Date Format

Postby EdM » Mon Mar 13, 2006 12:12 pm

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

Site Admin
User avatar
Posts: 2185
Joined: Wed Apr 16, 2003 4:35 pm
Location: Western PA

Postby Scott » Mon Mar 13, 2006 12:40 pm

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

Active Member
Posts: 108
Joined: Sun May 11, 2003 11:24 pm
Location: Nagasaki, Japan

Postby chrispizza » Tue Mar 14, 2006 7:42 am

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

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

Date Format

Postby EdM » Tue Mar 14, 2006 8:47 am

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

Site Admin
User avatar
Posts: 2185
Joined: Wed Apr 16, 2003 4:35 pm
Location: Western PA

Postby Scott » Tue Mar 14, 2006 9:32 am

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

Return to v5 Database Help

Who is online

Users browsing this forum: No registered users and 1 guest