Solved

XLSX to CSV

Posted on 2013-05-17
11
535 Views
Last Modified: 2013-05-17
Dear Experts,

I have an Access program that generates an .csv file.  The problem is, every time I export the date "2013/05/17", it gets converted to "5/17/2013".  In Access, I use Format, to force into "yyyy/mm/dd", and if I export it into .XLSX file, the format is fine.
It is when I convert it into .csv that the format change happens automatically.
Since I have to have the end result in .csv, I am not sure what I can do.
Please advise.
0
Comment
Question by:yballan
  • 5
  • 5
11 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39175274
<In Access, I use Format, to force into "yyyy/mm/dd">

try using  Cstr(Format([dateField],"yyyy/mm/dd"))
0
 
LVL 25

Expert Comment

by:Ron M
ID: 39175453
..also, you can format it the way it should appear.
Select all, copy..
Open a new excel sheet, select all > format all cells as "text"..
Then right click Cell A1.. "paste special", and choose "Values".

Then save as csv, and it will save it the way it appears.
0
 

Author Comment

by:yballan
ID: 39175522
Dear Capricorn1,

Thank you for your reply, but it produced the same results.

Dear xuserx2000,

Thank you for your reply, yes, that works, but I want to do everything from Access Macro/VBA.
This will require the enduser to have to get in and out of Excel sheet.  Everything is done in one step from Access, and if I can get these formats to work, that would be ideal.
Is there a way to accomplish "format all cell as text in .csv" from the Access VBA code?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39175570
<I have an Access program that generates an .csv file.>

how do you generate the .csv file?
are you using an Export Specification for doing this?
0
 

Author Comment

by:yballan
ID: 39175619
Dear capricorn1,

I use TransferText from Macro, with transfer type : Export Delimited, and specify the file name "NewProducts.csv"

I tried using TransferSpreadsheet and exported to NewProducts.xlsx, which retains the proper date format.  The problem is, as soon as I save the .xlsx file to .csv from Excel, the date format changes from "yyyy/mm/dd" to "m/dd/yyyy", so that didn't help either.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39175636
i have a second question in my last post..

are you using an Export Specification in your export process?

also, what version of access are you using?
0
 

Author Comment

by:yballan
ID: 39175733
No, I have left the "Specification Name" portion blank in my macro, so it reads like this:

TransferText  Export Delimited, ,ForExport, C:\NewProduct.csv, No, ,

I am using Access 2007.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39175840
first create an export specification


1.right click on the table
2.select export > Text file
   click on Browse and locate the destination folder
    click Save, then click OK

3. (you can accept the proposed name or change it, click OK)

4. In the export text wizard select the type (Delim / Fixed width)
5. Follow the wizard, before clicking on Finish
     5a .Click Advanced
6. In the Export Specification dialog box Field Information List, correct any descrepancies

7. click save as, give the specification a name <-- this is the specification name that you will use in your macro



.
0
 

Author Comment

by:yballan
ID: 39175898
Dear capricorn1,

Thank you for your detailed steps, I understand the concept, but I am having trouble being able to use the specification in my macro.
I created the text file, ExportSpec.txt,  using the wizard as you said, and corrected the descrepancies in Advanced.
Then, I Finish, the specification name is ExportSpec.txt, but I cannot select it from the Macro builder into "Specification Name".  I must be misunderstanding your direction, sorry to bug you, but what am I not doing correctly?
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 39176036
the specification Name is incorrect  "ExportSpec.txt"

save the spec as "ExportSpec"   no .txt.

see this
x
0
 

Author Comment

by:yballan
ID: 39176226
Thank you, I got it!!!!!!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now