Random Thread - Anything Goes

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.