Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

Reformating a CSV file

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
sam_norian
Asked:
sam_norian
  • 4
  • 4
1 Solution
 
KelvinYCommented:
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
 
sam_norianAuthor Commented:
Kelvin,

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

Thanks
0
 
KelvinYCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sam_norianAuthor Commented:
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
 
KelvinYCommented:
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
 
sam_norianAuthor Commented:
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
 
KelvinYCommented:
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
 
sam_norianAuthor Commented:
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

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!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now