Category filter

Script to convert CSV file on Windows devices

The scripts in this document enable you to convert CSV files stored on Windows devices into various formats, including .txt, .json, .jsonl, .xml, .yaml, and .sql. These conversions can be valuable for tasks such as data migration, database imports, and data sharing. Using Hexnode’s Execute Custom Script action, IT administrators can deploy scripts to Windows devices to convert existing CSV files into different file types.

Disclaimer:

The sample scripts provided below are adapted from third-party open-source sites.

PowerShell script to convert a CSV file to a text file

Unlike CSV files, which often require specific applications for opening with or editing, text files can be easily edited using basic text editors. Converting a CSV file to a text file can provide a basic, simplified representation of data.

# Input CSV file 

$inputFile = " C:\path\to\your\input.csv" 

# Output file 

$outputFile = " C:\path\to\your\output.txt" 

# Read the CSV file 

$csvContent = Import-Csv -Path $inputFile 

# Create the output file  

Out-File -FilePath $outputFile -Force 

foreach ($row in $csvContent) { 

    # Example transformation: Convert the CSV row to a pipe-separated format 

    $line = $row.PSObject.Properties.Value -join '|' 

    # Append the transformed line to the output file 

    Add-Content -Path $outputFile -Value $line 
} 

Write-Host "Conversion completed."

# Input CSV file

$inputFile = " C:\path\to\your\input.csv"

# Output file

$outputFile = " C:\path\to\your\output.txt"

# Read the CSV file

$csvContent = Import-Csv -Path $inputFile

# Create the output file

Out-File -FilePath $outputFile -Force

foreach ($row in $csvContent) {

# Example transformation: Convert the CSV row to a pipe-separated format

$line = $row.PSObject.Properties.Value -join '|'

# Append the transformed line to the output file

Add-Content -Path $outputFile -Value $line

}

Write-Host "Conversion completed."

Here’s how you should modify the given script to include necessary parameters. First, specify the path to your input CSV file ($inputFile) and the desired output text file path ($outputFile). Replace “C:\path\to\your\input.csv” and “C:\path\to\your\output.txt” with the actual paths for your input CSV and output text files.

The script uses PowerShell’s Import-Csv command to read the CSV data. It creates the output file using Out-File. However, if the output file ($outputFile) already exists, then the script will overwrite the existing file, effectively removing any content previously stored in it.

For each row in the CSV content, the script converts the data into a pipe-separated format by joining the values from each column in that row with the ‘|’ character. The transformed row is then written to the output file ($outputFile) using the Add-Content command, appending each row to the file. Once the conversion is complete, the script confirms the completion with an output message.

PowerShell script to convert a CSV file to a JSON file

While CSV files are human-readable and widely used for data exchange, they have limitations in representing complex data structures like nested objects and arrays. In contrast, JSON (JavaScript Object Notation) files provide a more flexible and versatile format for handling intricate data. JSON files are not only human-readable but also widely supported by numerous programming languages and applications, making them an ideal choice for data interchange.

# Input CSV file 

$inputFile = " C:\path\to\your\input.csv" 

# Output JSON file 

$outputFile = " C:\path\to\your\output.json" 

# Read the CSV file 

$csvContent = Import-Csv -Path $inputFile 
  
# Convert CSV content to JSON 

$jsonContent = $csvContent | ConvertTo-Json -Depth 1 
  
# Write the JSON content to the output file 

Set-Content -Path $outputFile -Value $jsonContent 
  
Write-Host "Conversion completed."

# Input CSV file

$inputFile = " C:\path\to\your\input.csv"

# Output JSON file

$outputFile = " C:\path\to\your\output.json"

# Read the CSV file

$csvContent = Import-Csv -Path $inputFile

# Convert CSV content to JSON

$jsonContent = $csvContent | ConvertTo-Json -Depth 1

# Write the JSON content to the output file

Set-Content -Path $outputFile -Value $jsonContent

Write-Host "Conversion completed."

Modify the script to include required attributes for the conversion. Set the path for your input CSV file ($inputFile) and the location of your output JSON file ($outputFile). Make sure to replace “C:\path\to\your\input.csv” and “C:\path\to\your\output.json” with the correct file paths.

The script uses PowerShell’s Import-Csv command to read the CSV data. The entire CSV content is then converted into a JSON object using the ConvertTo-Json command. This command has a -Depth parameter, which determines how many levels of nested objects (objects within objects) within the CSV data should be included in the resulting JSON format. The JSON content is stored in the $jsonContent variable.

Finally, the script writes the JSON content to the specified output file ($outputFile) using the Set-Content command and confirms the completion with an output message.

PowerShell script to convert a CSV file to a JSONL file

