Random Thread - Anything Goes

DustinB3403

How fast Santa goes to hit everyone's house in a night..

650 miles per second

From The Physics of Santa Claus ()

"Santa has 31 hours of Christmas to work with, thanks to the different time zones and the rotation of the earth, assuming he travels east to west(which seems logical). This works out to 822.6 visits per second. This is to say that for each Christian household with good children, Santa has 1/1000th of a second to park, hop out of the sleigh, jump down the chimney, fill the stockings, distribute the remaining presents under the tree, eat whatever snacks have been left, get back up the chimney, get back into the sleigh and move on to the next house. Assuming that each of these 91.8 million stops are evenly distributed around the earth (which, of course, we know to be false but for the purposes of our calculations we will accept), we are now talking about .78 miles per household, a total trip of 75-1/2 million miles, not counting stops to do what most of us must do at least once every 31 hours, plus feeding etc.
This means that Santa's sleigh is moving at 650 miles per second, 3,000 times the speed of sound. For purposes of comparison, the fastest man- made vehicle on earth, the Ulysses space probe, moves at a poky 27.4 miles per second - a conventional reindeer can run, tops, 15 miles per hour."

JaredBusch

@JaredBusch said in Random Thread - Anything Goes:

@JaredBusch said in Random Thread - Anything Goes:

Microsoft can kiss my fucking ass.

SQL, Excel, Access, all of it.

This keeps getting up votes.. so for reference...

So the workaround... I manually created a table with all the columns in the spreadsheet using datatype NVARCHAR(255) for every column.

I opened the spreadhseet in LibreOffice Calc.

Saved the sheet as a csv, chosing the | for my delimiter because it does not exist in the file.

Then some find/replace magic in VSCode.

