?
Solved

Reformating a CSV file

Posted on 2006-11-10
8
Medium Priority
?
291 Views
Last Modified: 2010-04-30
Hi,

Is it possible to format a date field in a CSV file?

At the moment it is formatted 20061110 I would like it formatted 10/11/2006.

Thanks.
0
Comment
Question by:sam_norian
[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
  • 4
  • 4
8 Comments
 
LVL 8

Expert Comment

by:KelvinY
ID: 17913112
Hi sam_norian,

Is this a file that already exists, or are you creating it with code? If you are creating it then it's quite simple. Your print statment would look something like

Print #1, Right$(mydate,2) & "/" & Mid$(mydate,5,2) & "/" & Left$(mydate,4)

If this doesn't help then post your code and we'll have another look at it.

Regards
  Kelvin
0
 
LVL 3

Author Comment

by:sam_norian
ID: 17913218
Kelvin,

The files are already created and sent from one of our suppliers.

Thanks
0
 
LVL 8

Expert Comment

by:KelvinY
ID: 17913319
sam_norian,

How are you reading the files? You could reformat an existing file using Excel. Open your CSV file and insert a column containing a formula like =CONCATENATE(RIGHT(A2,2), "/", MID(A2,5,2), "/", LEFT(A2,4)), where A2 is assumed to hold the unformated date. Once that's done then copy and Paste, Special as values and you can delete the original (unformatted) date.

If you're using VB code to read the files I assume that you are getting each field into a separate variable. In its present form you would have to read that date into a string, in which case you can reformat it by using something like

myformatteddate = Right$(mydate,2) & "/" & Mid$(mydate,5,2) & "/" & Left$(mydate,4)

myformatteddate would also be a string variable, but containing the formatted value which you could then write out to another file.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:sam_norian
ID: 17913466
I have a vb app that formats the file from multiline to single line (which you helped me with before (http://www.experts-exchange.com/Databases/Q_22045036.html)) and I was hoping to do it within that. The purpose the file is to be imported into an access db. I am only using a vb app to format the file correctly so access will accept it.

I am not currently loading each field as a separate variable, but I can go down this route if you give me a little help in doing this?

Thanks alot
0
 
LVL 8

Accepted Solution

by:
KelvinY earned 2000 total points
ID: 17913575
sam_norian,

The easiest way to tackle this is probably to import the files into a temporary table in Access, then use an append query, which formats the date fields correctly, to write the data to its final destination. You can create a calculated field using a formula like TheDate:Right$([datefield],2) & "/" & Mid$([datefield],5,2) & "/" & Left$([datefield],4) in the query to do the formatting. If you want to store the dates in a Date/Time you can use a function like DateSerial or DateValue to convert it. e.g.
 TheDate:DateValue(Right$([datefield],2) & "/" & Mid$([datefield],5,2) & "/" & Left$([datefield],4)).

You can automate importing CSV files into an Access database using DoCmd.TransferText.
0
 
LVL 3

Author Comment

by:sam_norian
ID: 17913631
Ok, I had a feeling that would be the case, this is what I am doing at the moment, I was just wondering if there was a way to do it with vb.

Thanks for your time and help
0
 
LVL 8

Expert Comment

by:KelvinY
ID: 17913680
sam_norian,

You can automate most of the process with VB. You need to create a table to hold the imported file temporarily, an append query to format and transfer the data and form to execute the code from. You will need to pass the name of the file to be imported to your import function. You could probably set this up using form with an unbound  textbox and a command button. If you're feeling confident enough you can use the Common dialog control to locate the file to import and write its name into the textbox. In your command button's On_Click procedure you can pick up the name of the file and use DoCmd.TransferText to import to your temporary table, then execute your append query, and delete the contents of the temporary table when you're finished. You can run a delete query or a SQL statement from code to do this. Let me know if you need any more help in setting this up.
0
 
LVL 3

Author Comment

by:sam_norian
ID: 17913702
Thanks,

This is the exact set up I have at the moment, except I use the TreeView and ListView controls in VB on a form rather than the common dialog box.

Thanks again.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses
Course of the Month14 days, 14 hours left to enroll

770 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