mezcel/ExportAllToCsv.vb

## readme.md

      
    Raw
  

              readme.md
            
          
    Messing with LibreOffice Macros

Forum Links:


'[Solved] How to mass export certain sheet to CSV?'


Exporting all Libreoffice worksheets as CSV


how do I export all sheets from a spreadsheet ?


## bashTroubleshoot.sh
#!/bin/bash
# CSV to JSON converter using BASH
# inspired from http://blog.secaserver.com/2013/12/convert-csv-json-bash/
# Usage bash csv2json.sh myScvScript.csv > jsonOutputScript.json


input=$1

[ -z $1 ] && echo "No CSV input file specified" && exit 1
[ ! -e $input ] && echo "Unable to locate $1" && exit 1

read first_line < $input
a=0
headings=`echo $first_line | awk -F, {'print NF'}`
lines=`cat $input | wc -l`
while [ $a -lt $headings ]
do
    head_array[$a]=$(echo $first_line | awk -v x=$(($a + 1)) -F"," '{print $x}')
    a=$(($a+1))
done

c=0
echo "{"
while [ $c -lt $lines ]
do
    read each_line
    if [ $c -ne 0 ]; then

        d=0
        echo -n "{"
        while [ $d -lt $headings ]
        do
            # non zero value indicates a match was found
            fk_preview1=$(echo ${head_array[$d]} | grep -o "fk_preview1" | wc -w)
            fk_preview2=$(echo ${head_array[$d]} | grep -o "fk_preview2" | wc -w)
            fk_preview3=$(echo ${head_array[$d]} | grep -o "fk_preview3" | wc -w)
            fk_preview4=$(echo ${head_array[$d]} | grep -o "fk_preview4" | wc -w)
            fk_preview0=$(($fk_preview1+$fk_preview2+$fk_preview3+$fk_preview4))
            # echo $fk_preview0

            each_element=$(echo $each_line | awk -v y=$(($d + 1)) -F"," '{print $y}')

            # remove quotes
            each_element=${each_element//\"}

            if ! [[ $each_element =~ ^-?[0-9]+$ ]]; then
                # wrap quotes if not an int
                each_element=\"$each_element\"
            fi

            if [ $fk_preview0 -eq 0 ]; then
                if [ $d -ne $(($headings-1)) ]; then
                    nextField=${head_array[$(($d+1))]}
                    case $nextField in "fk_preview1"|"fk_preview2"|"fk_preview3"|"fk_preview4")
                        echo -n \"${head_array[$d]}\":$each_element

                        ;;
                    *)
                        echo -n \"${head_array[$d]}\":$each_element
                        ;;
                    esac
                fi
                if [ $d -ne $(($headings-2)) ]; then
                    echo ","
                fi

            fi

            d=$(($d+1))
        done
        if [ $c -eq $(($lines-1)) ]; then
            echo "}"
        else
            echo "},"
        fi
    fi
    c=$(($c+1))
done < $input
echo "}"

## cleanmyscv.ps1
<#
Note:
This script is just string to string. The ID col will be a string instead of an int.
If i made all the field names for the ID Col the same name, ex:'id', i could then just have Powershell covert that col to int with ' -replace ':"([0-9]+)"',':$1'} '

here was a clue: https://stackoverflow.com/a/45964102/5861315

Powershelll Notes:

powershell -ExecutionPolicy ByPass -File D:\github\Toggle-Tag\PowerShell\cleanmyscv.ps1
https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/import-csv?view=powershell-6
https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/export-csv?view=powershell-6
https://blogs.technet.microsoft.com/heyscriptingguy/2011/10/17/easily-remove-columns-from-a-csv-file-by-using-powershell/
https://stackoverflow.com/a/21905177/5861315
https://blogs.technet.microsoft.com/heyscriptingguy/2011/11/02/remove-unwanted-quotation-marks-from-csv-files-by-using-powershell/
#>


try{
	$dirMyRoot = "D:\github\Toggle-Tag\"
	$dirCsvFromLibreOffice = $dirMyRoot+"csvfromLibreOffice\"
	$outDirCsv = $dirMyRoot+"csvforMySqlWorkbench\"
	$outDirJson = $dirMyRoot+"jsonforMySqlWorkbench\"

	# Check for a root Dir
	If(!(test-path $dirCsvFromLibreOffice)){
		Write-Host $dirCsvFromLibreOffice+" does not exist. You need a root folder like: "+$dirMyRoot+" and a csv source folder like: "+$dirCsvFromLibreOffice
		Write-Host "Terminating script ..."
		pause
		$host.Exit()
	}Else{
		Write-Host $dirCsvFromLibreOffice" exists"
	}

	# Make Csv Dir if Dir does not exist
	If(!(test-path $outDirCsv)){
		New-Item -ItemType Directory -Force -Path $outDirCsv
		Write-Host "Created Dir: "$outDirCsv
	}Else{
		Write-Host "Csv's will go in : "$outDirCsv
	}

	# Make Json Dir if Dir does not exist
	If(!(test-path $outDirJson)){
		New-Item -ItemType Directory -Force -Path $outDirJson
		Write-Host "Created Dir: "$outDirJson
	}Else{
		Write-Host "Json's will go in: "$outDirJson
	}

	# make an array of file names contained in the source dir
	$dirChildrenNames = Get-ChildItem -Path $dirCsvFromLibreOffice

	# Populate CSV and JSON directories
	foreach ($item in $dirChildrenNames){
		Write-Host " --------------------------------------------------------------------------------- "
		$filePathIn = $dirCsvFromLibreOffice+$item
		$filePathOutCsv = $outDirCsv+$item

		#Delete Csv file if exists
		If (Test-Path $filePathOutCsv){
			Remove-Item $filePathOutCsv
		}

		# remove UI testing preview cols from LibreOffice generated csv's
		Import-Csv $filePathIn -Delimiter "," | Select * -ExcludeProperty fk_preview1,fk_preview2,fk_preview3,fk_preview4 | Export-Csv $filePathOutCsv -Delimiter "," -NoTypeInformation

		Write-Host "Destination: "$filePathOutCsv
		Write-Host "	"$item

		$replaceString = $item -replace '.csv','.json'
		$filePathOutJson = $outDirJson+$replaceString

		#Delete Json file if exists
		If (Test-Path $filePathOutJson){
			Remove-Item $filePathOutJson
		}

		# Maje a new Json file from the new Csv
		Import-Csv $filePathOutCsv -Delimiter "," | ConvertTo-Json -Compress | Add-Content -Path $filePathOutJson
		Write-Host "Destination: "$filePathOutJson
		Write-Host "	"$replaceString
		Write-Host " --------------------------------------------------------------------------------- "
	}
	Write-Host
	Write-Host
	Write-Host
	Write-Host "!!! Process complete. There will now be 2 popup windows to verify your files were made !!!"
	pause
	Invoke-Item $outDirCsv
	Invoke-Item $outDirJson
}catch{
    Write-Error $_.Exception.ToString()
    Read-Host -Prompt "The above error occurred. Press Enter to exit."
	pause
}

## convertSheetsToCSVs.vb
Sub convertSheetsToCSVs
	'Dim fileProps(0) as new com.sun.star.beans.PropertyValue
	Dim fileProps(1) as new com.sun.star.beans.PropertyValue
	sheets = ThisComponent.Sheets

	' the UI for the destination path
	NumSheets = sheets.Count - 1 ' last sheet is the macro sheet
	xSheet = ThisComponent.sheets(NumSheets)
	oCell = xSheet.getCellByPosition(1,0) ' $B$1 ' file:///D:/github/Toggle-Tag/csvExports/
	destinationPath = oCell.string

	fileProps(0).Name = "FilterName"
	fileProps(0).Value = "Text - txt - csv (StarCalc)"

	' ASCII Codes http://www.theasciicode.com.ar/ascii-printable-characters/semicolon-ascii-code-59.html
	fileProps(1).Name = "FilterOptions"
	' https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options#Token_8.2C_csv_export
	fileProps(1).Value ="44,34,11" '44=comma 34=doublequotes, 11=ASCII/US (Western)

	i = 0

	Do While sheets.Count > i
	  sheet = sheets.getByIndex(i)
	  cntrllr = ThisComponent.CurrentController
	  cntrllr.setActiveSheet(sheet)
	  sURL = destinationPath & sheets.ElementNames(i) & ".csv"

	  ThisComponent.storeToURL(sURL, fileProps())
	  i = i + 1
	Loop

	msgbox "CSV Export complete! Check in the [" + destinationPath + "] dir to view your files"
