Solved

Reformating a CSV file

Posted on 2006-11-10
8
284 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…

760 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

25 Experts available now in Live!

Get 1:1 Help Now