Solved

XLSX to CSV

Posted on 2013-05-17
11
547 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Where is user Lync data stored 4 62
Running sum query 6 33
Is it possible to reset DSum? 12 43
Conditional lock of one field in  datasheet 9 21
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

867 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

15 Experts available now in Live!

Get 1:1 Help Now