End Sub

## csv2json.sh
#!/bin/bash
# CSV to JSON converter using BASH
# inspired from http://blog.secaserver.com/2013/12/convert-csv-json-bash/
# Usage bash csv2json.sh myScvScript.csv > jsonOutputScript.json

input=$1

[ -z $1 ] && echo "No CSV input file specified" && exit 1
[ ! -e $input ] && echo "Unable to locate $1" && exit 1

read first_line < $input
a=0
headings=`echo $first_line | awk -F, {'print NF'}`
lines=`cat $input | wc -l`
while [ $a -lt $headings ]
do
        head_array[$a]=$(echo $first_line | awk -v x=$(($a + 1)) -F"," '{print $x}')
        a=$(($a+1))
done

c=0
echo "{"
while [ $c -lt $lines ]
do
        read each_line
        if [ $c -ne 0 ]; then
                d=0
                echo -n "{"
                while [ $d -lt $headings ]
                do
                        each_element=$(echo $each_line | awk -v y=$(($d + 1)) -F"," '{print $y}')

                        # remove quotes
                        each_element=${each_element//\"}

                        if ! [[ $each_element =~ ^-?[0-9]+$ ]]; then
                            # wrap quotes if not an int
                            each_element=\"$each_element\"
                        fi

                        if [ $d -ne $(($headings-1)) ]; then
                                echo -n ${head_array[$d]}":"$each_element","
                        else
                                echo -n ${head_array[$d]}":"$each_element
                        fi
                        d=$(($d+1))
                done
                if [ $c -eq $(($lines-1)) ]; then
                        echo "}"
                else
                        echo "},"
                fi
        fi
        c=$(($c+1))
done < $input
echo "}"

## csv2jsonDir.sh
#!/bin/bash

# CSV to JSON converter using BASH
# requires csv2json.sh file

relativeInputRootDir=`ls ../csvForMySql/*.csv`
relativeOutputDir="../jsonForMySqlNoQuotes/"

for eachfile in $relativeInputRootDir
do
    echo $relativePath
   relativePath=$eachfile
   csvBaseName=$(basename ${relativePath} .${relativePath##*.})
   echo $csvBaseName

   outputFile=$relativeOutputDir$csvBaseName.json


   echo $outputFile

   bash csv2json2.sh $relativePath > $outputFile

done

## ExportAllToCsv.vb
Sub ExportAllToCsv
	document = ThisComponent

	' Use the global string tools library to generate a base filename for each CSV
	' based on the current prefixless filename
	GlobalScope.BasicLibraries.loadLibrary("Tools")
	BaseFilename = Tools.Strings.GetFileNameWithoutExtension(document.getURL(), "/")

	' Work out number of sheets for looping over them later.
	Sheets = document.Sheets
	NumSheets = Sheets.Count - 1

	' Set up a propval object to store the filter properties
	Dim Propval(1) as New com.sun.star.beans.PropertyValue
	Propval(0).Name = "FilterName"
	Propval(0).Value = "Text - txt - csv (StarCalc)"
	Propval(1).Name = "FilterOptions"
	Propval(1).Value ="59,34,0,1,1"   'ASCII  59 = ;  34 = "

	For I = 0 to NumSheets
		' For each sheet, assemble a filename and save using the filter
		SheetName = Sheets(I).Name
		document.getCurrentController.setActiveSheet(Sheets(I))
		Filename = "/tmp/" + BaseFilename + "." + SheetName + ".csv"
		FileURL = convertToURL(Filename)
		document.StoreAsURL(FileURL, Propval())
	Next I

	' Libreoffice thinks our filename is now the last-exported-CSV filename, so close.
	Msgbox "Files saved as /tmp/" + BaseFilename + ".*.csv. You'll need to close the spreadsheet now."
End Sub
' http://www.jpstacey.info/blog/2014-09-12/exporting-all-libreoffice-worksheets-csv

## gistfile1.txt
# powershell -ExecutionPolicy ByPass -File D:\github\Toggle-Tag\PowerShell\cleanmyscv.ps1
# https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/import-csv?view=powershell-6
# https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/export-csv?view=powershell-6
# https://blogs.technet.microsoft.com/heyscriptingguy/2011/10/17/easily-remove-columns-from-a-csv-file-by-using-powershell/
try
{
	$dirPathString = "D:\github\Toggle-Tag\csvfromLibreOffice\"
	$outDir = "D:\github\Toggle-Tag\csvforMySqlWorkbench\"


	# make an array of file names contained in the dir
	$dirChildrenNames = Get-ChildItem -Path $dirPathString

	foreach ($item in $dirChildrenNames)
	{
		$filePathIn = $dirPathString+$item
		$filePathOut = $outDir+$item

		Import-Csv $filePathIn -Delimiter "," | Select * -ExcludeProperty fk_preview1,fk_preview2,fk_preview3,fk_preview4 | Export-Csv $filePathOut -Delimiter "," -NoTypeInformation

		Write-Host $filePathOut
	}
	pause
}
catch
{
    Write-Error $_.Exception.ToString()
    Read-Host -Prompt "The above error occurred. Press Enter to exit."
	pause
}

## latestBash.sh
#!/bin/bash
# TAB CSV to JSON converter using BASH
# Usage bash csv2json.sh myScvScript.csv > jsonOutputScript.json
# this script is called by another .sh named csv2jsonDir.sh

input=$1

[ -z $1 ] && echo "No CSV input file specified" && exit 1
[ ! -e $input ] && echo "Unable to locate $1" && exit 1

read first_line < $input
noRecords=`cat $input | wc -l`
noFields=`echo -e $first_line | awk {'print NF'}`
declare -a fieldArr
fieldArr=()

echo "{"
while IFS=$'\t' read -r -a csvRecordsArray
do
    fieldNo=0
    skipFieldCounter=1

    if [[ $recordNo -ne 0 ]]; then echo -n "{"; fi
    while [[ $fieldNo -lt $noFields ]]
    do
        if [[ $recordNo -ne 0 ]]; then
            exemptVar=${fieldArr[$fieldNo]}
            case "$exemptVar" in "fk_preview1"|"fk_preview2"|"fk_preview3"|"fk_preview4")
                # skip field
                skipFieldCounter=$(($skipFieldCounter + 1))
                ;;
            *)
                quotedVal=${csvRecordsArray[$fieldNo]}
                if ! [[ $quotedVal =~ ^-?[0-9]+$ ]]; then
                    # wrap quotes if not an int
                    echo -n \"${fieldArr[$fieldNo]}\":\"$quotedVal\"
                else
                    echo -n \"${fieldArr[$fieldNo]}\":$quotedVal
                fi
                if [[ $fieldNo -lt $(($noFields - $skipFieldCounter)) ]]; then echo -n ","; fi
                ;;
            esac


        else
            # last delineated obj, no comma
            fieldArr=("${fieldArr[@]}" ${csvRecordsArray[$fieldNo]})
        fi
        fieldNo=$(($fieldNo+1))
    done
    if [[ $recordNo -gt 0 ]]; then echo "}"; fi

    recordNo=$(($recordNo+1))

    if [[ $recordNo -lt $noRecords ]] && [[ $recordNo -gt 1 ]]; then echo -n ","; fi
done < $input
echo "}"

## latestMacro.vb
REM  *****  BASIC  *****

Sub Main

End Sub

Sub convertSheetsToCSVs
	' This macro cycles though every sheet in the Cacl Doc and converts it to a CSV file.
	' The files are sent to a user defined directory path featured on a sheet neamed macros
	' For my convenience, Windows exports Comma Csv, Linus exports Tab Csv

	Dim fileProps(1) as new com.sun.star.beans.PropertyValue 'property array'
	Dim relativeDir(1) as new com.sun.star.beans.PropertyValue
	relativeDir(0).Value = "file://../Shell-Scripts"
	relativeDir(1).Value = GetCurrentFolder() & "/csvFromLibreOffice/"

	sheets = ThisComponent.Sheets

	NumSheets = sheets.Count - 1 ' last sheet is the macro sheet
	xSheet = ThisComponent.sheets(NumSheets)
	oCell = xSheet.getCellByPosition(1,0) ' $B$1 ' file:///LibreOffice-Calc/csvfromLibreOffice
	'destinationPath = oCell.string
	destinationPath = relativeDir(1).Value

	fileProps(0).Name = "FilterName"
	fileProps(0).Value = "Text - txt - csv (StarCalc)"

	' Filter Options:
	' https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options#Token_8.2C_csv_export
	fileProps(1).Name = "FilterOptions"

	' ASCII Codes:
	' http://www.theasciicode.com.ar/ascii-printable-characters/semicolon-ascii-code-59.html


	If GetGUIType() = 1 Then
		'Windows comma csv output
		fileProps(1).Value ="44,34,11" '44=comma 34=doublequotes, 11=ASCII/US (Western)
	ElseIf GetGUIType() = 4 Then
		'Linux tabs csv output
		fileProps(1).Value ="09,34,11" '44=tab 34=doublequotes, 11=ASCII/US (Western)
	ElseIf GetGUIType() = 3 Then
		'Mac tabs csv output
		fileProps(1).Value ="09,34,11" '44=tab 34=doublequotes, 11=ASCII/US (Western)
	End If


	i = 0

	Do While sheets.Count > i
	  sheet = sheets.getByIndex(i)
	  cntrllr = ThisComponent.CurrentController
	  cntrllr.setActiveSheet(sheet)
	  sURL = destinationPath & sheets.ElementNames(i) & ".csv"

	  ThisComponent.storeToURL(sURL, fileProps())
	  i = i + 1
	Loop

	msgbox "CSV Export complete! Check within the [" + destinationPath + "] dir  to view your files you should now run the PowerShell or BASH script hosted within D:/github/Toggle-Tag/Shell-Scripts"

	DisplayGUIType(relativeDir(0).Value, relativeDir(1).Value)

End Sub

Function GetCurrentFolder
    sUrl = ThisComponent.getURL()
    sParts = Split(sUrl, "/")
    ReDim Preserve sParts(0 to UBound(sParts) - 1)
    GetCurrentFolder = Join(sParts, "/")
End Function

Sub DisplayGUIType(dir1 As String, dir2 As String)
	Dim s As String

	thisComponentUrl = ThisComponent.getURL()
	sParts = Split(thisComponentUrl, "/")
	ReDim Preserve sParts(0 to UBound(sParts) - 2)
	joinPath = Join(sParts, "/")


	Select Case GetGUIType()
		Case 1
			s = "Windows"
			joinPath = joinPath & "/Shell-Scripts/PowerShell"
			Shell "explorer.exe" & " " & joinPath, vbNormalFocus ' Windows Explorer

		Case 4
			s = "UNIX"
			joinPath = joinPath & "/Shell-Scripts/BASH"

			On Error Goto MacShell
				' Linux is my primary UNIX
				Shell ("nemo " & joinPath) ' Linux Mint Nemo
				Resume ProceedFileManager

			MacShell:
				' Mac is my rarely used UNIX
				Shell ("open " & joinPath) ' Mac OS Finder
				Resume ProceedFileManager

			ProceedFileManager:
				' i cant run shell directly from within LibreOffice
				' Shell ("sh " & joinPath & " /csv2jsonDir.sh")

		Case Else
			s = "Unknown value " & CStr(GetGUIType()) & CHR$(10) & "Probably running in Client/Server mode"

		End Select
			MsgBox "GUI type is " & s, 0, "GetGUIType()"

End Sub ' The value -1 is returned if the type is not known

## libreofficeMacro.vb
REM  *****  BASIC  *****

Sub Main

End Sub

Sub convertSheetsToCSVs
	' Visual Basic for Applications (VBA) is an implementation of Microsoft's event-driven programming language Visual Basic 6.
	' Visual Basic for Applications enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs).
	' This macro cycles though every sheet in the Cacl Doc and converts it to a CSV file. The files are sent to a user defined directory path featured on a sheet neamed macros

	Dim fileProps(1) as new com.sun.star.beans.PropertyValue 'property array'
	Dim relativeDir(1) as new com.sun.star.beans.PropertyValue
	relativeDir(0).Value = "file://../Shell-Scripts"
	relativeDir(1).Value = GetCurrentFolder() & "/csvfromLibreOffice/"

	sheets = ThisComponent.Sheets

	NumSheets = sheets.Count - 1 ' last sheet is the macro sheet
	xSheet = ThisComponent.sheets(NumSheets)
	oCell = xSheet.getCellByPosition(1,0) ' $B$1 ' file:///LibreOffice-Calc/csvfromLibreOffice
	'destinationPath = oCell.string
	destinationPath = relativeDir(1).Value

	fileProps(0).Name = "FilterName"
	fileProps(0).Value = "Text - txt - csv (StarCalc)"

	' Filter Options:
	' https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options#Token_8.2C_csv_export
	fileProps(1).Name = "FilterOptions"

	' ASCII Codes:
	' http://www.theasciicode.com.ar/ascii-printable-characters/semicolon-ascii-code-59.html
	fileProps(1).Value ="44,34,11" '44=comma 34=doublequotes, 11=ASCII/US (Western)

	i = 0

	Do While sheets.Count > i
	  sheet = sheets.getByIndex(i)
	  cntrllr = ThisComponent.CurrentController
	  cntrllr.setActiveSheet(sheet)
	  sURL = destinationPath & sheets.ElementNames(i) & ".csv"

	  ThisComponent.storeToURL(sURL, fileProps())
	  i = i + 1
	Loop

	msgbox "CSV Export complete! Check within the [" + destinationPath + "] dir to view your files"
	DisplayGUIType(relativeDir(0).Value, relativeDir(1).Value)
End Sub

Function GetCurrentFolder
    sUrl = ThisComponent.getURL()
    sParts = Split(sUrl, "/")
    ReDim Preserve sParts(0 to UBound(sParts) - 1)
    GetCurrentFolder = Join(sParts, "/")
End Function

Sub DisplayGUIType(dir1 As String, dir2 As String)
	Dim s As String
	Select Case GetGUIType()
	Case 1
		s = "Windows"
		Shell "explorer.exe" & " " & dir1, vbNormalFocus
	Case 2
		s = "OS/2" ' Set in the source code, but no longer documented.
	Case 3
		s = "Mac OS" ' Used to be documented, never supported, I expect Mac to return 4.
	Case 4
		s = "UNIX"
		Shell "nemo" & " " & dir2, vbNormalFocus
	Case Else
		s = "Unknown value " & CStr(GetGUIType()) & CHR$(10) &_
	"Probably running in Client/Server mode"
	End Select
		MsgBox "GUI type is " & s, 0, "GetGUIType()"
End Sub
' The value -1 is returned if the type is not known

## messingWithTabs.sh
#!/bin/bash
# CSV to JSON converter using BASH
# inspired from http://blog.secaserver.com/2013/12/convert-csv-json-bash/
# Usage bash csv2json.sh myScvScript.csv > jsonOutputScript.json


input=$1

[ -z $1 ] && echo "No CSV input file specified" && exit 1
[ ! -e $input ] && echo "Unable to locate $1" && exit 1

read first_line < $input
a=0
#headings=`echo $first_line | awk -F, {'print NF'}`
headings=`echo -e $first_line | awk {'print NF'}`
lines=`cat $input | wc -l`

while [ $a -lt $headings ]
do
    #head_array[$a]=$(echo $first_line | awk -v x=$(($a + 1)) -F"," '{print $x}')
    head_array[$a]=$(echo -e $first_line | awk -v x=$(($a + 1)) '{print $x}')
    a=$(($a+1))
done

c=0
echo "{"
while [ $c -lt $lines ]
do
    read each_line

    mycount=0
    while IFS=$'\t' read -r -a myArray
    do
        echo $mycount
        echo "${myArray[1]}"
        echo "${myArray[2]}"
        echo "${myArray[3]}"
        echo "${myArray[4]}"
        echo "${myArray[5]}"
        echo "${myArray[6]}"
        echo "${myArray[7]}"
        echo "${myArray[8]}"
        echo "${myArray[9]}"
        mycount=$(($mycount+1))
    done

    if [ $c -ne 0 ]; then

        d=0
        echo -n "{"
        while [ $d -lt $headings ]
        do
            # non zero value indicates a match was found
            fk_preview1=$(echo ${head_array[$d]} | grep -o "fk_preview1" | wc -w)
            fk_preview2=$(echo ${head_array[$d]} | grep -o "fk_preview2" | wc -w)
            fk_preview3=$(echo ${head_array[$d]} | grep -o "fk_preview3" | wc -w)
            fk_preview4=$(echo ${head_array[$d]} | grep -o "fk_preview4" | wc -w)
            fk_preview0=$(($fk_preview1+$fk_preview2+$fk_preview3+$fk_preview4))


            #each_element=$(echo $each_line | awk -v y=$(($d + 1)) -F"," '{print $y}')
            each_element=$(echo $each_line | awk -v y=$(($d + 1)) -F"\t" '{print $y}')

            #echo d = $d , each_element = $each_element

            # remove quotes
            each_element=${each_element//\"}
            # each_element=$each_element | sed 's/"//g'


            if ! [[ $each_element =~ ^-?[0-9]+$ ]]; then
                # wrap quotes if not an int
                each_element=\"$each_element\"
                # escape char comas
                # each_element=$each_element | sed 's/,/\\\,/g;s/|/,/g'
                #each_element=$each_element | sed 's/,//g'

            fi

            if [ $fk_preview0 -eq 0 ]; then
                if [ $d -ne $(($headings-1)) ]; then
                    nextField=${head_array[$(($d+1))]}
                    case $nextField in "fk_preview1"|"fk_preview2"|"fk_preview3"|"fk_preview4")
                        #echo -n ${head_array[$d]}:$each_element
                        jsonObj0=${head_array[$d]}":"$each_element

                        ;;
                    *)
                        #echo -n ${head_array[$d]}:$each_element
                        jsonObj0=${head_array[$d]}":"$each_element
                        ;;
                    esac

                    #jsonObj0=$jsonObj0 | sed 's/,/\\\,/g;s/|/,/g'
                    #echo -n $jsonObj0
                    echo ${head_array[$d]}":"${myArray[$(($d + 1))]}

                fi

                if [ $d -ne $(($headings-2)) ]; then
                    #echo ","
                    echo ","
                fi

            fi

            d=$(($d+1))
        done
        if [ $c -eq $(($lines-1)) ]; then
            echo "}"
        else
            #echo "},"
            echo "},"
        fi
    fi
    c=$(($c+1))