JSON Lines (JSONL) is a format for storing structured data, allowing you to process one record at a time. Like JSON, JSONL is human-readable, supported by many programming languages and applications, and can represent complex data structures. However, unlike JSON, which stores multiple records in a single file with nested objects and arrays, JSONL stores each record as a separate JSON object on its own line.

# Input CSV file 

$inputFile = " C:\path\to\your\input.csv" 

# Output JSONL file 

$outputFile = " C:\path\to\your\output.jsonl"   

# Read the CSV file 

$csvContent = Import-Csv -Path $inputFile 
  
# Convert CSV content to JSONL 

foreach ($row in $csvContent) { 

    $jsonObject = $row | ConvertTo-Json -Compress 

    Add-Content -Path $outputFile -Value $jsonObject 
}   
Write-Host "Conversion completed."

# Input CSV file

$inputFile = " C:\path\to\your\input.csv"

# Output JSONL file

$outputFile = " C:\path\to\your\output.jsonl"

# Read the CSV file

$csvContent = Import-Csv -Path $inputFile

# Convert CSV content to JSONL

foreach ($row in $csvContent) {

$jsonObject = $row | ConvertTo-Json -Compress

Add-Content -Path $outputFile -Value $jsonObject

}

Write-Host "Conversion completed."

Make sure the script contains the necessary parameters. Specify the input CSV file path ($inputFile) and the desired output JSONL file path ($outputFile). Replace “C:\path\to\your\input.csv” and “C:\path\to\your\output.jsonl” with the actual paths for your input CSV and output JSONL files.

The script uses PowerShell’s Import-Csv command to read the CSV data. For each row in the CSV content, the script converts it into a JSON object using the ConvertTo-Json command with the -Compress flag to minimize whitespace. The JSON object is then written to the output file ($outputFile) using the Add-Content command, appending each JSON object on a new line. Once the conversion is complete, the script confirms the completion with an output message.

PowerShell script to convert a CSV file to an XML file

XML (Extensible Markup Language) is a markup language similar to HTML that provides guidelines for encoding text in a format that is both machine-readable and human readable. XML is widely used for storing and transferring data on the web and in many other applications.

# Input CSV file 

$inputFile = " C:\path\to\your\input.csv" 

# Output XML file 

$outputFile = " C:\path\to\your\output.xml" 
  
# Read the CSV file 

$csvContent = Import-Csv -Path $inputFile 
  
# Create an XML document 

[xml]$xmlDocument = New-Object System.Xml.XmlDocument 

$root = $xmlDocument.CreateElement("records") 

$xmlDocument.AppendChild($root) | Out-Null 
  
# Convert CSV content to XML 

foreach ($row in $csvContent) { 

    $record = $xmlDocument.CreateElement("record") 

    foreach ($column in $row.PSObject.Properties) { 

        $element = $xmlDocument.CreateElement($column.Name) 

        $element.InnerText = $column.Value 

        $record.AppendChild($element) | Out-Null 
    } 
    $root.AppendChild($record) | Out-Null 
} 
  
# Save the XML document to the output file 
$xmlDocument.Save($outputFile) 
  
Write-Host "Conversion completed."

# Input CSV file

$inputFile = " C:\path\to\your\input.csv"

# Output XML file

$outputFile = " C:\path\to\your\output.xml"

# Read the CSV file

$csvContent = Import-Csv -Path $inputFile

# Create an XML document

[xml]$xmlDocument = New-Object System.Xml.XmlDocument

$root = $xmlDocument.CreateElement("records")

$xmlDocument.AppendChild($root) | Out-Null

# Convert CSV content to XML

foreach ($row in $csvContent) {

$record = $xmlDocument.CreateElement("record")

foreach ($column in $row.PSObject.Properties) {

$element = $xmlDocument.CreateElement($column.Name)

$element.InnerText = $column.Value

$record.AppendChild($element) | Out-Null

}

$root.AppendChild($record) | Out-Null

}

# Save the XML document to the output file

$xmlDocument.Save($outputFile)

Write-Host "Conversion completed."

You are required to provide necessary attributes for the file conversion. Provide the path to your input CSV file ($inputFile) and the location for your output XML file ($outputFile). Update “C:\path\to\your\input.csv” and “C:\path\to\your\output.xml” with your file paths.

The script uses PowerShell’s Import-Csv command to read the CSV data. It then creates an XML document using the System.Xml.XmlDocument class and iterates through each row and column in the CSV, creating XML elements with column names as tags and values as inner text. Finally, the script saves the XML document to the specified output file ($outputFile) using the Save() method and confirms the completion with an output message.

PowerShell script to convert a CSV file to a YAML file

