Solved

Reformating a CSV file

Posted on 2006-11-10
8
286 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
  • 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

776 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