PowerShell Script : Convert UTF-8 text file into UTF-16 Unicode encoding file , salesforce dataloader export csv to UTF-16 Unicode SFDC 原生編碼字元轉換補遺

The native dataloader from Salesforce only export .csv file in UTF-8 encoding . And it is asked to exchange information with Therefore™ information management application which uses UTF-16 encoding data .

The pros and cons of UTF-8 and UTF-16 (Unicode) encoding can be found in here.
Storage and indexing speed etc. matters. 

The most easy way to do this is using powershell script to encode the .csv from Salesforce dataloader export to Unicode in mainly one line .PS script. Schedule this script into window task scheduler , done.

  

Get-content -encoding UTF8 $filename | Set-Content -encoding Unicode "$new_filename"

Script below shows how to convert multiple UTF-8 files in folder into UTF-16 files with error log tracking. 

Both UTF-8 and UTF-16 files will be available in the folder after conversion.
UTF-8 abc.csv will be cloned , and a UTF-16 abc_utf16.csv will be created in the same folder.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

#Continue conversion even if one file failed , just log down the error. $ErrorActionPreference="SilentlyContinue" Stop-Transcript | out-null $ErrorActionPreference = "Continue" #-path of the log file Start-Transcript -path "C:\ABCProject\log\utfLog.txt" -append try { #double bracket for path with space #get all csv files inside that folder get-childitem "C:\ABCProject\write\*.csv" | foreach-object { $name = $_.FullName; write-host "The file name" $name; $fname = $name.replace(".csv","_utf16.csv"); write-host "The new file" $fname; get-content -encoding UTF8 $name | Set-Content -encoding Unicode "$fname" } Stop-Transcript exit } catch { #log -message "Get-WmiObject cmdlet failed" -type "Error" write-host "Error found " $_.Exception.Message.ToString() -type "Error" }

Copy files based on file list document 按照文件清單複製檔案 抄送

 Consider of a scenario, partner's SFTP server is down for an hour . A lots of files failed to upload to them.  After comparing the file list of two company, a missing file list text file has been created.
Maybe 100 out of 1000 files are missing.

Hence, you are going to resend those missing files based on the file list.
Batch script below help to do the job in an easy way.

You may just copy all missing files to a new folder, then resend.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

@ECHO ON SET FileList=C:\Users\XXX\MissingFiles.txt ::The folder that contains the files SET Source=X:\AllFiles ::The folder that the files should be copied to SET Destination=Y:\ToBeResend FOR /F "USEBACKQ TOKENS=*" %%F IN ("%FileList%") DO XCOPY /F /Y "%Source%\%%~F" "%Destination%\" GOTO :EOF

Batch script commands to split big text file in windows with efficiency comparison 命令提示字元 分割打開超大型檔案

When we try to open the big file (e.g. : server log), we may meet an error when we open with text editor.

Notepad : File is too large for Notepad .

Notepad++ : File is too big to be opened .

At this point, the only way is try to split the file using program. Surely it is always good to use those "close to metal" language like C++ . However , you may not want to install compiler , SDK etc.

Is there any convenient way ? Yes ,using batch script is always a solution .
But always remember , it may takes you more than half hour to split 1GB file into 100 small files.

Code in Batch .bat script (Method 1 Faster)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

@echo off setLocal EnableDelayedExpansion set limit=50000 #Rows per file REM can be in any extension (e.g. csv ) , as long as it is a text file set file=YourFileName.txt set lineCounter=1 set filenameCounter=1 set name= set extension= for %%a in (%file%) do ( set "name=%%~na" set "extension=%%~xa" ) for /f "tokens=*" %%a in (%file%) do ( if !lineCounter! gtr !limit! ( set /a filenameCounter=!filenameCounter! + 1 set lineCounter=1 echo Created !splitFile!. ) REM Output filename pattern YourFileName-part1.csv , YourFileName-part2.csv set splitFile=!name!-part!filenameCounter!!extension! echo %%a>> !splitFile! set /a lineCounter=!lineCounter! + 1 )

Code in Batch .bat script (Method 2 Slower)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

@echo off setlocal enableextensions disabledelayedexpansion set STARTTIME=%TIME% set "nLines=50000" #Rows in each file set "line=0" REM can be in any extension (e.g. csv ) , as long as it is a text file for /f "usebackq delims=" %%a in ("InputFileName.txt") do ( set /a "file=line/%nLines%", "line+=1" setlocal enabledelayedexpansion for %%b in (!file!) do ( endlocal >>"OutputName_%%b.txt" echo %%a REM Ouput filename pattern : OutputName_1.txt , OutputName_2.txt          REM Filename prefix will NOT follow the input file in this way. ) )

• Efficiency Comparison 

            The way of writing the script could lead to double process time.

• Test with 100K rows , >18 MB file.

           For example : Split a file 18.7MB into 3 files (each file with max 31670 rows). 

           Using method 1 one takes 23s , while using method 2 takes 46s.

• Test with 5000K rows , >1 GB file

           With method 1 , if a 1.17GB (1230188 KB , around 5000Krows inside) has to be split into 100                         small files (50K rows @file) , it takes 30m42s .

            With method 2, believer me, you don't want to try.

• PC configuration reference 

           16GB RAM , with 8 x QuaCores , i5 CPU.  

Conclusion

- Better use C++ to split file over 1GB . ;)

 

Batch script commands to combine text(.csv) files 使用命令提示字元合併多個文字檔案

Code Preview

copy *.csv all.csv #For flatten file structure 單一資料夾檔案合併

for /R %f in (*.txt) do type "%f" >> all.txt #For merging sub-folder files 多層資料夾檔案合併

There are lots of use cases that we have to handle numbers of fragment files . One of the the common operation is to combine text files into a single file. You may find section below helpful if you have to merge multiple text files. 

Scenario 

Case A : Combine csv data feed into single Excel sheet for filtering and analysis.

Case B : Collect all intranet system log for investigation .

The task is easy but can be time consuming , and the task actually can be done using command line within 3s. There are mainly two ways to combine text files in command line.

Method 1 
The simple way is using copy command for NO sub-directory file case   

Flatten file structure

The command 

copy *.csv all.csv

Result 

The Command above mainly choose the files to merge in the folder by selecting file extension .

It tells window to merge all .csv file in folder "C:/Users/Local User/Documents" into a file named as "all.csv".

Alternatively, you may also use the wildcard symbol * in file name like below :

The command  

copy b*.csv b_all.csv

Result 

The command merge .csv files with prefix letter "b" in filename into file named as "b_all.csv".

Method 2
The customized way is using for /R to handle recursive selection in sub-directory   

There are certain text log files in the sub-folders , those files need to be combined as well. 

Hence the folder has to be searched recursively in order to find out all the files inside sub-folders.

The command 

for /R %f in (*.txt) do type "%f" >> allLog.txt

Result 

All .txt file inside the folder "C:/Users/Local User/Documents" and its sub-folders will be merged into a file named as "allLog.txt".

Summary

- Text files could be merged using command "copy" or "for /R".

- The first method only apply to situation with flatten file structure while the second could      be applied to sub-folder structure.

- Wildcard symbol * could be used for filename or file extension selection.