String Manipulation and output formatting of CSV data for inserting into a SQL Database

Question

I am new to PowerShell and I am trying to import data from a CSV log file into a SQL database.

I use ForEach to format the table and inject the values into a query using Invoke-Sqlcmd, which works okay except the date and time format needed to be changed.

I started by reformatting the date and time to match the smalldatetime format.

I have looked through various sites trying to figure out how to format the text back into its original form. Aside from reprocessing it back into a new CSV file and repeating the process, I can't think of a way to accomplish this offhand ( And I don't even know if that would work as intended or not. )

$CSVImport = Import-CSV $FileBrowser.FileName -Header $FSHeader | Select $_ | ? Time -NotLike 'Time'
             $CSVRowCount = $CSVImport.Count
             "Inserting $CSVRowCount rows from CSV into SQL Table"
             ForEach ($CSVLine in $CSVImport) {
                $CSVLine
                $CSVLine = $CSVLine -Replace '(\d{1,2})\/(\d{1,2})\/(\d{4})', '$3/$1/$2'
$CSVLine = $CSVLine -Replace '(\d{1,2})\/(\d{1,2})\/(\d{4})', '$3/$1/$2'

The output from my $CSVLine variable shows:

Date           ColumnB ColumnC ColumnD 
-------------- ------- ------- ------
2/7/2017 13:28 Second  Third   Last

Afterwards, the format changes to.

@{Date=2017/2/7 13:28; ColumnB=Second; ColumnC=Third; ColumnD=Last}

I am unsure how to utilize the output for the query or reformat it back to the table that existed before.

Any recommendations in how to proceed with this?

Thank you in advance.

score 0 · Answer 1 · answered May 06 '19 at 21:05

0

If you put your date in a DateTime type

[datetime]'2017/2/7 13:28'

You will then have a date time object that you can work with to format to your hearts content:

[datetime]'2017/2/7 13:28' | gm


   TypeName: System.DateTime

Name                 MemberType     Definition                                                                                                                  
----                 ----------     ----------                                                                                                                  
Add                  Method         datetime Add(timespan value)

......

See this past article for help with date time How to format a DateTime in PowerShell

answered May 06 '19 at 21:05

Thom Schumacher

1,469
13
24

Maybe i didn't understand the question fully you were looking to change the formatting of the date right? – Thom Schumacher May 06 '19 at 22:15
Yes, I want to change it from mm/dd/yyyy HH:MM:SS to yyyy/mm/dd/ HH:MM:SS (smalldatetime) for SQL. The information is coming from rows within a CSV file. Each line is stored to $CSVLine as ForEach assigns the header format and CSVImport Reads the file. Sorry, I am still getting used to the text formatting on here, my first time to use it. – Maverick May 06 '19 at 22:29
I hope it is easier to understand now. I finally got the formatting cleaned up a bit on the question. – Maverick May 06 '19 at 22:40

score 0 · Accepted Answer · answered May 07 '19 at 06:28

0

After importing with your user supplied headers Date,ColumnB,ColumnC,ColumnD
$CSVImport is an object with properties - as is $CSVLine.

Your command

$CSVLine = $CSVLine -Replace ...

forces PowerShell to stringify the object with it's properties what results in your output

@{Date=2017/2/7 13:28; ColumnB=Second; ColumnC=Third; ColumnD=Last}

You could do the replace only on $CSVLine.Date
but better convert to a [datetime] as suggested in thom-schumachers answer.

Depending on your locale simply casting might not be sufficient, then try

[datetime]::ParseExact($CSVLine.Date,"M/d/yyyy HH:mm",$Null)

answered May 07 '19 at 06:28

Okay, this is where my ignorance shows through. I have only been working with PowerShell for almost 3 weeks. I am not having any luck using the sample that you provided. ( I am surprised that I have made it this far....lol ). Can I set that to a variable or how do I use the object? I get errors when I attempt to do anything with it. I was able to do CSVLine.Date and -Replace (Worked Great). I then had to overcome the two digit month and day, which I did by using 4 if statements to reformat ( I am sure that someone will tell me that there is an easier way.) Finally, I added :00 to the time. – Maverick May 08 '19 at 01:46

String Manipulation and output formatting of CSV data for inserting into a SQL Database

2 Answers2