YAML (yet another markup language or YAML ain’t markup language) is another data serialization format that offers several advantages over CSV files. It provides a more human-readable and clean representation of data, supporting complex data structures like nested objects, arrays, and dictionaries. YAML’s use of whitespace and indentation makes it easier to read and write, while its support for comments allows for better documentation.

# Input CSV file 

$inputFile = " C:\path\to\your\input.csv" 

# Output YAML file 

$outputFile = " C:\path\to\your\output.yaml"   

# Read the CSV file 

$csvContent = Import-Csv -Path $inputFile 
  
# Convert CSV content to YAML 

$yamlContent = "" 

foreach ($row in $csvContent) { 

    foreach ($column in $row.PSObject.Properties) { 

        $yamlContent += "{0}: {1}`n" -f $column.Name, $column.Value 
    } 
    $yamlContent += "`n" 
} 
  
# Write the YAML content to the output file 
$yamlContent | Out-File -FilePath $outputFile -Encoding UTF8 
  
Write-Host "Conversion completed."

# Input CSV file

$inputFile = " C:\path\to\your\input.csv"

# Output YAML file

$outputFile = " C:\path\to\your\output.yaml"

# Read the CSV file

$csvContent = Import-Csv -Path $inputFile

# Convert CSV content to YAML

$yamlContent = ""

foreach ($row in $csvContent) {

foreach ($column in $row.PSObject.Properties) {

$yamlContent += "{0}: {1}`n" -f $column.Name, $column.Value

}

$yamlContent += "`n"

}

# Write the YAML content to the output file

$yamlContent | Out-File -FilePath $outputFile -Encoding UTF8

Write-Host "Conversion completed."

You are required to add the necessary attributes to the script. Begin by specifying the path to the input CSV file ($inputFile) and the path where you want to save the output YAML file ($outputFile). Be sure to replace “C:\path\to\your\input.csv” and “C:\path\to\your\output.yaml” with the correct paths.

The script uses PowerShell’s Import-Csv command to read the CSV data. It then iterates through each row and column of the CSV content, converting the data into YAML key-value pairs. The converted YAML content is stored in the $yamlContent variable. Finally, the script writes the YAML content to the specified output file ($outputFile) using the Out-File command and confirms the completion with an output message.

PowerShell script to convert a CSV file to an SQL file

SQL (Structured Query Language) is used for managing and manipulating relational databases. Unlike CSV, which are primarily used for data storage and exchange, SQL enables direct interaction with databases, allowing users to perform various operations like querying, updating, inserting, and deleting data.

# Input CSV file 

$inputFile = " C:\path\to\your\input.csv" 

# Output SQL file 

$outputFile = " C:\path\to\your\output.sql"   

# Read the CSV file 

$csvContent = Import-Csv -Path $inputFile 
  
# Prepare SQL insert statements 

$sqlStatements = foreach ($row in $csvContent) { 

    $columns = [string]::Join(', ', ($row.PSObject.Properties.Name)) 

    $values = $row.PSObject.Properties.Value -join "', '" 

    "INSERT INTO TableName ($columns) VALUES ('$values');" 
} 
  
# Write the SQL statements to the output file 

$sqlStatements | Out-File -FilePath $outputFile -Encoding UTF8 
  
Write-Host "Conversion completed."

# Input CSV file

$inputFile = " C:\path\to\your\input.csv"

# Output SQL file

$outputFile = " C:\path\to\your\output.sql"

# Read the CSV file

$csvContent = Import-Csv -Path $inputFile

# Prepare SQL insert statements

$sqlStatements = foreach ($row in $csvContent) {

$columns = [string]::Join(', ', ($row.PSObject.Properties.Name))

$values = $row.PSObject.Properties.Value -join "', '"

"INSERT INTO TableName ($columns) VALUES ('$values');"

}

# Write the SQL statements to the output file

$sqlStatements | Out-File -FilePath $outputFile -Encoding UTF8

Write-Host "Conversion completed."

Before executing the script, you are also expected to provide necessary attributes. Set the paths for your input CSV file ($inputFile) and the SQL output file ($outputFile). Replace “C:\path\to\your\input.csv” and “C:\path\to\your\output.sql” with the actual paths for your files, and replace ‘TableName’ with your database table name.

The script reads the CSV data using Import-Csv, processes each row to extract column names and values, and creates an SQL INSERT statement for each row. These statements are written to the output file ($outputFile). Once done, the script confirms the completion with an output message.

What happens at the device end?

When executed, each script converts the CSV file into the specified format and saves the converted file to the output path specified within the script. And an output message saying “Conversion completed” will be displayed on the Action History tab of the device.

Notes:

It is recommended to manually validate the script execution on a system before executing the action in bulk.
Hexnode will not be responsible for any damage/loss to the system on the behavior of the script.

Need more help?

Raise a Ticket

Ask Community

Chat With us