Solved

XLSX to CSV

Posted on 2013-05-17
11
557 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 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

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…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

816 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

13 Experts available now in Live!

Get 1:1 Help Now