Tag Archives: windows

08 Apr

MySQL Load Data Infile Syntax Generator Tool Download

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. The file name must be given as a literal string.

I have already written the basic how to follow that considering additional cases.

A lot of questions I have seen for loading separated data to MySQL, so here I’ve created a very beginner level excel tool that will allow you to generate the LOAD DATA syntax as per your choices.
Presently sample table name and field/line separators are specified, which you may alter as per your own file.

Load-data-syntax-v1.0

Load-data-syntax-v1.0

Download the sheet here: Load Data

Load Data Syntax:

Options column specifies options that are required for syntaxes generation.
Under Selection column you will choose your option while under Description column, for each Option description is provided.

Using basic knowledge of load data you can quite easily generate the syntax by specifying option, choosing file to be loaded and finally clicking the Syntax button.

Please refer the syntax on MySQL documentation as well.

This is a “early release”, please check for the updates.
If you have any suggestions or bug report please comment / mail.

Thanks.

20 Mar

Working with EditPlus Text Editor-Regular Expression How To

Editplus is a lot better than the regular text editor, Notepad.
From all it’s features I like RegExp Support the most, and than comes the block select feature.
Here are the quick lines to carry out regular tasks using regular expression in Editplus. It’s kinda downloadable cheetsheet list.

Remove all empty lines:

Find: “^\n” (Ignore double-quotes in all find/replace)
Replace: “”
Where,
^ – Beginning of the line
\n – New Line

Remove Multiple Spaces convert into single space:

Find: ” +”
Replace: ” “
Where,
+ – find one or more occurance of space character.

Comment multiple line of code:

Find: “^”
Replace: “#” or “//”
You may optionally use: Edit Menu > Format > Line Comment.
Generate Comma Separated List from new line delimited list:

Find: “\n”
Replace: “, “
This helps in even joining some of lines of code instead of replacing by comma you may replace it with “”.

Manipulate columns display order / punctuation:

Find: “([0-9]+)\t([a-zA-Z]+)”
Replace: “\2\t\1″
Where,
[0-9]+ – Finds one or more digits
[a-zA-Z]+ – Finds one or more characters
() – mark the block or capture the group
\2 – 2nd mark expression
Eg:
123 abc
345 cde
567 efg
Becomes:
abc 123
cde 345
efg 567
The Other Way:
- Press Alt+C
- Drag you mouse to select respective column and click
- Copy / Cut as required

[ad#ad-2-300×250]

Append / Add semicolon (any character) at the end of the line:

Find: “\n”
Replace: “;\n”

Enclose lines by quotes:

Find: “\n”
Replace: “‘\n'”

Delete all lines containing a given STRING:

Find: “^.*STRING.*$”
Replace: “”

Remove lines not containing a given STRING:

I don’t know how to do this!! :)

Convert tab separated file into insert statements:

TSV: abcd de4 iirn 34399
SQL: INSERT INTO TABLENAME VALUES (“abcd”, “de4″, “iirn”,”34399″);
Find: “(.*)\t(.*)\t(.*)\t(.*)”
Replace: “INSERT INTO TABLENAME VALUES (“\1″, “\2″, “\3″,”\4″);”

Format the telephone number:

Find: “([0-9][0-9][0-9])([0-9][0-9][0-9])([0-9].*)”
Replace: “\1-\2-\3″
Eg.:

Original: 1231231231
Formatted-1: 123-123-1231

Remove Brackets:

Find: “\(|\)”
Replace: “”
Where,
\( – Match (. \ is required to escape marking the expression.
| – or

Replace 1st occurrence of character:

Find: ” (.*)”
Replace: “-\1″
Where,
(.*) – matches everything and marks the block
** Make sure you ignore double-quotes(“) while writing in find / replace boxes.

EditPlus supports following regular expressions in Find, Replace and Find in Files command.

Expression – Description
  • \t – Tab character.
  • \n – New line.
  • . – Matches any character.
  • | – Either expression on its left and right side matches the target string.
  • [] – Any of the enclosed characters may match the target character.
  • [^] – None of the enclosed characters may match the target character.
  • * – Character to the left of asterisk in the expression should match 0 or more times.
  • + – Character to the left of plus sign in the expression should match 1 or more times.
  • ? – Character to the left of question mark in the expression should match 0 or 1 time.
  • ^ – Expression to the right of ^ matches only when it is at the beginning of line.
  • $ – Expression to the left of $ matches only when it is at the end of line.
  • () – Affects evaluation order of expression and also used for tagged expression.
  • \ – Escape character. If you want to use character “\” itself, you should use “\\”.

Notable Features of Editplus are :

  • Spell checking
  • Regex-based find & replace
  • Encoding conversion
  • Newline conversion
  • Syntax highlighting
  • Multiple undo/redo
  • Rectangular block selection
  • Auto indentation
  • Code folding (Text folding)

Download pdf: Editplus-RegExp.

18 Mar

batch script to add remove prefix zero pad bulk file rename

This post has two batch scripts:
1. Batch script to rename files with zero padded number series-prefix

2. Batch script to remove prefix of perticular length

1. Batch script to rename files with zero padded number series-prefix

This script will accept file-type to be searched and lenght of zero-padded prefix to be attached.

Usage: RenZeroPad.bat

Consider you have to rename / arrange a lot of mp3 files in a perticular sequence:

File names before execution:
fileX.mp3
fileY.mp3
fileZ.mp3

Command on dos prompt: RenZeroPad.bat mp3 4

File names with attached prefix:
0001 fileX.mp3
0002 fileY.mp3
0003 fileZ.mp3

[ad#ad-2-300×250]

The Batch Script to add zero pad digits prefix:

@echo off
setLocal EnableDelayedExpansion

set /a cnt=1
for %%i in (*.%1) do (
call :Set0Pad %2

set newName=!str! %%i
ren “%%i” “!newName!”
)

:Set0Pad
set padcntr=0000000000%cnt%
set str=%padcntr:~-%1%
set renstr=%str%
set /a cnt+=1

** Download available at the end of the page.

2. Batch script to remove prefix of perticular length

This script will accept file-type to be searched and lenght of prefix to be removed.

Usage: RemovePrefix.bat

Consider you have to rename / remove prefixes from a bunch of files:

0001_fileX.doc
0002_fileY.doc
0003_fileZ.doc

Command on dos prompt: RemovePrefix.bat mp3 5

Files will be renamed with removed prefixes as follows:
fileX.doc
fileY.doc
fileZ.doc

The Batch Script to remove prefix:

@echo off
setLocal EnableDelayedExpansion

set /a cnt=1
for %%i in (*.%1) do (
set str=%%i
set newstr=!str:~%2!
ren “%%i” “!newstr!”
)

Download: RemovePrefix.bat and RenZeroPad.bat

24 Nov

Using VLookup like Batch script to compare two excel / csv

Using Vlookup:

I have two csv files; File1 has Id and Value Columns and File2 has Id.

Problem: I need to compare both files and put respective values to File2 from File1.

Solution: VLookup in excel

Steps:

  • Open both files in Excel & arrange it vertically (Window >> Arrange; for ease)

vlookup-1

  • Click on cell where you want to put compared value: B2
  • Click on fx button and select VLOOKUP function.

vlookup-2

  • Under lookup_value, click first search field: A2.
  • Click back in Table_array text field, and click on button to select range. Select two column for comparison starting from second row, i.e. A2 to B9.
  • Put 2 in Col_index_num. If lookup succeeds in finding lookup_value in Table_array it will return 2nd value as result.
  • Put FALSE in Range_lookup to do exact match.
  • Click ok, and you will find B2 will be filled with respective value.
  • You may drag the command to other rows and values will get filled. Optionally you may copy and paste it from rows B3 to B9 to lookup and fill all values.

vlookup-3

  • You can observer in output here, if I change the value of ID in File2 which doesn’t exist, VLookup returns #N/A.

Descriptions in details for this function and each respected field are easily available in excel help.

Download Files: File1, File2

Batch script to compare and assign value-simulate vlookup:

Further I came up with a batch script to compare field value, simulate vlookup. After doinglittle testing, I managed to fix it.

for /f “tokens=1 delims=, skip=1″ %%i in (File2.csv) do @findstr  “%%i,” File1.csv >nul & If errorlevel 0 if not errorlevel 1 (for /f “tokens=1,2 delims=,” %%m in (‘findstr /i /L “%%i,” File1.csv’) do (@echo %%m,%%n>>output.csv echo %%i)) else (echo %%i,NA>>output.csv)

Please consider this batch script is written specifically for these csv and prove my understanding.
This script will compare file2 with file1, extract similar data from file1 and put it in output.csv.

Download batch file: vlookup-batch.bat

If you find any difficulty or don’t yield required results by following above process or using script, comment.

18 Nov

bat – batch file to create formatted date time (ddmmyyyy) directory

I required to make a batch script which needs file / directory to be created with current date / time stamps.
Following are the code snipts for formating date / time in a batch script:

FOR /F “TOKENS=1* DELIMS= ” %%A IN (‘DATE/T’) DO SET MYDATE=%%B
FOR /F “TOKENS=1,2 eol=/ DELIMS=/ ” %%A IN (‘DATE/T’) DO SET mm=%%B
FOR /F “TOKENS=1,2 DELIMS=/ eol=/” %%A IN (‘echo %MYDATE%’) DO SET dd=%%B
FOR /F “TOKENS=2,3 DELIMS=/” %%A IN (‘echo %MYDATE%’) DO SET yyyy=%%B
set DATED=%mm%%dd%%yyyy%
md %DATED: =%

or

FOR /F “tokens=*” %%A IN (‘DATE/T’) DO SET MYDATE=%%A
#Using regular expresions to remove / and DAY
SET MYDATE=%MYDATE:/=%
SET MYDATE=%MYDATE:* =%
md %MYDATE%

Other ways to achieve date string in a batch script variable:

@echo off
set yy=%date:~-4%
set mm=%date:~-7,2%
set dd=%date:~-10,2%
set MYDATE=%yy%%mm%%dd%

or

for /f “tokens=2-4 delims=/ ” %%g in (‘date /t’) do (
set mm=%%h
set dd=%%g
set yy=%%i
)
set MYDATE=%yy%%mm%%dd%

Similarly we can work with time:

for /f “tokens=1-2 delims=: ” %%j in (‘time /t’) do (
set hh=%%j
set mn=%%k
)
set MYTIME=%hh%%mn%

Further you can go ahead mixing time with date as well.

-- Kedar Vaijanapurkar --