Solved

Reformating a CSV file

Posted on 2006-11-10
8
287 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

861 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