done < $input
echo "}"

## powershell-csvcleaner.ps1
<#
Note:
This script is just a string to string manipulation.
I want to take csv's generated by a LibreOffice Calc macro and keep only the usable field columns for MySql imports. (no csv header)
Then I want to convert that csv into a Json format used in my app (int numbers are not quoted)
I also want to perform directory management
#>

$PSScriptRoot = Split-Path -Parent -Path $MyInvocation.MyCommand.Definition
Write-Host "Directory of the current file = "$PSScriptRoot
Write-Host

try{
	$dirMyRoot = "D:\github\Toggle-Tag\"
	$dirCsvFromLibreOffice = $dirMyRoot+"csvfromLibreOffice\"
	$outDirCsv = $dirMyRoot+"csvForMySql\"
	$outDirJson = $dirMyRoot+"jsonForMySql\"
	$outDirJsonNoQuotes = $dirMyRoot+"jsonForMySqlNoQuotes\"
	$tempTestLocation = "D:\github\Toggle-Tag\taffydb-testbench\app-json"

	Write-Host "********"
	# Check for a root Dir
	Write-Host "Checking root dir ..."$dirCsvFromLibreOffice
	If(!(test-path $dirCsvFromLibreOffice)){
		Write-Host $dirCsvFromLibreOffice+" does not exist. You need a root folder like: "+$dirMyRoot+" and a csv source folder like: "+$dirCsvFromLibreOffice
		Write-Host
		Write-Host "Terminating script ..."
		Write-Host
		pause
		$host.Exit()
	}Else{
		Write-Host $dirCsvFromLibreOffice" exists"
		Write-Host
	}

	# Make Csv Dir if Dir does not exist
	# with headers
	Write-Host "Checking Csv destination dir ..."$outDirCsv
	If(!(test-path $outDirCsv)){
		New-Item -ItemType Directory -Force -Path $outDirCsv
		Write-Host "Created Dir: "$outDirCsv
		Write-Host
	}Else{
		Write-Host "Csv's, with headers, will go in : "$outDirCsv
		Write-Host
	}

	# Make Json Dir if Dir does not exist
	# json with quotes
	Write-Host "Checking Json destination dir ..."$outDirJson
	If(!(test-path $outDirJson)){
		New-Item -ItemType Directory -Force -Path $outDirJson
		Write-Host "Created Dir: "$outDirJson
		Write-Host
	}Else{
		Write-Host "Json's 'with quotes' will go in: "$outDirJson
		Write-Host
	}
	# json with out quotes
	Write-Host "Checking Json destination dir ..."$outDirJsonNoQuotes
	If(!(test-path $outDirJsonNoQuotes)){
		New-Item -ItemType Directory -Force -Path $outDirJsonNoQuotes
		Write-Host "Created Dir: "$outDirJsonNoQuotes
		Write-Host
	}Else{
		Write-Host "Json's 'without quotes' will go in: "$outDirJsonNoQuotes
		Write-Host
	}

	# make an array of file names contained in the source dir
	$dirChildrenNames = Get-ChildItem -Path $dirCsvFromLibreOffice

	Write-Host "	********"
	# Populate CSV and JSON directories
	Write-Host
	Write-Host ">>> Performing csv to json process..."
	Write-Host
	foreach ($item in $dirChildrenNames){
		Write-Host "--------"
		$filePathIn = $dirCsvFromLibreOffice+$item
		$filePathOutCsv = $outDirCsv+$item

		Write-Host ">> Input CSV source: "$filePathIn
		Write-Host

		#Delete Csv with quotes and headers file if exists
		If (Test-Path $filePathOutCsv){
			Remove-Item $filePathOutCsv
			Write-Host "   Removed pre-existing Csv, with HEADERS and 'quotes' :"$filePathOutCsv
		}

		# remove UI testing preview cols from LibreOffice generated csv's
		Import-Csv $filePathIn -Delimiter "," | Select * -ExcludeProperty fk_preview1,fk_preview2,fk_preview3,fk_preview4 | Export-Csv $filePathOutCsv -Delimiter "," -NoTypeInformation

		Write-Host
		Write-Host "	CSV file: "$item
		Write-Host
		Write-Host "	>> Output CSV 'with headers' path: "$filePathOutCsv
		Write-Host

		$replaceString = $item -replace '.csv','.json'

		$filePathOutJson = $outDirJson+$replaceString

		#Delete Json with quotes file if exists
		If (Test-Path $filePathOutJson){
			Remove-Item $filePathOutJson
			Write-Host "   Removed pre-existing Json 'with quotes' :"$filePathOutJson
			Write-Host
		}

		Write-Host "	Json file: "$replaceString
		Write-Host

		# Maje a new Json file with quotes from the new Csv
		Import-Csv $filePathOutCsv -Delimiter "," | ConvertTo-Json -Compress | Add-Content -Path $filePathOutJson
		Write-Host "	>> Output JSON, 'with quotes', path: "$filePathOutJson

		$filePathOutJsonNoQuote = $outDirJsonNoQuotes+$replaceString

		#Delete Json with quotes file if exists
		If (Test-Path $filePathOutJsonNoQuote){
			Remove-Item $filePathOutJsonNoQuote
			Write-Host "   Removed pre-existing Json 'without quotes' :"$filePathOutJsonNoQuote
			Write-Host
		}

		Write-Host "in file for NO Quote: "$filePathOutCsvNoQuotes" line 183"
		# Maje a new Json file without quotes from the new Csv without quotes
		import-csv $filePathOutCsv | ConvertTo-Json -Compress | Foreach {$_ -replace ':"([0-9]+)"',':$1'} | Out-File $filePathOutJsonNoQuote
		Write-Host "	>> Output JSON, 'without quotes', path: "$filePathOutJsonNoQuote

		# ------------ testbench -------------------------------------
		#Delete Json file if exists
		If (Test-Path $tempTestLocation$replaceString){
			Remove-Item $tempTestLocation$replaceString
			Write-Host "Removed pre-existing Json (for testbench) :"$filePathOutJsonNoQuote
			Write-Host
		}

		Copy-Item $filePathOutJsonNoQuote -Destination $tempTestLocation
		Write-Host "	>> Copied JSON 'without quotes Testbench' to path: "$tempTestLocation

		Write-Host "	--------"
		Write-Host
	}
	Write-Host
	Write-Host "DONE."
	Write-Host
	Write-Host "!!! Process complete. There will now be 2 popup windows to verify your files were made !!!"
	Write-Host
	pause
	Invoke-Item $outDirCsv
	Invoke-Item $outDirJson
}catch{
    Write-Error $_.Exception.ToString()
    Read-Host -Prompt "The above error occurred. Press Enter to exit."
	pause
}

