Ecommerce software home
Shopping Cart Software Forum for Ecommerce Templates
 
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?

Find us on Facebook Follow us on Twitter View our YouTube channel
Search our site
 All Forums
 Technical
 ASP (Windows server) versions
 Exporting to csv - date missing comma delimiter
Author « Topic »  

cciknives
Advanced Member

Canada
224 Posts

Posted - 11/28/2019 :  17:09:44  
Hello,

I am not sure if this can be fixed or not. I noticed that when exporting orders or order details to a csv file that when imported into Excel the date and time end up in the same column.

Is there anyway that the date can get a delimiter comma after the date so that the information date and time are imported into separate columns.

To import I need to trigger the wizard in Excel so that the columns format properly on import. i.e. Select Tab - Data, From Text/CSV, select file, Import, Delimiter "Comma", Imports fine except for the date.

Thanks for any help on this one, Peter

CCI - Tools for the Discriminating Chef

dbdave
ECT Moderator

USA
10243 Posts

Posted - 11/28/2019 :  21:16:05  
Before posting my reply, I checked the database to be sure, but indeed that timestamp (date and time) are not two separate columns. That info is in the ordDate column of the orders table.
So it's not something that's broken, hence no fix.

I was thinking though that if you were to open the exported csv in notepad, you might be able to use find>replace and search for the year and replace with the year plus the delimiter and add a column name for the header.

Then open in excel.

Alternately, it's likely possible to write a custom export program that will break that column into two during the export process, but you would need to hire a developer to handle that I suppose.

David

Vince
Administrator

42426 Posts

Posted - 11/28/2019 :  23:36:05  
Good idea, Dave. But another possibility could be to open the CSV file in Excel. Then copy the order date column and paste it to the next empty column. Then select the date column and go to Format Cells and format as date, and the same with the second but format as time.

Vince

Click Here for Shopping Cart Software
Click Here to sign up for our newsletter
Click Here for the latest updater

cciknives
Advanced Member

Canada
224 Posts

Posted - 11/29/2019 :  14:45:14  
I tried Dave's advice and copied column to inserted row on right. Then formatted the date column to dates only, then selected Text to Columns and striped out the times using fixed width. Times column I striped out the dates. Now I have dates and times in two columns and if I need them combined I just use the Concatenate formula.

Thanks for the advice Vince and Dave :)

CCI - Tools for the Discriminating Chef
  « Topic »  
Jump To:
Shopping Cart Software Forum for Ecommerce Templates © 2002-2022 ecommercetemplates.com
This page was generated in 0.03 seconds. Snitz Forums 2000