2

I also posted this on the powershell usenet groups, but I think it is mostly dead now.

I am in over my head working on a script. Working with both powershell and log parser. I am currently trying to take csv input and output into a accdb. I have a good working script, with one problem.

One of my fields should be of type datetime. However the csv that exports from powershell via export-csv makes it a string. I would prefer for it to be a datetime format. I have looked into type casting (with LogParser) and into ParseExact (with powershell), but I am lost.

Here is an example of what the csv that export-csv is making:

"TIME","TEMP","HUMID","DEWPT"
"06/28/2011 12:53:13","70.9","79.8","63.6"
"06/28/2011 12:23:07","70.8","78.6","63.1"
"06/28/2011 11:53:00","70.8","78.9","63.2"
"06/28/2011 11:22:53","70.7","78.4","62.9"

Here is what I've come up with for the ParseExact (in powershell):

{ [datetime]::ParseExact($_."TIME","MM'/'dd'/'YYYY' 'HH':'MM':'SS") }

If you need to see the code I am using to append the csv data into the accdb database let me know and I'll post it. I have no preference on where the cast happens. I make the CSV in powershell and I copy it to a table in Access via logparser.

Thanks for looking.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
Ty.
  • 187
  • 1
  • 2
  • 13
  • The parseexact appears unnecessary. That date format parses fine using the default parse method: [datetime]"06/28/2011 12:53:13" – mjolinor Jun 29 '11 at 13:15
  • No luck with either [datetime] or [datetime]:: – Ty. Jun 29 '11 at 14:23
  • Using your posted test data as test_csv.csv, this spits out [datetime] objects: import-csv test_csv.csv | foreach {$_.time -as [datetime]} – mjolinor Jun 29 '11 at 14:34
  • As does this: import-csv test_csv.csv |% {[datetime]$($_.time)} – mjolinor Jun 29 '11 at 14:37
  • I'm starting to think that may be regional settings. – mjolinor Jun 29 '11 at 14:45
  • It does work. It changes the exported csv to include the AM/PM information. However when I try to use logparser to dump into access, it still sees it as a string format. – Ty. Jun 29 '11 at 14:46
  • The exported CSV is formatted like this: `"TIME","TEMP","HUMID","DEWPT" "6/8/2011 12:01:58 AM","70.5","76.3","62.0" "6/8/2011 12:32:07 AM","70.5","77.8","62.5" "6/8/2011 1:02:13 AM","70.5","77.8","62.5" "6/8/2011 1:32:21 AM","70.5","80.2","63.4" "6/8/2011 2:02:27 AM","70.5","79.9","63.3"` – Ty. Jun 29 '11 at 14:52

1 Answers1

1

Any help here?

http://mjolinor.wordpress.com/2011/05/01/typecasting-imported-csv-data/

mjolinor
  • 66,130
  • 7
  • 114
  • 135
  • This works for the purposes of OGV and changes the format to AM/PM when I do the export-csv, but when I use logparser to put into the database, it still sees it as a string. – Ty. Jun 29 '11 at 14:39
  • Anything you export to csv is going to get exported as a string - all csv data is string values. If it needs to be strongly typed when it's committed to the database it needs to be re-cast after it's imported from csv, and before it's committed. Logparser may not be able to deal with anything except string data. – mjolinor Jun 29 '11 at 14:55
  • Ok. Thanks for your help. I tried to vote up your answer, but it says I don't have enough rep points. – Ty. Jun 29 '11 at 15:03