## processCSVfromLibreOfficeToToggleTab.ps1
<#
Note:
This script is just a string to string manipulation.
I want to take csv's generated by a LibreOffice Calc macro and keep only the usable field columns for MySql imports. (no csv header)
Then I want to convert that csv into a Json format used in my app (int numbers are not quoted)
I also want to perform directory management
#>

$PSScriptRoot = Split-Path -Parent -Path $MyInvocation.MyCommand.Definition
Write-Host "Directory of the current file = "$PSScriptRoot
Write-Host

try{
	$dirMyRoot = "D:\github\Toggle-Tag\"
	$dirCsvFromLibreOffice = $dirMyRoot+"csvfromLibreOffice\"
	$outDirCsv = $dirMyRoot+"csvForMySql\"
	$outDirCsvNoHeaders = $dirMyRoot+"csvForMySqlNoHeader\"
	$outDirJson = $dirMyRoot+"jsonForMySql\"
	$outDirJsonNoQuotes = $dirMyRoot+"jsonForMySqlNoQuotes\"
	$tempTestLocation = "D:\github\Toggle-Tag\taffydb-testbench\app-json"

	Write-Host "********"
	# Check for a root Dir
	Write-Host "Checking root dir ..."$dirCsvFromLibreOffice
	If(!(test-path $dirCsvFromLibreOffice)){
		Write-Host $dirCsvFromLibreOffice+" does not exist. You need a root folder like: "+$dirMyRoot+" and a csv source folder like: "+$dirCsvFromLibreOffice
		Write-Host
		Write-Host "Terminating script ..."
		Write-Host
		pause
		$host.Exit()
	}Else{
		Write-Host $dirCsvFromLibreOffice" exists"
		Write-Host
	}

	# Make Csv Dir if Dir does not exist
	# with headers
	Write-Host "Checking Csv destination dir ..."$outDirCsv
	If(!(test-path $outDirCsv)){
		New-Item -ItemType Directory -Force -Path $outDirCsv
		Write-Host "Created Dir: "$outDirCsv
		Write-Host
	}Else{
		Write-Host "Csv's, with headers, will go in : "$outDirCsv
		Write-Host
	}
	# csv without headers
	Write-Host "Checking Csv destination dir ..."$outDirCsvNoHeaders
	If(!(test-path $outDirCsvNoHeaders)){
		New-Item -ItemType Directory -Force -Path $outDirCsvNoHeaders
		Write-Host "Created Dir: "$outDirCsvNoHeaders
		Write-Host
	}Else{
		Write-Host "Csv's, without headers, will go in : "$outDirCsvNoHeaders
		Write-Host
	}

	# Make Json Dir if Dir does not exist
	# json with quotes
	Write-Host "Checking Json destination dir ..."$outDirJson
	If(!(test-path $outDirJson)){
		New-Item -ItemType Directory -Force -Path $outDirJson
		Write-Host "Created Dir: "$outDirJson
		Write-Host
	}Else{
		Write-Host "Json's 'with quotes' will go in: "$outDirJson
		Write-Host
	}
	# json with out quotes
	Write-Host "Checking Json destination dir ..."$outDirJsonNoQuotes
	If(!(test-path $outDirJsonNoQuotes)){
		New-Item -ItemType Directory -Force -Path $outDirJsonNoQuotes
		Write-Host "Created Dir: "$outDirJsonNoQuotes
		Write-Host
	}Else{
		Write-Host "Json's 'without quotes' will go in: "$outDirJsonNoQuotes
		Write-Host
	}

	# make an array of file names contained in the source dir
	$dirChildrenNames = Get-ChildItem -Path $dirCsvFromLibreOffice

	Write-Host "	********"
	# Populate CSV and JSON directories
	Write-Host
	Write-Host ">>> Performing csv to json process..."
	Write-Host
	foreach ($item in $dirChildrenNames){
		Write-Host "--------"
		$filePathIn = $dirCsvFromLibreOffice+$item
		$filePathOutCsv = $outDirCsv+$item
		$filePathOutCsvNoHeader = $outDirCsvNoHeaders+$item

		Write-Host ">> Input CSV source: "$filePathIn
		Write-Host

		#Delete Csv with quotes and headers file if exists
		If (Test-Path $filePathOutCsv){
			Remove-Item $filePathOutCsv
			Write-Host "   Removed pre-existing Csv, with HEADERS and 'quotes' :"$filePathOutCsv
		}

		# remove UI testing preview cols from LibreOffice generated csv's
		Import-Csv $filePathIn -Delimiter "," | Select * -ExcludeProperty fk_preview1,fk_preview2,fk_preview3,fk_preview4 | Export-Csv $filePathOutCsv -Delimiter "," -NoTypeInformation

		#Delete Csv without headers file, if exists
		If (Test-Path $filePathOutCsvNoHeader){
			Remove-Item $filePathOutCsvNoHeader
			Write-Host "   Removed pre-existing Csv, without HEADERS :"$filePathOutCsvNoHeader
		}

		(Get-Content $filePathOutCsv) | Select-Object -skip 1 | Out-File $filePathOutCsvNoHeader -force

		Write-Host
		Write-Host "	CSV file: "$item
		Write-Host
		Write-Host "	>> Output CSV 'with headers' path: "$filePathOutCsv
		Write-Host "	>> Output CSV 'without headers' path: "$filePathOutCsvNoHeader
		#Write-Host "	>> Output CSV 'without quotes' path: "$filePathOutCsvNoQuotes
		Write-Host

		$replaceString = $item -replace '.csv','.json'

		$filePathOutJson = $outDirJson+$replaceString

		#Delete Json with quotes file if exists
		If (Test-Path $filePathOutJson){
			Remove-Item $filePathOutJson
			Write-Host "   Removed pre-existing Json 'with quotes' :"$filePathOutJson
			Write-Host
		}

		Write-Host "	Json file: "$replaceString
		Write-Host

		# Maje a new Json file with quotes from the new Csv
		Import-Csv $filePathOutCsv -Delimiter "," | ConvertTo-Json -Compress | Add-Content -Path $filePathOutJson
		Write-Host "	>> Output JSON, 'with quotes', path: "$filePathOutJson

		$filePathOutJsonNoQuote = $outDirJsonNoQuotes+$replaceString

		#Delete Json with quotes file if exists
		If (Test-Path $filePathOutJsonNoQuote){
			Remove-Item $filePathOutJsonNoQuote
			Write-Host "   Removed pre-existing Json 'without quotes' :"$filePathOutJsonNoQuote
			Write-Host
		}

		Write-Host "in file for NO Quote: "$filePathOutCsvNoQuotes" line 183"
		# Maje a new Json file without quotes from the new Csv without quotes
		import-csv $filePathOutCsv | ConvertTo-Json -Compress | Foreach {$_ -creplace '"NULL"','null' -replace ':"([0-9]+)"',':$1'} | Out-File $filePathOutJsonNoQuote
		Write-Host "	>> Output JSON, 'without quotes', path: "$filePathOutJsonNoQuote

		# ------------ testbench -------------------------------------
		#Delete Json file if exists
		If (Test-Path $tempTestLocation$replaceString){
			Remove-Item $tempTestLocation$replaceString
			Write-Host "Removed pre-existing Json (for testbench) :"$filePathOutJsonNoQuote
			Write-Host
		}

		Copy-Item $filePathOutJsonNoQuote -Destination $tempTestLocation
		Write-Host "	>> Copied JSON 'without quotes Testbench' to path: "$tempTestLocation

		Write-Host "	--------"
		Write-Host
	}
	Write-Host
	Write-Host "DONE."
	Write-Host
	Write-Host "!!! Process complete. There will now be 2 popup windows to verify your files were made !!!"
	Write-Host
	pause
	Invoke-Item $outDirCsv
	Invoke-Item $outDirJson
}catch{
    Write-Error $_.Exception.ToString()
    Read-Host -Prompt "The above error occurred. Press Enter to exit."
	pause
}