' -> ''
" -> ""
\n -> ');\n
| -> ','
^ -> INSERT INTO NETL_Import (columns listed) value ('

Saved as NETL_Import.sql and ran it in Azure Data Studio.

12328 rows imported later, I now have to clean all that shit up into real data types.

Figured out a bunch of easy cleanup....
0_1542692739731_0ddb6ca8-5105-4e79-a8fa-6d01f0f525c3-image.png

Now working my way column by column to make shit correct..
0_1542692799348_56ef5bcc-044c-4719-a863-1a07ffd00601-image.png

But it will be correct, and it will be a repeatable script. Because there is no way in hell that we've been told everything correctly.

JaredBusch

The block comments around the updates are just so I do not run them every time I rerun the insert as I work down each column. The top of the entire file starts with a truncate to I can jsut keep rerunning things adding columns of data.

nadnerB

RojoLoco

scottalanmiller

https://images.i.thechive.com/__bc9e6ec37c30767121ec3e048abb7366_width-600.jpeg

scottalanmiller

https://images.i.thechive.com/__611cce5686184638e8ac492228866192_width-600.jpeg

scottalanmiller

https://images.i.thechive.com/__72022a9165a1a3f6b8ac55716b88c733_width-600.jpeg

scottalanmiller

https://images.i.thechive.com/__8f6987b667c8c1f65adb9c23672bd494_width-600.jpeg

scottalanmiller

https://images.i.thechive.com/__a1618f5f0cdf5dea3d70b4ab2916c389_width-600.jpeg

JaredBusch

@JaredBusch said in Random Thread - Anything Goes:

@JaredBusch said in Random Thread - Anything Goes:

@JaredBusch said in Random Thread - Anything Goes:

Microsoft can kiss my fucking ass.

SQL, Excel, Access, all of it.

This keeps getting up votes.. so for reference...

So the workaround... I manually created a table with all the columns in the spreadsheet using datatype NVARCHAR(255) for every column.

I opened the spreadhseet in LibreOffice Calc.

Saved the sheet as a csv, chosing the | for my delimiter because it does not exist in the file.

Then some find/replace magic in VSCode.
' -> ''
" -> ""
\n -> ');\n
| -> ','
^ -> INSERT INTO NETL_Import (columns listed) value ('
Saved as NETL_Import.sql and ran it in Azure Data Studio.

12328 rows imported later, I now have to clean all that shit up into real data types.

Figured out a bunch of easy cleanup....

Now working my way column by column to make shit correct..

But it will be correct, and it will be a repeatable script. Because there is no way in hell that we've been told everything correctly.

So, done. I have the data in my local test system.. OMG what a pain in the ass.

Oh and now, I cannot just dump the data and import it to their host, because SQL Server isn't designed for that........

tonyshowoff

@JaredBusch said in Random Thread - Anything Goes:

@JaredBusch said in Random Thread - Anything Goes:
@JaredBusch said in Random Thread - Anything Goes:

@JaredBusch said in Random Thread - Anything Goes:

Microsoft can kiss my fucking ass.

SQL, Excel, Access, all of it.

This keeps getting up votes.. so for reference...

So the workaround... I manually created a table with all the columns in the spreadsheet using datatype NVARCHAR(255) for every column.

I opened the spreadhseet in LibreOffice Calc.

Saved the sheet as a csv, chosing the | for my delimiter because it does not exist in the file.

Then some find/replace magic in VSCode.
' -> ''
" -> ""
\n -> ');\n
| -> ','
^ -> INSERT INTO NETL_Import (columns listed) value ('
Saved as NETL_Import.sql and ran it in Azure Data Studio.

12328 rows imported later, I now have to clean all that shit up into real data types.

Figured out a bunch of easy cleanup....

Now working my way column by column to make shit correct..

But it will be correct, and it will be a repeatable script. Because there is no way in hell that we've been told everything correctly.
So, done. I have the data in my local test system.. OMG what a pain in the ass.

Oh and now, I cannot just dump the data and import it to their host, because SQL Server isn't designed for that........

Whenever I have to deal with insane escaping or anything like that in generated SQL, I just convert the value to binary, so:

O'm"
lol

0x4f276d220a6c6f6c

JaredBusch

@tonyshowoff said in Random Thread - Anything Goes:

@JaredBusch said in Random Thread - Anything Goes:
@JaredBusch said in Random Thread - Anything Goes:
@JaredBusch said in Random Thread - Anything Goes:

@JaredBusch said in Random Thread - Anything Goes:

Microsoft can kiss my fucking ass.

SQL, Excel, Access, all of it.

This keeps getting up votes.. so for reference...

So the workaround... I manually created a table with all the columns in the spreadsheet using datatype NVARCHAR(255) for every column.

I opened the spreadhseet in LibreOffice Calc.

Saved the sheet as a csv, chosing the | for my delimiter because it does not exist in the file.

Then some find/replace magic in VSCode.
' -> ''
" -> ""
\n -> ');\n
| -> ','
^ -> INSERT INTO NETL_Import (columns listed) value ('
Saved as NETL_Import.sql and ran it in Azure Data Studio.

12328 rows imported later, I now have to clean all that shit up into real data types.

Figured out a bunch of easy cleanup....

Now working my way column by column to make shit correct..

But it will be correct, and it will be a repeatable script. Because there is no way in hell that we've been told everything correctly.
So, done. I have the data in my local test system.. OMG what a pain in the ass.

Oh and now, I cannot just dump the data and import it to their host, because SQL Server isn't designed for that........
Whenever I have to deal with insane escaping or anything like that in generated SQL, I just convert the value to binary, so:
O'm"
lol
0x4f276d220a6c6f6c

I didnn't design the database structure. I'm just working with it.

tonyshowoff

@JaredBusch said in Random Thread - Anything Goes:

@tonyshowoff said in Random Thread - Anything Goes:
@JaredBusch said in Random Thread - Anything Goes:
@JaredBusch said in Random Thread - Anything Goes:
@JaredBusch said in Random Thread - Anything Goes:

@JaredBusch said in Random Thread - Anything Goes:

Microsoft can kiss my fucking ass.

SQL, Excel, Access, all of it.

This keeps getting up votes.. so for reference...

So the workaround... I manually created a table with all the columns in the spreadsheet using datatype NVARCHAR(255) for every column.

I opened the spreadhseet in LibreOffice Calc.

Saved the sheet as a csv, chosing the | for my delimiter because it does not exist in the file.

Then some find/replace magic in VSCode.
' -> ''
" -> ""
\n -> ');\n
| -> ','
^ -> INSERT INTO NETL_Import (columns listed) value ('
Saved as NETL_Import.sql and ran it in Azure Data Studio.

12328 rows imported later, I now have to clean all that shit up into real data types.

Figured out a bunch of easy cleanup....

Now working my way column by column to make shit correct..

But it will be correct, and it will be a repeatable script. Because there is no way in hell that we've been told everything correctly.
So, done. I have the data in my local test system.. OMG what a pain in the ass.

Oh and now, I cannot just dump the data and import it to their host, because SQL Server isn't designed for that........
Whenever I have to deal with insane escaping or anything like that in generated SQL, I just convert the value to binary, so:
O'm"
lol
0x4f276d220a6c6f6c
I didnn't design the database structure. I'm just working with it.

The structure doesn't matter, if you're pulling out the data from a CSV, you just create a hex string (make sure you start with 0x) of it so you don't have to worry about escaping new lines, quotes, and the few other characters needed.