Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

XLSX to CSV

Posted on 2013-05-17
11
Medium Priority
?
586 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 Malmstead
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

618 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