## searchDirCovert.bat
for /f "delims=|" %%f in ('dir /b .\ODSDIRECTORY') do "C:\Program Files (x86)\LibreOffice 4\program\soffice.exe" --headless --convert-to csv:"Text - txt - csv (StarCalc)":"59,ANSI,1" --outdir .\CSV ".\ODSDIRECTORY\%%f"
exit

rem 'if you want to merge all CSV into one CSV'
rem copy .\CSV\*.csv merged.csv
rem https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=75404
	#!/bin/bash
	# CSV to JSON converter using BASH
	# inspired from http://blog.secaserver.com/2013/12/convert-csv-json-bash/
	# Usage bash csv2json.sh myScvScript.csv > jsonOutputScript.json


	input=$1

	[ -z $1 ] && echo "No CSV input file specified" && exit 1
	[ ! -e $input ] && echo "Unable to locate $1" && exit 1

	read first_line < $input
	a=0
	headings=`echo $first_line \| awk -F, {'print NF'}`
	lines=`cat $input \| wc -l`
	while [ $a -lt $headings ]
	do
	head_array[$a]=$(echo $first_line \| awk -v x=$(($a + 1)) -F"," '{print $x}')
	a=$(($a+1))
	done

	c=0
	echo "{"
	while [ $c -lt $lines ]
	do
	read each_line
	if [ $c -ne 0 ]; then

	d=0
	echo -n "{"
	while [ $d -lt $headings ]
	do
	# non zero value indicates a match was found
	fk_preview1=$(echo ${head_array[$d]} \| grep -o "fk_preview1" \| wc -w)
	fk_preview2=$(echo ${head_array[$d]} \| grep -o "fk_preview2" \| wc -w)
	fk_preview3=$(echo ${head_array[$d]} \| grep -o "fk_preview3" \| wc -w)
	fk_preview4=$(echo ${head_array[$d]} \| grep -o "fk_preview4" \| wc -w)
	fk_preview0=$(($fk_preview1+$fk_preview2+$fk_preview3+$fk_preview4))
	# echo $fk_preview0

	each_element=$(echo $each_line \| awk -v y=$(($d + 1)) -F"," '{print $y}')

	# remove quotes
	each_element=${each_element//\"}

	if ! [[ $each_element =~ ^-?[0-9]+$ ]]; then
	# wrap quotes if not an int
	each_element=\"$each_element\"
	fi

	if [ $fk_preview0 -eq 0 ]; then
	if [ $d -ne $(($headings-1)) ]; then
	nextField=${head_array[$(($d+1))]}
	case $nextField in "fk_preview1"\|"fk_preview2"\|"fk_preview3"\|"fk_preview4")
	echo -n \"${head_array[$d]}\":$each_element

	;;
	*)
	echo -n \"${head_array[$d]}\":$each_element
	;;
	esac
	fi
	if [ $d -ne $(($headings-2)) ]; then
	echo ","
	fi

	fi

	d=$(($d+1))
	done
	if [ $c -eq $(($lines-1)) ]; then
	echo "}"
	else
	echo "},"
	fi
	fi
	c=$(($c+1))
	done < $input
	echo "}"
	<#
	Note:
	This script is just string to string. The ID col will be a string instead of an int.
	If i made all the field names for the ID Col the same name, ex:'id', i could then just have Powershell covert that col to int with ' -replace ':"([0-9]+)"',':$1'} '

	here was a clue: https://stackoverflow.com/a/45964102/5861315

	Powershelll Notes:

	powershell -ExecutionPolicy ByPass -File D:\github\Toggle-Tag\PowerShell\cleanmyscv.ps1
	https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/import-csv?view=powershell-6
	https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/export-csv?view=powershell-6
	https://blogs.technet.microsoft.com/heyscriptingguy/2011/10/17/easily-remove-columns-from-a-csv-file-by-using-powershell/
	https://stackoverflow.com/a/21905177/5861315
	https://blogs.technet.microsoft.com/heyscriptingguy/2011/11/02/remove-unwanted-quotation-marks-from-csv-files-by-using-powershell/
	#>


	try{
	$dirMyRoot = "D:\github\Toggle-Tag\"
	$dirCsvFromLibreOffice = $dirMyRoot+"csvfromLibreOffice\"
	$outDirCsv = $dirMyRoot+"csvforMySqlWorkbench\"
	$outDirJson = $dirMyRoot+"jsonforMySqlWorkbench\"

	# Check for a root Dir
	If(!(test-path $dirCsvFromLibreOffice)){
	Write-Host $dirCsvFromLibreOffice+" does not exist. You need a root folder like: "+$dirMyRoot+" and a csv source folder like: "+$dirCsvFromLibreOffice
	Write-Host "Terminating script ..."
	pause
	$host.Exit()
	}Else{
	Write-Host $dirCsvFromLibreOffice" exists"
	}

	# Make Csv Dir if Dir does not exist
	If(!(test-path $outDirCsv)){
	New-Item -ItemType Directory -Force -Path $outDirCsv
	Write-Host "Created Dir: "$outDirCsv
	}Else{
	Write-Host "Csv's will go in : "$outDirCsv
	}

	# Make Json Dir if Dir does not exist
	If(!(test-path $outDirJson)){
	New-Item -ItemType Directory -Force -Path $outDirJson
	Write-Host "Created Dir: "$outDirJson
	}Else{
	Write-Host "Json's will go in: "$outDirJson
	}

	# make an array of file names contained in the source dir
	$dirChildrenNames = Get-ChildItem -Path $dirCsvFromLibreOffice

	# Populate CSV and JSON directories
	foreach ($item in $dirChildrenNames){
	Write-Host " --------------------------------------------------------------------------------- "
	$filePathIn = $dirCsvFromLibreOffice+$item
	$filePathOutCsv = $outDirCsv+$item

	#Delete Csv file if exists
	If (Test-Path $filePathOutCsv){
	Remove-Item $filePathOutCsv
	}

	# remove UI testing preview cols from LibreOffice generated csv's
	Import-Csv $filePathIn -Delimiter "," \| Select * -ExcludeProperty fk_preview1,fk_preview2,fk_preview3,fk_preview4 \| Export-Csv $filePathOutCsv -Delimiter "," -NoTypeInformation

	Write-Host "Destination: "$filePathOutCsv
	Write-Host " "$item

	$replaceString = $item -replace '.csv','.json'
	$filePathOutJson = $outDirJson+$replaceString

	#Delete Json file if exists
	If (Test-Path $filePathOutJson){
	Remove-Item $filePathOutJson
	}

	# Maje a new Json file from the new Csv
	Import-Csv $filePathOutCsv -Delimiter "," \| ConvertTo-Json -Compress \| Add-Content -Path $filePathOutJson
	Write-Host "Destination: "$filePathOutJson
	Write-Host " "$replaceString
	Write-Host " --------------------------------------------------------------------------------- "
	}
	Write-Host
	Write-Host
	Write-Host
	Write-Host "!!! Process complete. There will now be 2 popup windows to verify your files were made !!!"
	pause
	Invoke-Item $outDirCsv
	Invoke-Item $outDirJson
	}catch{
	Write-Error $_.Exception.ToString()
	Read-Host -Prompt "The above error occurred. Press Enter to exit."
	pause
	}
	Sub convertSheetsToCSVs
	'Dim fileProps(0) as new com.sun.star.beans.PropertyValue
	Dim fileProps(1) as new com.sun.star.beans.PropertyValue
	sheets = ThisComponent.Sheets

	' the UI for the destination path
	NumSheets = sheets.Count - 1 ' last sheet is the macro sheet
	xSheet = ThisComponent.sheets(NumSheets)
	oCell = xSheet.getCellByPosition(1,0) ' $B$1 ' file:///D:/github/Toggle-Tag/csvExports/
	destinationPath = oCell.string

	fileProps(0).Name = "FilterName"
	fileProps(0).Value = "Text - txt - csv (StarCalc)"

	' ASCII Codes http://www.theasciicode.com.ar/ascii-printable-characters/semicolon-ascii-code-59.html
	fileProps(1).Name = "FilterOptions"
	' https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options#Token_8.2C_csv_export
	fileProps(1).Value ="44,34,11" '44=comma 34=doublequotes, 11=ASCII/US (Western)

	i = 0

	Do While sheets.Count > i
	sheet = sheets.getByIndex(i)
	cntrllr = ThisComponent.CurrentController
	cntrllr.setActiveSheet(sheet)
	sURL = destinationPath & sheets.ElementNames(i) & ".csv"

	ThisComponent.storeToURL(sURL, fileProps())
	i = i + 1
	Loop

	msgbox "CSV Export complete! Check in the [" + destinationPath + "] dir to view your files"
	End Sub
	#!/bin/bash

	# CSV to JSON converter using BASH
	# requires csv2json.sh file

	relativeInputRootDir=`ls ../csvForMySql/*.csv`
	relativeOutputDir="../jsonForMySqlNoQuotes/"

	for eachfile in $relativeInputRootDir
	do
	echo $relativePath
	relativePath=$eachfile
	csvBaseName=$(basename ${relativePath} .${relativePath##*.})
	echo $csvBaseName

	outputFile=$relativeOutputDir$csvBaseName.json


	echo $outputFile

	bash csv2json2.sh $relativePath > $outputFile

	done
	Sub ExportAllToCsv
	document = ThisComponent

	' Use the global string tools library to generate a base filename for each CSV
	' based on the current prefixless filename
	GlobalScope.BasicLibraries.loadLibrary("Tools")
	BaseFilename = Tools.Strings.GetFileNameWithoutExtension(document.getURL(), "/")

	' Work out number of sheets for looping over them later.
	Sheets = document.Sheets
	NumSheets = Sheets.Count - 1

	' Set up a propval object to store the filter properties
	Dim Propval(1) as New com.sun.star.beans.PropertyValue
	Propval(0).Name = "FilterName"
	Propval(0).Value = "Text - txt - csv (StarCalc)"
	Propval(1).Name = "FilterOptions"
	Propval(1).Value ="59,34,0,1,1" 'ASCII 59 = ; 34 = "

	For I = 0 to NumSheets
	' For each sheet, assemble a filename and save using the filter
	SheetName = Sheets(I).Name
	document.getCurrentController.setActiveSheet(Sheets(I))
	Filename = "/tmp/" + BaseFilename + "." + SheetName + ".csv"
	FileURL = convertToURL(Filename)
	document.StoreAsURL(FileURL, Propval())
	Next I

	' Libreoffice thinks our filename is now the last-exported-CSV filename, so close.
	Msgbox "Files saved as /tmp/" + BaseFilename + ".*.csv. You'll need to close the spreadsheet now."
	End Sub
	' http://www.jpstacey.info/blog/2014-09-12/exporting-all-libreoffice-worksheets-csv
	# powershell -ExecutionPolicy ByPass -File D:\github\Toggle-Tag\PowerShell\cleanmyscv.ps1
	# https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/import-csv?view=powershell-6
	# https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/export-csv?view=powershell-6
	# https://blogs.technet.microsoft.com/heyscriptingguy/2011/10/17/easily-remove-columns-from-a-csv-file-by-using-powershell/
	try
	{
	$dirPathString = "D:\github\Toggle-Tag\csvfromLibreOffice\"
	$outDir = "D:\github\Toggle-Tag\csvforMySqlWorkbench\"


	# make an array of file names contained in the dir
	$dirChildrenNames = Get-ChildItem -Path $dirPathString

	foreach ($item in $dirChildrenNames)
	{
	$filePathIn = $dirPathString+$item
	$filePathOut = $outDir+$item

	Import-Csv $filePathIn -Delimiter "," \| Select * -ExcludeProperty fk_preview1,fk_preview2,fk_preview3,fk_preview4 \| Export-Csv $filePathOut -Delimiter "," -NoTypeInformation

	Write-Host $filePathOut
	}
	pause
	}
	catch
	{
	Write-Error $_.Exception.ToString()
	Read-Host -Prompt "The above error occurred. Press Enter to exit."
	pause
	}
	#!/bin/bash
	# TAB CSV to JSON converter using BASH
	# Usage bash csv2json.sh myScvScript.csv > jsonOutputScript.json
	# this script is called by another .sh named csv2jsonDir.sh

	input=$1

	[ -z $1 ] && echo "No CSV input file specified" && exit 1
	[ ! -e $input ] && echo "Unable to locate $1" && exit 1

	read first_line < $input
	noRecords=`cat $input \| wc -l`
	noFields=`echo -e $first_line \| awk {'print NF'}`
	declare -a fieldArr
	fieldArr=()

	echo "{"
	while IFS=$'\t' read -r -a csvRecordsArray
	do
	fieldNo=0
	skipFieldCounter=1

	if [[ $recordNo -ne 0 ]]; then echo -n "{"; fi
	while [[ $fieldNo -lt $noFields ]]
	do
	if [[ $recordNo -ne 0 ]]; then
	exemptVar=${fieldArr[$fieldNo]}
	case "$exemptVar" in "fk_preview1"\|"fk_preview2"\|"fk_preview3"\|"fk_preview4")
	# skip field
	skipFieldCounter=$(($skipFieldCounter + 1))
	;;
	*)
	quotedVal=${csvRecordsArray[$fieldNo]}
	if ! [[ $quotedVal =~ ^-?[0-9]+$ ]]; then
	# wrap quotes if not an int
	echo -n \"${fieldArr[$fieldNo]}\":\"$quotedVal\"
	else
	echo -n \"${fieldArr[$fieldNo]}\":$quotedVal
	fi
	if [[ $fieldNo -lt $(($noFields - $skipFieldCounter)) ]]; then echo -n ","; fi
	;;
	esac


	else
	# last delineated obj, no comma
	fieldArr=("${fieldArr[@]}" ${csvRecordsArray[$fieldNo]})
	fi
	fieldNo=$(($fieldNo+1))
	done
	if [[ $recordNo -gt 0 ]]; then echo "}"; fi

	recordNo=$(($recordNo+1))

	if [[ $recordNo -lt $noRecords ]] && [[ $recordNo -gt 1 ]]; then echo -n ","; fi
	done < $input
	echo "}"
	REM *** BASIC ***

	Sub Main

	End Sub

	Sub convertSheetsToCSVs
	' This macro cycles though every sheet in the Cacl Doc and converts it to a CSV file.
	' The files are sent to a user defined directory path featured on a sheet neamed macros
	' For my convenience, Windows exports Comma Csv, Linus exports Tab Csv

	Dim fileProps(1) as new com.sun.star.beans.PropertyValue 'property array'
	Dim relativeDir(1) as new com.sun.star.beans.PropertyValue
	relativeDir(0).Value = "file://../Shell-Scripts"
	relativeDir(1).Value = GetCurrentFolder() & "/csvFromLibreOffice/"

	sheets = ThisComponent.Sheets

	NumSheets = sheets.Count - 1 ' last sheet is the macro sheet
	xSheet = ThisComponent.sheets(NumSheets)
	oCell = xSheet.getCellByPosition(1,0) ' $B$1 ' file:///LibreOffice-Calc/csvfromLibreOffice
	'destinationPath = oCell.string
	destinationPath = relativeDir(1).Value

	fileProps(0).Name = "FilterName"
	fileProps(0).Value = "Text - txt - csv (StarCalc)"

	' Filter Options:
	' https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options#Token_8.2C_csv_export
	fileProps(1).Name = "FilterOptions"

	' ASCII Codes:
	' http://www.theasciicode.com.ar/ascii-printable-characters/semicolon-ascii-code-59.html


	If GetGUIType() = 1 Then
	'Windows comma csv output
	fileProps(1).Value ="44,34,11" '44=comma 34=doublequotes, 11=ASCII/US (Western)
	ElseIf GetGUIType() = 4 Then
	'Linux tabs csv output
	fileProps(1).Value ="09,34,11" '44=tab 34=doublequotes, 11=ASCII/US (Western)
	ElseIf GetGUIType() = 3 Then
	'Mac tabs csv output
	fileProps(1).Value ="09,34,11" '44=tab 34=doublequotes, 11=ASCII/US (Western)
	End If


	i = 0

	Do While sheets.Count > i
	sheet = sheets.getByIndex(i)
	cntrllr = ThisComponent.CurrentController
	cntrllr.setActiveSheet(sheet)
	sURL = destinationPath & sheets.ElementNames(i) & ".csv"

	ThisComponent.storeToURL(sURL, fileProps())
	i = i + 1
	Loop

	msgbox "CSV Export complete! Check within the [" + destinationPath + "] dir to view your files you should now run the PowerShell or BASH script hosted within D:/github/Toggle-Tag/Shell-Scripts"

	DisplayGUIType(relativeDir(0).Value, relativeDir(1).Value)

	End Sub

	Function GetCurrentFolder
	sUrl = ThisComponent.getURL()
	sParts = Split(sUrl, "/")
	ReDim Preserve sParts(0 to UBound(sParts) - 1)
	GetCurrentFolder = Join(sParts, "/")
	End Function

	Sub DisplayGUIType(dir1 As String, dir2 As String)
	Dim s As String

	thisComponentUrl = ThisComponent.getURL()
	sParts = Split(thisComponentUrl, "/")
	ReDim Preserve sParts(0 to UBound(sParts) - 2)
	joinPath = Join(sParts, "/")


	Select Case GetGUIType()
	Case 1
	s = "Windows"
	joinPath = joinPath & "/Shell-Scripts/PowerShell"
	Shell "explorer.exe" & " " & joinPath, vbNormalFocus ' Windows Explorer

	Case 4
	s = "UNIX"
	joinPath = joinPath & "/Shell-Scripts/BASH"

	On Error Goto MacShell
	' Linux is my primary UNIX
	Shell ("nemo " & joinPath) ' Linux Mint Nemo
	Resume ProceedFileManager

	MacShell:
	' Mac is my rarely used UNIX
	Shell ("open " & joinPath) ' Mac OS Finder
	Resume ProceedFileManager

	ProceedFileManager:
	' i cant run shell directly from within LibreOffice
	' Shell ("sh " & joinPath & " /csv2jsonDir.sh")

	Case Else
	s = "Unknown value " & CStr(GetGUIType()) & CHR$(10) & "Probably running in Client/Server mode"

	End Select
	MsgBox "GUI type is " & s, 0, "GetGUIType()"

	End Sub ' The value -1 is returned if the type is not known
	<#
	Note:
	This script is just a string to string manipulation.
	I want to take csv's generated by a LibreOffice Calc macro and keep only the usable field columns for MySql imports. (no csv header)
	Then I want to convert that csv into a Json format used in my app (int numbers are not quoted)
	I also want to perform directory management
	#>

	$PSScriptRoot = Split-Path -Parent -Path $MyInvocation.MyCommand.Definition
	Write-Host "Directory of the current file = "$PSScriptRoot
	Write-Host

	try{
	$dirMyRoot = "D:\github\Toggle-Tag\"
	$dirCsvFromLibreOffice = $dirMyRoot+"csvfromLibreOffice\"
	$outDirCsv = $dirMyRoot+"csvForMySql\"
	$outDirJson = $dirMyRoot+"jsonForMySql\"
	$outDirJsonNoQuotes = $dirMyRoot+"jsonForMySqlNoQuotes\"
	$tempTestLocation = "D:\github\Toggle-Tag\taffydb-testbench\app-json"

	Write-Host "********"
	# Check for a root Dir
	Write-Host "Checking root dir ..."$dirCsvFromLibreOffice
	If(!(test-path $dirCsvFromLibreOffice)){
	Write-Host $dirCsvFromLibreOffice+" does not exist. You need a root folder like: "+$dirMyRoot+" and a csv source folder like: "+$dirCsvFromLibreOffice
	Write-Host
	Write-Host "Terminating script ..."
	Write-Host
	pause
	$host.Exit()
	}Else{
	Write-Host $dirCsvFromLibreOffice" exists"
	Write-Host
	}

	# Make Csv Dir if Dir does not exist
	# with headers
	Write-Host "Checking Csv destination dir ..."$outDirCsv
	If(!(test-path $outDirCsv)){
	New-Item -ItemType Directory -Force -Path $outDirCsv
	Write-Host "Created Dir: "$outDirCsv
	Write-Host
	}Else{
	Write-Host "Csv's, with headers, will go in : "$outDirCsv
	Write-Host
	}

	# Make Json Dir if Dir does not exist
	# json with quotes
	Write-Host "Checking Json destination dir ..."$outDirJson
	If(!(test-path $outDirJson)){
	New-Item -ItemType Directory -Force -Path $outDirJson
	Write-Host "Created Dir: "$outDirJson
	Write-Host
	}Else{
	Write-Host "Json's 'with quotes' will go in: "$outDirJson
	Write-Host
	}
	# json with out quotes
	Write-Host "Checking Json destination dir ..."$outDirJsonNoQuotes
	If(!(test-path $outDirJsonNoQuotes)){
	New-Item -ItemType Directory -Force -Path $outDirJsonNoQuotes
	Write-Host "Created Dir: "$outDirJsonNoQuotes
	Write-Host
	}Else{
	Write-Host "Json's 'without quotes' will go in: "$outDirJsonNoQuotes
	Write-Host
	}

	# make an array of file names contained in the source dir
	$dirChildrenNames = Get-ChildItem -Path $dirCsvFromLibreOffice

	Write-Host " ********"
	# Populate CSV and JSON directories
	Write-Host
	Write-Host ">>> Performing csv to json process..."
	Write-Host
	foreach ($item in $dirChildrenNames){
	Write-Host "--------"
	$filePathIn = $dirCsvFromLibreOffice+$item
	$filePathOutCsv = $outDirCsv+$item

	Write-Host ">> Input CSV source: "$filePathIn
	Write-Host

	#Delete Csv with quotes and headers file if exists
	If (Test-Path $filePathOutCsv){
	Remove-Item $filePathOutCsv
	Write-Host " Removed pre-existing Csv, with HEADERS and 'quotes' :"$filePathOutCsv
	}

	# remove UI testing preview cols from LibreOffice generated csv's
	Import-Csv $filePathIn -Delimiter "," \| Select * -ExcludeProperty fk_preview1,fk_preview2,fk_preview3,fk_preview4 \| Export-Csv $filePathOutCsv -Delimiter "," -NoTypeInformation

	Write-Host
	Write-Host " CSV file: "$item
	Write-Host
	Write-Host " >> Output CSV 'with headers' path: "$filePathOutCsv
	Write-Host

	$replaceString = $item -replace '.csv','.json'

	$filePathOutJson = $outDirJson+$replaceString

	#Delete Json with quotes file if exists
	If (Test-Path $filePathOutJson){
	Remove-Item $filePathOutJson
	Write-Host " Removed pre-existing Json 'with quotes' :"$filePathOutJson
	Write-Host
	}

	Write-Host " Json file: "$replaceString
	Write-Host

	# Maje a new Json file with quotes from the new Csv
	Import-Csv $filePathOutCsv -Delimiter "," \| ConvertTo-Json -Compress \| Add-Content -Path $filePathOutJson
	Write-Host " >> Output JSON, 'with quotes', path: "$filePathOutJson

	$filePathOutJsonNoQuote = $outDirJsonNoQuotes+$replaceString

	#Delete Json with quotes file if exists
	If (Test-Path $filePathOutJsonNoQuote){
	Remove-Item $filePathOutJsonNoQuote
	Write-Host " Removed pre-existing Json 'without quotes' :"$filePathOutJsonNoQuote
	Write-Host
	}

	Write-Host "in file for NO Quote: "$filePathOutCsvNoQuotes" line 183"
	# Maje a new Json file without quotes from the new Csv without quotes
	import-csv $filePathOutCsv \| ConvertTo-Json -Compress \| Foreach {$_ -replace ':"([0-9]+)"',':$1'} \| Out-File $filePathOutJsonNoQuote
	Write-Host " >> Output JSON, 'without quotes', path: "$filePathOutJsonNoQuote

	# ------------ testbench -------------------------------------
	#Delete Json file if exists
	If (Test-Path $tempTestLocation$replaceString){
	Remove-Item $tempTestLocation$replaceString
	Write-Host "Removed pre-existing Json (for testbench) :"$filePathOutJsonNoQuote
	Write-Host
	}

	Copy-Item $filePathOutJsonNoQuote -Destination $tempTestLocation
	Write-Host " >> Copied JSON 'without quotes Testbench' to path: "$tempTestLocation

	Write-Host " --------"
	Write-Host
	}
	Write-Host
	Write-Host "DONE."
	Write-Host
	Write-Host "!!! Process complete. There will now be 2 popup windows to verify your files were made !!!"
	Write-Host
	pause
	Invoke-Item $outDirCsv
	Invoke-Item $outDirJson
	}catch{
	Write-Error $_.Exception.ToString()
	Read-Host -Prompt "The above error occurred. Press Enter to exit."
	pause
	}
	for /f "delims=\|" %%f in ('dir /b .\ODSDIRECTORY') do "C:\Program Files (x86)\LibreOffice 4\program\soffice.exe" --headless --convert-to csv:"Text - txt - csv (StarCalc)":"59,ANSI,1" --outdir .\CSV ".\ODSDIRECTORY\%%f"
	exit

	rem 'if you want to merge all CSV into one CSV'
	rem copy .\CSV\*.csv merged.csv
	rem https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=75404