CSV Template Merge

SQL Generator

2023-12-14

One helpful thing is making SQL data with the CSV Template Merge tool. Some may call this SQL generation or data import depending on how they get the data.

Introduction

It is best to gather all the information you need and put it into a CSV file. Make sure to include the column names as the first row. For example, here's a simplified user listing.

									name,dob,active
									John,2000-01-01,true
									Jane,2001-01-12,false
									Bob,2002-01-19,true
									

You can customize the template and I want to show some options for each column.

									insert into users (name, dob, active)
									values ('${name}', '${dob}', ${active === 'true' ? 1 : 0});
									

Is the standard I like to use that includes some simple replacement on active. It would show up as

									insert into users (name, dob, active)
									values ('John', '2000-01-01', 1);
									
									insert into users (name, dob, active)
									values ('Jane', '2001-01-12', 0);
									
									insert into users (name, dob, active)
									values ('Bob', '2002-01-19', 1);
									

Ternary results

Now let's break things down a little more and show what each item would return. I'm just using 2 records here because there are many transformations done.

									name,dob,active
									John,2000-01-01,true
									Jane,2001-01-12,false
									

Replacement based on a value

									${active === 'true' ? 1 : 0}
									

This results in

1
									
									0
									

Each row returns just a single value, and it is based on a string of 'true' or not.

Transforming to uppercase

									name,dob,active
									John,2000-01-01,true
									Jane,2001-01-12,false
									

									${name.toUpperCase()}
									

This results in

JOHN
									
									JANE
									

It's just using a built in JavaScript function to do the transformations. You can do a lot more with JavaScript than this.

Date Changes

									name,dob,active
									John,2000-01-01,true
									Jane,2001-01-12,false
									

Dates use the JavaScript rules and can be a little complex to manipulate. Note that the addition of T00:00:00Z sets the time and the timezone to UTC. Formatting dates based on locales. Timezone of UTC has to be set so it doesn't use the timezone on your computer.

${new Date(Date.parse(dob + 'T00:00:00Z')).toUTCString()}
									${new Date(Date.parse(dob + 'T00:00:00Z')).toLocaleDateString('en-US', { timeZone: 'UTC'})}
									${new Date(Date.parse(dob + 'T00:00:00Z')).toLocaleDateString('en-GB', { timeZone: 'UTC'})}
									

This results in

Sat, 01 Jan 2000 00:00:00 GMT
									1/1/2000
									01/01/2000
									
									Fri, 12 Jan 2001 00:00:00 GMT
									1/12/2001
									12/01/2001
									

Note the different order of months and days depending on which locale is used.

Conditionally including the data with a ternary operation

									name,dob,active
									John,2000-01-01,true
									Jane,2001-01-12,false
									

${ active == 'true' ?
									`insert into users (name, dob, active)
									values ('${name}', '${dob}', ${active === 'true' ? 1 : 0});`:`-- Skipped row`
									}
									

This results in

									insert into users (name, dob, active)
									values ('John', '2000-01-01', 1);
									
									-- Skipped row
									

Argument Mapping

If you needed to do some advanced items against all columns, start with something like this.

									${ Array.from(arguments).map((x, i) => `${x} is in index ${i}`).join('\n') }
									
									
									

Which would produce

									John is in index 0
									2000-01-01 is in index 1
									true is in index 2
									
									Jane is in index 0
									2001-01-12 is in index 1
									false is in index 2
									

Thank you.
Ben Maddox

Tool Update - CSV Template Merge

2023-11-30

I have a new tool that has been rolling around in my head for a while. CSV Template Merge. There are many times when I've had to take a list of data and output repetitive code/data. There are many ways to do this, but it felt more challenging to set up than I wanted. With this tool, it should be much easier. You can set up what each item will output using a JavaScript template that is edited inline. You don't have to write the code and can see updates quickly. And because of how you set up the templates, it can use the full power of JavaScript.

One area where this would provide a lot of day-to-day use is formatting data for SQL scripting. As a simplified example, I have the CSV input.

									name,dob,active
									John,1/1/2000,true
									Jane,1/1/2001,false
									Bob,1/1/2002,true
									

and the template

									insert into users (name, dob, active)
									values ('${name}', '${dob}', ${active === 'true' ? 1 : 0});
									

Once merged, you'll get insert statements like this.

									insert into users (name, dob, active)
									values ('John', '1/1/2000', 1);
									
									insert into users (name, dob, active)
									values ('Jane', '1/1/2001', 0);
									
									insert into users (name, dob, active)
									values ('Bob', '1/1/2002', 1);
									

I like it this way because you're not dealing with a lot of manual replacement, excel string concatenation, or regex manipulation. I hope others also find this to be useful.

Have a great day!
Ben Maddox