<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Sasha Korovkina on Medium]]></title>
        <description><![CDATA[Stories by Sasha Korovkina on Medium]]></description>
        <link>https://medium.com/@sasha.korovkina2003?source=rss-af2804de7fa7------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*KHjtG4QQbAqXlc9btm09Hw.png</url>
            <title>Stories by Sasha Korovkina on Medium</title>
            <link>https://medium.com/@sasha.korovkina2003?source=rss-af2804de7fa7------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Mon, 16 Nov 2026 15:38:04 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@sasha.korovkina2003/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[The Fastest Way to Locate a Value Across Multiple Tables and Databases in SQL Server]]></title>
            <link>https://blog.devgenius.io/the-fastest-way-to-locate-a-value-across-multiple-tables-and-databases-in-sql-server-17de7cb9a7c5?source=rss-af2804de7fa7------2</link>
            <guid isPermaLink="false">https://medium.com/p/17de7cb9a7c5</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[sql-server]]></category>
            <category><![CDATA[microsoft]]></category>
            <category><![CDATA[query-optimization]]></category>
            <dc:creator><![CDATA[Sasha Korovkina]]></dc:creator>
            <pubDate>Thu, 21 Nov 2024 13:27:27 GMT</pubDate>
            <atom:updated>2024-11-21T15:52:43.013Z</atom:updated>
            <content:encoded><![CDATA[<h4>We will find the optimal way for looking up a single value across multiple tables and databases and return every place where it appears.</h4><h3>Problem</h3><p>The problem which we are solving is:</p><ol><li>An analyst has a certain value which they are trying to find in a SQL database.</li><li>The analyst does not know which table or column this value belongs to.</li><li>The analyst wants to find out if this<strong> value exists in the database, and, if so, where it can be found.</strong></li></ol><p>I recently encountered this challenge at CMI2I, where we frequently work with external data feeds. It&#39;s common to receive massive SQL outputs as data feeds, making it both tedious and time-consuming to pinpoint the locations of the values that truly matter.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*YGNPr-uR4BmQEP8Y" /><figcaption>Photo by <a href="https://unsplash.com/@jankolar?utm_source=medium&amp;utm_medium=referral">Jan Antonin Kolar</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h3>Experiment Conditions</h3><p>There are a few parameters we want to measure for each query:</p><ol><li>The time it takes</li><li>The resources it consumes</li></ol><p>We can use the following 2 commands to prefix our queries:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/9fd0475926a001babb89ea8053696032/href">https://medium.com/media/9fd0475926a001babb89ea8053696032/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/411/1*M2d4B_Rz3pJZcxyZTHKP3g.png" /><figcaption>The statistics displayed. Image produced by the author.</figcaption></figure><p>Here is an explanation of each statistic:</p><p><strong>Query Parse and Compile Time</strong></p><ul><li><strong>CPU time</strong>: The time spent by the CPU to parse and compile the query, measured in milliseconds.</li><li><strong>Elapsed time</strong>: The total time (wall-clock time) it took to parse and compile the query.</li></ul><p><strong>Execution Times</strong></p><ul><li><strong>CPU time</strong>: The time the CPU actively spent processing the query.</li><li><strong>Elapsed time</strong>: The total time it took for the query to execute, including waiting for external factors like I/O or locks.</li></ul><p>The difference between CPU time and elapsed time indicates the time SQL Server spent waiting for resources, such as reading data from disk or resolving locks.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/644/1*V46jF4Ceb5QFWjfTzHUPwg.png" /><figcaption>SQL IO statistics. Image produced by the author.</figcaption></figure><p><strong>STATISTICS IO Output</strong></p><ul><li><strong>Table</strong>: Identifies the table where reads are performed, helping locate areas for tuning in queries involving multiple tables.</li><li><strong>Scan Count</strong>: The number of times the table was accessed during the query; can indicate how efficiently indexes are used.</li><li><strong>Logical Reads</strong>: The number of 8KB data pages read from memory, representing the in-memory workload for the query.</li><li><strong>Physical Reads</strong>: The number of 8KB data pages retrieved from disk, indicating how much of the query’s workload required disk I/O.</li><li><strong>Read-Ahead Reads</strong>: Physical reads performed in advance to pre-load data pages into memory, aiming to optimize query performance.</li><li><strong>LOB Logical/Physical/Read-Ahead Reads</strong>: Reads specific to Large Object (LOB) data, such as varbinary(max) or text fields, which are stored separately from regular table data.</li></ul><blockquote>For more information on how to monitor the performance of your queries, you can have a look at this article: <a href="https://www.sqlservercentral.com/blogs/measuring-sql-query-performance">https://www.sqlservercentral.com/blogs/measuring-sql-query-performance</a></blockquote><h3>Approach #1 — Initial Query</h3><p>To start, I have found a query example which solves the task above. I wanted to get the general logic of the query and then tweak and optimize it later.</p><blockquote>I got this initial query from this StackOverflow Thread: <a href="https://stackoverflow.com/questions/436351/find-a-value-anywhere-in-a-database">https://stackoverflow.com/questions/436351/find-a-value-anywhere-in-a-database</a></blockquote><h4>The Query Logic</h4><p>The query leverages cursors to iterate through tables and their respective columns. It begins by looping over each table, then processes each column within the table. During this process, it constructs and executes IF statements, storing the results in a temporary table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/627/1*Mxpc5GSYQaPKrXqRe8q_xQ.png" /><figcaption>Inital query logic. Image produced by the author.</figcaption></figure><p>The initial query we will use works as follows:</p><ul><li>The user inputs the <strong>value and the schema</strong></li><li>The algorithm finds all of the tables in that schema using the query below:</li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/615cc76c198912fa0339019ef3d35b7c/href">https://medium.com/media/615cc76c198912fa0339019ef3d35b7c/href</a></iframe><ol><li><strong>Initialize Table Cursor</strong> — Start by creating a cursor to iterate over all the tables in the database (or a specified schema).</li><li><strong>Iterate Over Tables</strong> — For each table, initialize a second cursor to loop through its columns.</li><li><strong>Search Within Columns</strong> — For every column in the current table, search for the specified value in that column.</li><li><strong>Store Results</strong> — If a match is found, record the table name in a temporary table.</li><li><strong>Continue Searching </strong>— Move to the next column and repeat until all columns in the table have been checked, then proceed to the next table.</li></ol><p>Here is what the final query looks like:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/55fb844255fb30605fc3aed74012c774/href">https://medium.com/media/55fb844255fb30605fc3aed74012c774/href</a></iframe><h4>Test Approach #1</h4><p>For my testing value, this query took 40+ minutes to run, I had to kill it after this point. Therefore, we can see that this approach is not very efficient and we need to make some adjustments to our logic.</p><h3>Approach #2 — Optimized Query</h3><p>After a quick glance our previous query, a few immediate concerns come up:</p><ol><li>I am using lots of <strong>cursors</strong> (not in the best way possible). The nested cursor logic (looping through columns for each table) can be restructured using set-based logic.</li><li>Bad use of dynamic SQL.</li><li>Querying the METADATA table multiple times can be avoided.</li><li>The LIKE %value% pattern can be substituted by direct text comparison.</li><li>Avoid constructing @where_clause strings repeatedly within a loop.</li><li>EXISTS queries can be optimized by ensuring they terminate as soon as a match is found.</li></ol><p>Let’s cover each of these points in more detail…</p><h4>Logical Improvements</h4><p>We saw that the previous query uses a lot of <strong>line-by-line </strong>loops, which is highly inefficient and can be avoided in this case.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/245/1*KW4xWquokxB3G0pb2Q65_Q.png" /><figcaption>New query logic. Image produced by the author.</figcaption></figure><p>By doing this logic, we only have <strong>1 WHILE </strong>loop, which is an improvement from 2 in the previous case.</p><blockquote>PS Despite it being a common convention to switch from <strong>CURSOR</strong>s to <strong>WHILE </strong>loop for the sake of optimization, this is not always the case. If you want more information on when a <strong>CURSOR </strong>outperforms a <strong>WHILE </strong>loop and visa verse, you can check this article: <a href="https://sqlblog.org/2012/01/26/bad-habits-to-kick-thinking-a-while-loop-isnt-a-cursor">https://sqlblog.org/2012/01/26/bad-habits-to-kick-thinking-a-while-loop-isnt-a-cursor</a></blockquote><h4>Code Improvements</h4><p>We are going to replace the <strong>‘%LIKE%’ </strong>statement by an <strong>equals (=) </strong>statement. Of course, this depends on what kind of values you are looking for but it is fine in my case.</p><blockquote>If you want to get a detailed comparison of performance of both clauses, check out this thread: <a href="https://stackoverflow.com/questions/6142235/sql-like-vs-performance">https://stackoverflow.com/questions/6142235/sql-like-vs-performance</a></blockquote><p>Here is what the final code with this logic implemented looks like:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/a0263e437b38787bcf2191f8b5022e05/href">https://medium.com/media/a0263e437b38787bcf2191f8b5022e05/href</a></iframe><h4>Test Approach #2</h4><p>For the same value as used in Test #1, Test #2 took only 6 seconds, which is a significant improvement.</p><h3>Approach #3 — dbForge Search</h3><p>dbForge Search is a <strong>free Microsoft SQL Server Studio plug-in</strong> which allows you to search entities within your databases.</p><blockquote>You can download it for free here: <a href="https://www.devart.com/dbforge/sql/search/download.html">https://www.devart.com/dbforge/sql/search/download.html</a></blockquote><p>Some features which make it better than searching with a query are:</p><ol><li>You have <strong>all of your databases </strong>in a single interface. This makes it easy to chose which database you want to search.</li><li>You can search the <strong>DDL </strong>and <strong>Data </strong>from the same interface, which means that if the value you are searching for could be a table/column name or a value within that column, you can see both cases.</li><li>It finds the resulting table in <strong>object explorer </strong>for you. This is very useful when working with lots of tables.</li></ol><p>Here is what the interface looks like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/905/1*nBPuJt47ugZ-KERN8nPMMw.png" /><figcaption>dbForge interface. Image generated by author.</figcaption></figure><p>This approach may be slightly slower than executing a direct query, but the flexibility and versatility this tool provides often outweigh its occasional performance delays.</p><h3>Conclusion</h3><p>After this, I was pretty satisfied with the performance of the second query and dbForge. I have decided to use dbForge mainly to find database objects and the second query for a more optimized single value search.</p><p><em>Give these tools a try and let me know what you think! Have a great day!</em>❤️</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=17de7cb9a7c5" width="1" height="1" alt=""><hr><p><a href="https://blog.devgenius.io/the-fastest-way-to-locate-a-value-across-multiple-tables-and-databases-in-sql-server-17de7cb9a7c5">The Fastest Way to Locate a Value Across Multiple Tables and Databases in SQL Server</a> was originally published in <a href="https://blog.devgenius.io/">Dev Genius</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[A Complete Guide to Scrape Tesco Real Food Data]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://medium.com/@sasha.korovkina2003/a-complete-guide-to-scrape-tesco-real-food-data-eae81b95efa4?source=rss-af2804de7fa7------2"><img src="https://cdn-images-1.medium.com/max/2600/0*ZqhTCC8DaYM6RtX-" width="5184"></a></p><p class="medium-feed-snippet">I will teach you how to scrape Tesco Real Food data with code examples and resulting dataset at the end of the article</p><p class="medium-feed-link"><a href="https://medium.com/@sasha.korovkina2003/a-complete-guide-to-scrape-tesco-real-food-data-eae81b95efa4?source=rss-af2804de7fa7------2">Continue reading on Medium »</a></p></div>]]></description>
            <link>https://medium.com/@sasha.korovkina2003/a-complete-guide-to-scrape-tesco-real-food-data-eae81b95efa4?source=rss-af2804de7fa7------2</link>
            <guid isPermaLink="false">https://medium.com/p/eae81b95efa4</guid>
            <category><![CDATA[food]]></category>
            <category><![CDATA[selenium]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[scraping]]></category>
            <dc:creator><![CDATA[Sasha Korovkina]]></dc:creator>
            <pubDate>Fri, 27 Sep 2024 09:05:03 GMT</pubDate>
            <atom:updated>2024-09-27T09:05:03.487Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[How to Combine Clustering and Gradient Boosting — Predict Financial Data with Enhanced Accuracy]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://medium.com/@sasha.korovkina2003/how-to-combine-clustering-and-gradient-boosting-predict-financial-data-with-enhanced-accuracy-64b7df7065aa?source=rss-af2804de7fa7------2"><img src="https://cdn-images-1.medium.com/max/2600/0*C1UXvR-62M8rNHiS" width="4965"></a></p><p class="medium-feed-snippet">Predicting financial disclosure sources by using K-Means and relational clusters as a data feature</p><p class="medium-feed-link"><a href="https://medium.com/@sasha.korovkina2003/how-to-combine-clustering-and-gradient-boosting-predict-financial-data-with-enhanced-accuracy-64b7df7065aa?source=rss-af2804de7fa7------2">Continue reading on Medium »</a></p></div>]]></description>
            <link>https://medium.com/@sasha.korovkina2003/how-to-combine-clustering-and-gradient-boosting-predict-financial-data-with-enhanced-accuracy-64b7df7065aa?source=rss-af2804de7fa7------2</link>
            <guid isPermaLink="false">https://medium.com/p/64b7df7065aa</guid>
            <category><![CDATA[clustering]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[finance]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Sasha Korovkina]]></dc:creator>
            <pubDate>Tue, 18 Jun 2024 09:39:13 GMT</pubDate>
            <atom:updated>2024-06-18T09:39:13.522Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[Complex Financial PDF Documents to Excel. A Case Study.]]></title>
            <link>https://medium.com/codex/complex-financial-pdf-documents-to-excel-a-case-study-c54377a948db?source=rss-af2804de7fa7------2</link>
            <guid isPermaLink="false">https://medium.com/p/c54377a948db</guid>
            <category><![CDATA[finance]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[life]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[pdf]]></category>
            <dc:creator><![CDATA[Sasha Korovkina]]></dc:creator>
            <pubDate>Sun, 19 May 2024 08:12:53 GMT</pubDate>
            <atom:updated>2024-05-19T08:12:53.768Z</atom:updated>
            <content:encoded><![CDATA[<h3>Goal</h3><p>The goal of this article is to accurately extract data from both structured and unstructured financial PDFs into Excel using Python and Excel functionality to achieve this.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*L3fjcXFtwXt2CSWE" /><figcaption>Photo by <a href="https://unsplash.com/@northfolk?utm_source=medium&amp;utm_medium=referral">NORTHFOLK</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h3>PDF Types</h3><p>I classify financial PDFs into 2 categories — a structured PDF and a non-structured PDF.</p><ul><li>A structured PDF — This is a structured PDF, meaning its layout makes the content clear. When someone looks at it, they can easily see how the elements are related because of how they’re positioned. Elements which refer to the same record are all grouped on a single row.</li></ul><p><em>Example:</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/487/0*fKhLabPPoEqkOo0C.png" /><figcaption>Image source: <a href="https://support.microsoft.com/en-us/office/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c">https://support.microsoft.com/en-us/office/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c</a>.</figcaption></figure><p>From this table, it is easy to tell that each row is referring to a single product and its price. We do not need to make any inferences with this table structure.</p><ul><li>A non-structured PDF — This PDF lacks structure, meaning that someone who hasn’t worked with it before won’t be able to easily see how the different parts relate to each other.</li></ul><p><em>Example:</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*1iLs_Jz7gj7bTgcB.jpg" /><figcaption>Image source: <a href="https://www.excelstemplates.com/income-statement-template">https://www.excelstemplates.com/income-statement-template</a></figcaption></figure><p>The PDF is a bit confusing at first glance, especially when trying to figure out what the 900,000 refers to. The irregular spacing doesn’t help either, making it harder to see how everything fits together. Plus, it’s not clear why things are grouped as a, b, and c. We need someone with expertise to organize the PDF into a logical structure before we can put it into Excel.</p><h3>PDF Handling</h3><p>For structured PDFs, utilizing <strong>Excel’s in-built functionality</strong> is logical because Excel is designed to efficiently manage structured data, recognize patterns, and offer extensive capabilities for data manipulation. On the other hand, for unstructured PDFs, employing <strong>coordinate mapping</strong> is suitable as it allows for flexible data extraction from diverse formats, adaptation to varying layouts, and customized extraction criteria based on specific coordinates within the document.</p><p><em>Let’s dive in!</em></p><h3>Unstructured PDF — Coordinate Mapping</h3><blockquote>Please note that sample data has been utilized consistently throughout. Bear this in mind while attempting to execute the code.</blockquote><p>Here is the process overview for coordinate mapping:</p><ol><li>Retrieve the document’s source from the financial software system that generated it.</li><li>Convert the document into an image format and utilize Optical Character Recognition (OCR) to extract both the graphical components and textual content.</li><li>Utilize outlines or structural analysis to identify and define distinct text groups within the Excel table.</li><li>Import the extracted dataset into Excel, arranging it according to the identified text groups.</li></ol><blockquote><strong>Pro tip:</strong> if your PDF contains underlined text, check out this article <a href="https://medium.com/towards-data-science/get-underlined-text-from-any-pdf-b7b068ca3d64">https://medium.com/towards-data-science/get-underlined-text-from-any-pdf-b7b068ca3d64</a></blockquote><h4>PDF Software Underlayer</h4><p>To ascertain the document’s origins and potential structure, our initial step is to access the software responsible for generating the PDF. This inquiry will discern whether the document was manually crafted or generated through software. Typically, financial PDFs are software-generated, simplifying the structuring process.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/9e363fed9b9d70fc832e18a0d89747f6/href">https://medium.com/media/9e363fed9b9d70fc832e18a0d89747f6/href</a></iframe><h4>PDF Component Extraction</h4><p>We then use the PDF query library to extract the fields and the text from the PDF file.</p><blockquote>For an in-depth explanation of how PDF query works and the best way to use it, have a look at this article: <a href="https://medium.com/dev-genius/building-a-high-precision-financial-pdf-extraction-tool-part-1-c6db2073eeee">https://medium.com/dev-genius/building-a-high-precision-financial-pdf-extraction-tool-part-1-c6db2073eeee</a></blockquote><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/7a3344945e3471eab99e07d06a97769b/href">https://medium.com/media/7a3344945e3471eab99e07d06a97769b/href</a></iframe><p>We meticulously craft rectangular components that encapsulate each element of the page, seamlessly integrating text within them. Through this process, we faithfully replicate the entire structure of the document.</p><h4>Extracting Data Into a Dataframe</h4><p>Each of our elements is defined by 4 coordinates — x0, y0, x1, y1 . If 2 elements align, their x or y coordinates would be equal, for example:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*n3lSOP3LuBIYAyrrA-0UIA.png" /><figcaption>Image source: <a href="https://www.investopedia.com/terms/f/financial-statements.asp">https://www.investopedia.com/terms/f/financial-statements.asp</a>, image edited by the author.</figcaption></figure><p>In the image above, we can see a few types of aligment — left, right and bottom aligment. The elements which are positioned on the red lines would have the same x1, y1 and x2 coordinates respectively.</p><p>Use the code below to get the aligned components in a more complex scenario:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/141122910e80ad541f761fc328d07f22/href">https://medium.com/media/141122910e80ad541f761fc328d07f22/href</a></iframe><h4>Code Breakdown</h4><ul><li>To begin, we extract essential data from the file, such as identification codes, positions, shareholders, and coding dates. These details can be located within the PDF XML file.</li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/79fc96ce2f28bdee59abed0d315a2b15/href">https://medium.com/media/79fc96ce2f28bdee59abed0d315a2b15/href</a></iframe><ul><li>We’ve constructed two lists: ‘bene’ (short for beneficiaries) and ‘pos’ (short for positions). In this instance, these lists delineate shareholders and their respective shareholdings, but their usage can extend to various contexts (e.g., products and prices). When encountering a termination condition, denoted by the string ‘End’ in our case, we then check if there is any data present in the arrays.</li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/8d4ec8c32c9974aa4422abb5a6840313/href">https://medium.com/media/8d4ec8c32c9974aa4422abb5a6840313/href</a></iframe><ul><li>Our objective is to merge the two datasets. To achieve this, we begin by rounding the coordinates. Subsequently, we proceed to update the Excel file with the new values.</li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/4124366c33372f3d821a1176850c1cc9/href">https://medium.com/media/4124366c33372f3d821a1176850c1cc9/href</a></iframe><p>The key part of this code which exports the data from the processed PDF and into the Excel is:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/ccd2dd73a50465befbc56bd851a0510e/href">https://medium.com/media/ccd2dd73a50465befbc56bd851a0510e/href</a></iframe><p>Though I am using a temporary Excel, you can use a non-temporary file as well. Here is what the output from your PDF would look like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/441/1*kWUZ6pRLxVxaYffn_LiZHQ.png" /><figcaption>The data has been extracted and placed into the Excel columns</figcaption></figure><h3>Structured PDF — Excel Import</h3><p>Now we will consider a structured financial document, for which we will use the Excel native import.</p><h4>1. Creating an Empty Excel File</h4><p>To begin, we’ll utilize the xlwings library in Python for interacting with Excel. First, install the library using pip install xlwings. Then, we&#39;ll write a function to handle this initial step:</p><pre>import xlwings as xw<br><br>def create_excel_from_pdf(pdf_file_path):<br>    # Start Excel in the background<br>    app = xw.App(visible=False)<br><br>    # Create a new workbook<br>    wb = app.books.add()<br><br>    # Implement your logic to extract data from the PDF and populate the Excel file<br>    # Example:<br>    ws = wb.sheets.active<br>    ws.range(&#39;A1&#39;).value = &#39;Example Data&#39;<br><br>    # Save the workbook<br>    excel_file_path = &quot;output.xlsx&quot;<br>    wb.save(excel_file_path)<br><br>    # Close the workbook and quit Excel<br>    wb.close()<br>    app.quit()<br><br>    return excel_file_path</pre><pre>    def process_pdf_unstructured(self, pdf_file_path):<br>        # Start Excel in the background<br>        app = xw.App(visible=False)<br><br>        # Create a new workbook<br>        wb = app.books.add()<br><br>        macro_code_template = &#39;&#39;&#39;</pre><p>Now, we will:</p><ul><li><strong>Start Excel in the background:</strong> Initiates an instance of Excel in the background, which is invisible to the user.</li><li><strong>Create a new workbook:</strong> Generates a new Excel workbook within the Excel instance.</li><li><strong>Prepare the macro code template: </strong>A template for the VBA macro code is defined. This macro is responsible for importing data from a PDF file into the Excel workbook.</li><li><strong>Replace placeholder with the actual file path: </strong>Inserts the actual file path of the PDF into the macro code template.</li><li><strong>Add VBA module and write macro code:</strong> A VBA module is added to the workbook, and the modified macro code is written into this module. This macro, named “getData,” includes instructions to import data from the PDF into the Excel workbook and manipulate it.</li><li><strong>Run the macro if it exists: </strong>Checks if the macro named “getData” exists in the workbook. If it does, the macro is executed.</li></ul><p>Here is what the code looks like:</p><pre>    def process_pdf_unstructured(self, pdf_file_path):<br>        # Start Excel in the background<br>        app = xw.App(visible=False)<br><br>        # Create a new workbook<br>        wb = app.books.add()<br><br>        macro_code_template = &#39;&#39;&#39;SOME VBA MACRO&#39;&#39;&#39;<br>        # Fill in the placeholder with the actual file path<br>        macro_code = macro_code_template.replace(&#39;{}&#39;, pdf_file_path)<br><br>        print(macro_code)<br>        # Add a VBA module to the workbook and write the macro code to it<br>        wb_vba = wb.api.VBProject.VBComponents.Add(1)  # 1 means vbext_ct_StdModule, which is a standard VBA module<br>        wb_vba.CodeModule.AddFromString(macro_code)<br><br>        # Run the macro if it exists<br>        if wb.macro(&#39;getData&#39;) is not None:<br>            wb.macro(&#39;getData&#39;)()<br><br>        # Generate the file name for the resulting workbook<br>        base_name = os.path.basename(pdf_file_path)<br><br>        file_name = f&quot;{base_name}output.xlsx&quot;<br>        print(base_name)<br>        target_directory = r&quot;SOME/PDF/PATH&quot;<br>        excel_file_path = os.path.join(target_directory, file_name)<br><br>        # Save the workbook to the target directory<br>        wb.save(excel_file_path)<br>        print(&quot;Path to the resulting workbook:&quot;, excel_file_path)<br><br>        # Close the workbook<br>        wb.close()<br><br>        # Quit Excel<br>        app.quit()<br><br>        # Print the path to the resulting workbook<br>        print(&quot;Processed PDF file path is:&quot;, pdf_file_path)<br><br>        return excel_file_path</pre><p>In VBA code, the fundamental components are:</p><ol><li>Uploading the PDF file.</li><li>Employing the Excel vision algorithm to extract table data from the PDF file.</li></ol><p>The VBA code:</p><pre>Sub getData()<br>    Dim pdfFilePath As String<br>    pdfFilePath = &quot;actual_pdf_file_path.pdf&quot;  &#39; Placeholder for the actual PDF file path<br><br>    &#39; Import data from the PDF into the Excel workbook<br>    ActiveWorkbook.Queries.Add Name:=&quot;Table001 (Page 1)&quot;, Formula:= _<br>        &quot;let&quot; &amp; Chr(13) &amp; &quot;&quot; &amp; Chr(10) &amp; &quot;    Source = Pdf.Tables(File.Contents(&quot;&quot;&quot; &amp; pdfFilePath &amp; &quot;&quot;&quot;), [Implementation=&quot;&quot;1.3&quot;&quot;]),&quot; &amp; Chr(13) &amp; &quot;&quot; &amp; Chr(10) &amp; &quot;    Table001 = Source{[Id=&quot;&quot;Table001&quot;&quot;]}[Data],&quot; &amp; Chr(13) &amp; &quot;&quot; &amp; Chr(10) &amp; &quot;    #&quot;&quot;Changed Type&quot;&quot; = Table.TransformColumnTypes(Table001,{{&quot;&quot;Column1&quot;&quot;, type text}, {&quot;&quot;Column2&quot;&quot;, type text}, {&quot;&quot;Column3&quot;&quot;, type text}, {&quot;&quot;Column4&quot;&quot;, type text}, {&quot;&quot;Column&quot; &amp; _<br>        &quot;5&quot;&quot;, type text}, {&quot;&quot;Column6&quot;&quot;, type text}, {&quot;&quot;Column7&quot;&quot;, type text}})&quot; &amp; Chr(13) &amp; &quot;&quot; &amp; Chr(10) &amp; &quot;in&quot; &amp; Chr(13) &amp; &quot;&quot; &amp; Chr(10) &amp; &quot;    #&quot;&quot;Changed Type&quot;&quot;&quot;<br><br>    &#39; Create a new worksheet and import data into it<br>    ActiveWorkbook.Worksheets.Add<br>    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _<br>        &quot;OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=&quot;&quot;Table001 (Page 1)&quot;&quot;;Extended Properties=&quot;&quot;&quot;&quot;&quot; _<br>        , Destination:=Range(&quot;$A$1&quot;)).QueryTable<br>        .CommandType = xlCmdSql<br>        .CommandText = Array(&quot;SELECT * FROM [Table001 (Page 1)]&quot;)<br>        .RowNumbers = False<br>        .FillAdjacentFormulas = False<br>        .PreserveFormatting = True<br>        .RefreshOnFileOpen = False<br>        .BackgroundQuery = True<br>        .RefreshStyle = xlInsertDeleteCells<br>        .SavePassword = False<br>        .SaveData = True<br>        .AdjustColumnWidth = True<br>        .RefreshPeriod = 0<br>        .PreserveColumnInfo = True<br>        .ListObject.DisplayName = &quot;Table001__Page_1&quot;<br>        .Refresh BackgroundQuery:=False<br>    End With<br><br>    &#39; Display a message box<br>    MsgBox &quot;Hello from Python!&quot;<br>End Sub</pre><p>Overall, the Python function to transfer a structured PDF into an Excel is:</p><pre>def process_pdf_unstructured(self, pdf_file_path):<br>    # Start Excel in the background<br>    app = xw.App(visible=False)<br><br>    # Create a new workbook<br>    wb = app.books.add()<br><br>    macro_code_template = &#39;&#39;&#39;<br>    Sub getData()<br>        Dim pdfFilePath As String<br>        pdfFilePath = &quot;{}&quot;<br><br>        ActiveWorkbook.Queries.Add Name:=&quot;Table001 (Page 1)&quot;, Formula:= _<br>            &quot;let&quot; &amp; Chr(13) &amp; &quot;&quot; &amp; Chr(10) &amp; &quot;    Source = Pdf.Tables(File.Contents(&quot;&quot;&quot; &amp; pdfFilePath &amp; &quot;&quot;&quot;), [Implementation=&quot;&quot;1.3&quot;&quot;]),&quot; &amp; Chr(13) &amp; &quot;&quot; &amp; Chr(10) &amp; &quot;    Table001 = Source{[Id=&quot;&quot;Table001&quot;&quot;]}[Data],&quot; &amp; Chr(13) &amp; &quot;&quot; &amp; Chr(10) &amp; &quot;    #&quot;&quot;Changed Type&quot;&quot; = Table.TransformColumnTypes(Table001,{{&quot;&quot;Column1&quot;&quot;, type text}, {&quot;&quot;Column2&quot;&quot;, type text}, {&quot;&quot;Column3&quot;&quot;, type text}, {&quot;&quot;Column4&quot;&quot;, type text}, {&quot;&quot;Column&quot; &amp; _<br>            &quot;5&quot;&quot;, type text}, {&quot;&quot;Column6&quot;&quot;, type text}, {&quot;&quot;Column7&quot;&quot;, type text}})&quot; &amp; Chr(13) &amp; &quot;&quot; &amp; Chr(10) &amp; &quot;in&quot; &amp; Chr(13) &amp; &quot;&quot; &amp; Chr(10) &amp; &quot;    #&quot;&quot;Changed Type&quot;&quot;&quot;<br>        ActiveWorkbook.Worksheets.Add<br>        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _<br>            &quot;OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=&quot;&quot;Table001 (Page 1)&quot;&quot;;Extended Properties=&quot;&quot;&quot;&quot;&quot; _<br>            , Destination:=Range(&quot;$A$1&quot;)).QueryTable<br>            .CommandType = xlCmdSql<br>            .CommandText = Array(&quot;SELECT * FROM [Table001 (Page 1)]&quot;)<br>            .RowNumbers = False<br>            .FillAdjacentFormulas = False<br>            .PreserveFormatting = True<br>            .RefreshOnFileOpen = False<br>            .BackgroundQuery = True<br>            .RefreshStyle = xlInsertDeleteCells<br>            .SavePassword = False<br>            .SaveData = True<br>            .AdjustColumnWidth = True<br>            .RefreshPeriod = 0<br>            .PreserveColumnInfo = True<br>            .ListObject.DisplayName = &quot;Table001__Page_1&quot;<br>            .Refresh BackgroundQuery:=False<br>        End With<br>        MsgBox &quot;Hello from Python!&quot;<br>    End Sub<br>    &#39;&#39;&#39;<br>    # Fill in the placeholder with the actual file path<br>    macro_code = macro_code_template.replace(&#39;{}&#39;, pdf_file_path)<br><br>    # Add a VBA module to the workbook and write the macro code to it<br>    wb_vba = wb.api.VBProject.VBComponents.Add(1)  # 1 means vbext_ct_StdModule, which is a standard VBA module<br>    wb_vba.CodeModule.AddFromString(macro_code)<br><br>    # Run the macro if it exists<br>    if wb.macro(&#39;getData&#39;) is not None:<br>        wb.macro(&#39;getData&#39;)()<br><br>    # Generate the file name for the resulting workbook<br>    base_name = os.path.basename(pdf_file_path)<br>    file_name = f&quot;{base_name}output.xlsx&quot;<br>    target_directory = r&quot;YOUR\OUTPUT\DIRECTORY&quot;<br>    excel_file_path = os.path.join(target_directory, file_name)<br><br>    # Save the workbook to the target directory<br>    wb.save(excel_file_path)<br><br>    # Close the workbook<br>    wb.close()<br><br>    # Quit Excel<br>    app.quit()<br><br>    return excel_file_path</pre><p>Throughout this article, we streamlined the extraction of financial data from PDFs, whether they’re structured or unstructured. We categorized financial PDFs into two main types: structured and unstructured. In doing so, we elucidated the distinction between them, highlighting how structured PDFs have a clear and organized layout that facilitates easy comprehension of the interrelation between different elements. By focusing on this clarity, we were able to devise methods to efficiently extract data, ensuring that elements pertaining to the same record are neatly grouped together for seamless integration into Excel.</p><blockquote>PS to see more of my articles, you can follow me on LinkedIn here: <a href="https://www.linkedin.com/in/sasha-korovkina-5b992019b/">https://www.linkedin.com/in/sasha-korovkina-5b992019b/</a></blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c54377a948db" width="1" height="1" alt=""><hr><p><a href="https://medium.com/codex/complex-financial-pdf-documents-to-excel-a-case-study-c54377a948db">Complex Financial PDF Documents to Excel. A Case Study.</a> was originally published in <a href="https://medium.com/codex">CodeX</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Server’s Secret Feature — Run Python and Add-Ons Natively In SQL Server.]]></title>
            <link>https://medium.com/data-science/sql-servers-secret-feature-run-python-and-add-ons-natively-in-sql-server-7f3c4efe5c00?source=rss-af2804de7fa7------2</link>
            <guid isPermaLink="false">https://medium.com/p/7f3c4efe5c00</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Sasha Korovkina]]></dc:creator>
            <pubDate>Wed, 15 May 2024 16:56:03 GMT</pubDate>
            <atom:updated>2024-05-15T16:56:03.172Z</atom:updated>
            <content:encoded><![CDATA[<h3>SQL Server’s Secret Feature — Run Python and Add-Ons Natively In SQL Server</h3><h4>Import Python libraries, manipulate and output SQL tables and more, all without leaving SQL server.</h4><h3>The Problem</h3><p>Within this project, we confront the challenge of managing 37,000 company names sourced from two distinct origins. The complexity lies in the potential discrepancy between how identical companies are listed across these sources.</p><h3>The goal</h3><p>The goal of this article is to teach you to run Python natively within Microsoft SQL server. To use add-ons and external libraries, as well as perform further processing on the resulting tables with SQL.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*_zZKk7cPes8UaiJ_" /><figcaption>Photo by <a href="https://unsplash.com/@christinhumephoto?utm_source=medium&amp;utm_medium=referral">Christin Hume</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h3>Initial Algorithm Build</h3><p>Here is the strategy I will follow when building the algorithms:</p><ol><li><strong>Blocking </strong>— Dividing datasets into smaller blocks or groups based on common attributes to reduce computational complexity in comparing records. It narrows down the search space and enhances efficiency in similarity search tasks.</li><li><strong>Pre-processing</strong> — Cleaning and standardizing raw data to prepare it for analysis by tasks like lowercase conversion, punctuation removal, and stop word elimination. This step improves data quality and reduces noise.</li><li><strong>Similarity search model application</strong> — Applying models to compute similarity or distance between pairs of records based on tokenized representations. This helps identify similar pairs, using metrics like cosine similarity or edit distance, for tasks like record linkage or deduplication.</li></ol><h4>Blocking</h4><p>My datasets are highly disproportional — I have 1,361,373 entities in one table and only 37,171 company names in the second table. If I attempt to match on the unprocessed table, the algorithm would take a very long time to do so.</p><p>In order to block the tables, we need to see what common characteristics there are between 2 datasets. In my case, the companies are all associated with internal projects. Hence I will do the following:</p><ol><li>Extract the distinct company name and project code from the smaller table.</li><li>Loop through the project codes and try to find them in the larger table.</li><li>Map all of the funds for that project and take it out of the large table.</li><li><em>Repeat for the next project!</em></li></ol><p>This way, I will be reducing the large dataset with each iteration, while also making sure that the mapping is rapid due to a smaller, filtered dataset on the project level.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/e09c04e90e374c8c7c7c8ae5ea0dced5/href">https://medium.com/media/e09c04e90e374c8c7c7c8ae5ea0dced5/href</a></iframe><p>Now, I will filter both tables by the project code, like so:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/9cc8d9c2895c5b2be0bfe3902c0f683c/href">https://medium.com/media/9cc8d9c2895c5b2be0bfe3902c0f683c/href</a></iframe><p>With this approach, our small table only has 406 rows for project ‘ABC’ for us to map, while the big table has 15,973 rows for us to map against. This is a big reduction from the raw table.</p><h4>Program Structure</h4><p>This project will consist of both Python and SQL functions on SQL server; here is a quick sketch of how the program will work to have a clearer understanding of each step:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/289/1*kH76z2PDS3v8k9UqpTSMlw.png" /><figcaption>Program structure. Image created by author.</figcaption></figure><p>Program execution:</p><ul><li>Printing the project code in a loop is the simplest version of this function:</li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/be9b39c5f263b8b5a1384d9a3b76b49f/href">https://medium.com/media/be9b39c5f263b8b5a1384d9a3b76b49f/href</a></iframe><p>It quickly becomes apparent that the SQL cursor uses up too many resources. In short, this happens because cursors operate at row level and go through every row to make an operation.</p><blockquote>More information on why cursors in SQL are inefficient and it is best to avoid them can be found here: <a href="https://stackoverflow.com/questions/4568464/sql-server-temporary-tables-vs-cursors">https://stackoverflow.com/questions/4568464/sql-server-temporary-tables-vs-cursors</a> (answer 2)</blockquote><p>To increase the performance, I will use temporary tables and remove the cursor. Here is the resulting function:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/7ab04df12210c40cc9a69f4f9f4c8c3e/href">https://medium.com/media/7ab04df12210c40cc9a69f4f9f4c8c3e/href</a></iframe><p>This now takes about 3 seconds per project to select the project code and the data from the large mapping table, filtered by that project.</p><p>For demonstration purposes, I will only focus on 2 projects, however I will return to running the function on all projects when doing so on production.</p><p>The final function we will be working with looks like this:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/85868ab26148b0177bc99c8d76f77fc1/href">https://medium.com/media/85868ab26148b0177bc99c8d76f77fc1/href</a></iframe><h4>Mapping Table Preparation</h4><p>The next step is to prepare the data for the Python pre-processing and mapping functions, for this we will need 2 datasets:</p><ol><li>The filtered data by project code from the large mapping table</li><li>The filtered data by project code from the small companies table</li></ol><p>Here is what the updated function looks like with the data from 2 tables being selected:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/f71795b61397d18eb4616a4d0e1b7ae7/href">https://medium.com/media/f71795b61397d18eb4616a4d0e1b7ae7/href</a></iframe><blockquote>Important: pythonic functions in SQL only take in <strong>1 table input.</strong> Make sure to put your data into a <strong>single wide table </strong>before feeding it into a Python function in SQL.</blockquote><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/b894d90f4c10da4d17559e3a32bb0dc4/href">https://medium.com/media/b894d90f4c10da4d17559e3a32bb0dc4/href</a></iframe><p>As a result of this function, we get the projects, the company names and the sources for each project.</p><p><em>Now we are ready for Python!</em></p><h3>Python Execution in SQL</h3><p>Python in SQL Server, through sp_execute_external_script, allows you to run Python code directly within SQL Server.</p><p>It enables integration of Python&#39;s capabilities into SQL workflows with data exchange between SQL and Python. In the provided example, a Python script is executed, creating a pandas DataFrame from input data.</p><p>The result is returned as a single output.</p><p><em>How cool is that!</em></p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/d522a8b34d0fcfce26038f580e5c5b87/href">https://medium.com/media/d522a8b34d0fcfce26038f580e5c5b87/href</a></iframe><p>There are a few important things to note about running Python in SQL:</p><ol><li>Strings are defined by double quotes (“), not single quotes (‘). Make sure to check this especially if you are using regex expressions, to avoid spending time on error tracing</li><li>There is only 1 output permitted — so your Python code will result in 1 table on output</li><li>You can use print statements for debugging and see the results be printed to the ‘Messages’ tab within your SQL server. Like so:</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/615/1*3wuwZpliYbGn-mOc0puw4g.png" /><figcaption>Image created by author.</figcaption></figure><h4>Python Libraries In SQL</h4><p>In SQL Server, several libraries come pre-installed and are readily accessible. To view the complete list of these libraries, you can execute the following command:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/6dadbdd45a33aaa580323ce0173063f9/href">https://medium.com/media/6dadbdd45a33aaa580323ce0173063f9/href</a></iframe><p>Here is what the output will look like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/223/1*bEgcASNw4e5aZRfVTDz3zA.png" /><figcaption>You can import these packages just as you would do in a normal Python script (import …). Image created by author.</figcaption></figure><h3>Matching Text With Python</h3><p>Coming back to our generated table, we can now match the company names from different sources using Python. Our Python procedure will take in the long table and output a table with the mapped entities. It should show the match it thinks is most likely from the large mapping table next to each record from the small company table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wuicmN_zX47dXYWvu3SC-g.png" /><figcaption>Assuming that Company 1.1 is the closest match to Company 1, the output should look like the output above. Image created by author.</figcaption></figure><p>To do this, let’s first add a Python function to our SQL procedure. The first step is to simply feed in the dataset into Python, I will do this with a sample dataset and then with our data, here is the code:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/17376f92eaa35aff589b14dcca9bbb04/href">https://medium.com/media/17376f92eaa35aff589b14dcca9bbb04/href</a></iframe><p>This system allows us to feed in both of our tables into the pythonic function as inputs, it then prints both tables as outputs.</p><h4>Pre-Processing In Python</h4><p>In order to match our strings effectively, we must conduct some preprocessing in Python, this includes:</p><ol><li>Remove accents and other language-specific special characters</li><li>Remove the white spaces</li><li>Remove punctuation</li></ol><p>The first step will be done with collation in SQL, while the other 2 will be present in the preprocessing step of the Python function.</p><p>Here is what our function with preprocessing looks like:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/a2f0b600486401cf66223759fbf16e19/href">https://medium.com/media/a2f0b600486401cf66223759fbf16e19/href</a></iframe><p>The result of this is 3 columns, one with the name of the company in small, lower cap and no space letters, the second column is the project column and the third column is the source.</p><h4>Matching Strings In Python</h4><p>Here we have to be creative as we are pretty limited with the number of libraries which we can use. Therefore, let’s first identify how we would want our output to look.</p><p>We want to match the data coming from source 2, to the data in source 1. Therefore, for each value in source 2, we should have a bunch of matching values from source 1 with scores to represent the closeness of the match.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/373/1*qnL4HVNI3G5cWEPSy7qLJw.png" /><figcaption>Output table structure. Image created by author.</figcaption></figure><p>We will use <strong>python built-in libraries </strong>first, to avoid the need for library imports and hence simplify the job.</p><p>The logic:</p><ol><li>Loop through each project</li><li>Make a table with the funds by source, where source 1 is the large table with the mapping data and 2 is the initial company dataset</li><li>Select the data from the small dataset into an array</li><li>Compare each element in the resulting array to each element in the large mapping data frame</li><li>Return the scores for each entity</li></ol><p>The code:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/c072a2e6d12a7ddb04b78fc7df415f10/href">https://medium.com/media/c072a2e6d12a7ddb04b78fc7df415f10/href</a></iframe><p>And here is the final output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/432/1*2uDZAK0_-uq2F_8r0oUmlw.png" /><figcaption>This is made-up data to demonstrate the result, however the structure should be identical for your dataset. Image generated by author.</figcaption></figure><p>In this table, we have each company name, the project which it belongs to and the source — whether it is from the large mapping table or the small companies table. The score on the right indicates the similarity metric between the company name from source 2 and source 1. It is important to note that company4, which came from source 2, will always have a score of 1–100% match, as it is being matched against itself.</p><p>Executing Python scripts within SQL Server via the Machine Learning Services is a powerful feature that allows for in-database analytics and machine learning tasks. This integration enables direct data access without the need for data movement, significantly optimizing performance and security for data-intensive operations.</p><p>However, there are limitations to be aware of. The environment supports a <strong>single input</strong>, which might restrict the complexity of tasks that can be performed directly within the SQL context. Additionally, only a <strong>limited set of Python libraries are available</strong>, which may require alternative solutions for certain types of data analysis or machine learning tasks not supported by the default libraries. Furthermore, users must navigate the intricacies of SQL Server’s environment, such as complex spacing in T-SQL queries that include Python code, which can be a source of errors and confusion.</p><p>Despite these challenges, there are numerous applications where executing Python in SQL Server is advantageous:</p><p>1. <strong>Data Cleansing and Transformation — </strong>Python can be used directly in SQL Server to perform complex data preprocessing tasks, like handling missing data or normalizing values, before further analysis or reporting.</p><p>2. <strong>Predictive Analytics </strong>— Deploying Python machine learning models directly within SQL Server allows for real-time predictions, such as customer churn or sales forecasting, using live database data.</p><p>3. <strong>Advanced Analytics </strong>— Python’s capabilities can be leveraged to perform sophisticated statistical analysis and data mining directly on the database, aiding in decision-making processes without the latency of data transfer.</p><p>4. <strong>Automated Reporting and Visualization</strong> — Python scripts can generate data visualizations and reports directly from SQL Server data, enabling automated updates and dashboards.</p><p>5. <strong>Operationalizing Machine Learning Models </strong>— By integrating Python in SQL Server, models can be updated and managed directly within the database environment, simplifying the operational workflow.</p><p>In conclusion, while the execution of Python in SQL Server presents some challenges, it also opens up a wealth of possibilities for enhancing and simplifying data processing, analysis, and predictive modeling directly within the database environment.</p><blockquote>PS to see more of my articles, you can follow me on LinkedIn here: <a href="https://www.linkedin.com/in/sasha-korovkina-5b992019b/">https://www.linkedin.com/in/sasha-korovkina-5b992019b/</a></blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7f3c4efe5c00" width="1" height="1" alt=""><hr><p><a href="https://medium.com/data-science/sql-servers-secret-feature-run-python-and-add-ons-natively-in-sql-server-7f3c4efe5c00">SQL Server’s Secret Feature — Run Python and Add-Ons Natively In SQL Server.</a> was originally published in <a href="https://medium.com/data-science">TDS Archive</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Get Underlined Text from Any PDF with Python]]></title>
            <link>https://medium.com/data-science/get-underlined-text-from-any-pdf-b7b068ca3d64?source=rss-af2804de7fa7------2</link>
            <guid isPermaLink="false">https://medium.com/p/b7b068ca3d64</guid>
            <category><![CDATA[pdf]]></category>
            <category><![CDATA[developer]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[development]]></category>
            <category><![CDATA[finance]]></category>
            <dc:creator><![CDATA[Sasha Korovkina]]></dc:creator>
            <pubDate>Fri, 03 May 2024 08:43:14 GMT</pubDate>
            <atom:updated>2024-05-03T15:26:51.135Z</atom:updated>
            <content:encoded><![CDATA[<h4>A step-by-step guide to get underlined text as an array from PDF files.</h4><blockquote><em>💡 If you want to see the code for this project, check out my repository: </em><a href="https://github.com/sasha-korovkina/pdfUnderlinedExtractor"><strong><em>https://github.com/sasha-korovkina/pdfUnderlinedExtractor</em></strong></a></blockquote><p>PDF data extraction can be a real headache, and it gets even trickier when you’re trying to snag underlined text — believe it or not, there aren’t any go-to solutions or libraries that handle this out of the box. But don’t worry, I’m here to show you how to tackle this.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*noNGMVqtffJeQlo1" /><figcaption>Photo by <a href="https://unsplash.com/@dlxmedia?utm_source=medium&amp;utm_medium=referral">dlxmedia.hu</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h4>The Theory</h4><p>Extracting underlined text from PDFs can take a few different paths. You might consider using OCR to detect text components with bottom lines or delve into PyMuPDF’s markup capabilities. However, I’ve found that OCR tends to falter, suffering from inconsistency and low accuracy. PyMuPDF isn’t my favorite either — it demands finicky parameter tuning, which is time-consuming. Plus, one wrong setting and you could lose a bunch of data.</p><p>It is important to remember that PDFs are:</p><ul><li><strong>Non-Structured Data: </strong>PDF elements often lack grouping or categorization, which complicates efforts to search through the content systematically.</li><li><strong>Text Formatting Recognition: </strong>Detecting specific text formats such as bold or underlined is notoriously difficult in PDFs, as most Python libraries do not support this capability effectively.</li></ul><p>But fear not, as we have a strategy to resolve this.</p><h4>The Strategy</h4><ul><li>Convert the PDF to Structured XML: Start by transforming the PDF document into a structured XML format to facilitate easier data manipulation.</li><li>Extract Desired Components: Identify and isolate the specific components from the XML that are relevant to our needs.</li><li>Use OCR (Optical Character Recognition) on the extracted coordinates to get the underlined text data as an array.</li><li>Extract and Output Underlined Text: Finally, extract the underlined text from the document and display or print the results.</li></ul><h4>The Code</h4><ol><li><strong>PDF to XML</strong></li></ol><p>We will use the <strong>pdfquery</strong> library, the most comprehensive PDF to XML converter which I have come across.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/1ea87a66c18c66d1b2db6480fefe8534/href">https://medium.com/media/1ea87a66c18c66d1b2db6480fefe8534/href</a></iframe><p>2. <strong>Studying the XML</strong></p><p>The XML has a few key components which we are interested in:</p><ul><li><em>LTRect — </em>sometimes, the library would parse the underlined text as a rectangle of minimal width under the text</li><li><em>LTLine — </em>other times, it would recognise the outline as a separate line component.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3mp8b9M9D-BmUm8LuEMa5g.png" /><figcaption>This is what your output XML will look like. Image created by author.</figcaption></figure><p>LTRect component example:</p><pre>&lt;LTRect y0=&quot;563.787&quot; y1=&quot;629.964&quot; x0=&quot;367.942&quot; x1=&quot;473.826&quot; width=&quot;105.884&quot; height=&quot;66.178&quot; bbox=&quot;[367.942, 563.787, 473.826, 629.964]&quot; linewidth=&quot;0&quot; pts=&quot;[[367.942, 629.964], [473.826, 629.964], [473.826, 563.787], [367.942, 563.787]]&quot;&gt;</pre><p>Therefore, by converting the whole document into XML format, we can replicate it’s structure as XML components, let’s do just that!</p><h4>Structure Replication</h4><p>Now, we will re-create the structure of our document as bounding box coordinates. To do this, we will parse the XML to define the page, component boxes, lines and rectangles, and then draw them all on our canvas in 3 different colors.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/6619b4a47185a25766d8a7f378ec6081/href">https://medium.com/media/6619b4a47185a25766d8a7f378ec6081/href</a></iframe><p>Here is our inital PDF, it has been generated in Microsoft Word, by exporting a document with some underlines to the PDF file format:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/987/1*wp1CPBaNTk4xZtoHK47mSA.png" /><figcaption>Initial document with sample text. Image created by author.</figcaption></figure><p>After applying the algorithm above, here is the visual representation we get:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/990/1*llAAurT_zoDcSPf3xHQi6Q.png" /><figcaption>The box outline of the document Black — all document components, blue — underlined text. Image created by author.</figcaption></figure><p>This image represents the structure of our document, where the black box is used to describe all components on the page, and the blue is used to describe the LTRect elements, hence the underlined text.</p><h4>Text Overlay</h4><p>Now, let’s visualize all of the text within the PDF in it’s respective positions, with the following line of code:</p><pre>can.drawString(text_x, text_y, text)</pre><p>Here is the output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/988/1*tgp2Vz1O-cB8_bNqIKOFvw.png" /><figcaption>PDF re-creation based on text location and underlines. Image created by author.</figcaption></figure><blockquote>Note that the text is not exactly where it was in the original document, due to the difference in size and font of the mark-up language in the pdfquery library.</blockquote><h4>Co-Ordinate Extraction</h4><p>As the result of our XML, we will have an array of coordinates of underlined regions, in my case I have called it underline_text.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/d5727a31b4627e5f780c4f06fb986d2d/href">https://medium.com/media/d5727a31b4627e5f780c4f06fb986d2d/href</a></iframe><h4>Text Extraction</h4><p>Here’s the process:</p><ol><li>We identify the coordinate rectangles as previously determined.</li><li>We extract these sections from the PDF.</li><li>We apply Tesseract OCR to extract text from each extracted section.</li></ol><p>This method of extracting text from PDFs using coordinate rectangles and Tesseract OCR is effective for several reasons:</p><ol><li><strong>Precision in Text Extraction</strong>: By identifying specific coordinate rectangles, the process targets only relevant areas of the PDF. This focused approach avoids unnecessary processing of the entire document and reduces errors related to extracting unwanted text.</li><li><strong>Efficiency</strong>: Extracting predefined sections directly from the PDF is much faster than processing the entire document. This method saves computational resources and time, particularly useful when dealing with large documents.</li><li><strong>Accuracy with OCR</strong>: Tesseract OCR is a robust optical character recognition tool that can convert images of text into machine-readable text. By feeding it precise sections of text, it can perform more accurately as it deals with less background noise and formatting issues that might confuse the OCR process in larger, unsegmented documents.</li></ol><p>And this is the code:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/fb8a7bb0744e5165e6dee7a87b40d101/href">https://medium.com/media/fb8a7bb0744e5165e6dee7a87b40d101/href</a></iframe><p>Make sure that you have tesseract installed on your system before running this function. For in-depth instructions, check out their official installation guide here: <a href="https://github.com/tesseract-ocr/tessdoc/blob/main/Installation.md">https://github.com/tesseract-ocr/tessdoc/blob/main/Installation.md</a> or in my GitHub repository here: <a href="https://github.com/sasha-korovkina/pdfUnderlinedExtractor">https://github.com/sasha-korovkina/pdfUnderlinedExtractor</a>.</p><h4>Putting It All Together…</h4><p>Now, If we take any PDF file, like this example file:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/448/1*feaBa1c-zF67Q1fjo8GrLg.png" /><figcaption>The whole text of the test file. Image created by author.</figcaption></figure><p>We have some underlined words in this file:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/544/1*BHNIFImYaEzEFtOGBojWsg.png" /><figcaption>ipsum and laboris are underlined here. Image created by author.</figcaption></figure><p>After running the code described above, here is what we get:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*C0KvBgedXabsoLtxPhxyJw.png" /><figcaption>An array of all underlined words in the document. Image created by author.</figcaption></figure><p>After getting this array, you can use these words for further processing!</p><blockquote>Enjoy using this script! I’d love to hear about any creative applications you come up with or if you’d like to contribute. Let me know! ❤️</blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b7b068ca3d64" width="1" height="1" alt=""><hr><p><a href="https://medium.com/data-science/get-underlined-text-from-any-pdf-b7b068ca3d64">Get Underlined Text from Any PDF with Python</a> was originally published in <a href="https://medium.com/data-science">TDS Archive</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building a High Precision Financial PDF Extraction Tool. Part 1.]]></title>
            <link>https://blog.devgenius.io/building-a-high-precision-financial-pdf-extraction-tool-part-1-c6db2073eeee?source=rss-af2804de7fa7------2</link>
            <guid isPermaLink="false">https://medium.com/p/c6db2073eeee</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[development]]></category>
            <category><![CDATA[money]]></category>
            <category><![CDATA[pdf]]></category>
            <category><![CDATA[finance]]></category>
            <dc:creator><![CDATA[Sasha Korovkina]]></dc:creator>
            <pubDate>Mon, 29 Apr 2024 14:55:05 GMT</pubDate>
            <atom:updated>2024-04-30T15:04:22.603Z</atom:updated>
            <content:encoded><![CDATA[<h3>Parsing Text from PDF Files</h3><h4>Classification</h4><p>PDFs can be:</p><ul><li>“True” PDFs are generated through software like Microsoft Word or Excel, or by using a virtual printer function, containing both text and images.</li><li>“Image-only” PDFs result from scanning physical documents or converting images (e.g., JPG, TIFF) into PDFs, effectively creating a fixed image of the content.</li><li>Searchable PDFs are developed by applying Optical Character Recognition (OCR) to image-based documents, which analyzes and reads the characters and structure, adding a searchable text layer beneath the image. This makes the documents appear similar to their originals and enables text selection, copying, and markup.</li></ul><p>Here is an example of the layers in a searchable PDF:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/654/0*RkjyFrTLdSpHGqTB.png" /><figcaption>Credit: <a href="https://pdf.abbyy.com/learning-center/pdf-types/">https://pdf.abbyy.com/learning-center/pdf-types/</a></figcaption></figure><h4>Classifying PDFs With Python</h4><p>Let’s build a Python algorithm which would classify the PDFs into the segments above.</p><p>We will be comparing the area of text on the page to the area of the image on the page, and then determine which area dominates. This is the idea case scenario, where the PDF is either 100% text or 100% image. We can alter the threshold to accommodate for the values in between.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/ecca4faa4a04c9221faabcfd11ca44b4/href">https://medium.com/media/ecca4faa4a04c9221faabcfd11ca44b4/href</a></iframe><h4><strong>Basic Classification</strong></h4><p>The classifier_func function opens a PDF file, iterates through each page to check for any text content, and returns True as soon as text is found on any page, or False if no text is found throughout the document.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/574/1*0Oe4Miz_GmkXCtR0VaP6rQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/570/1*eydHbsZb8q6gFZWkG6nRlA.png" /><figcaption>The 2 PDFs look very similar, yet the one on the left was generated through banking software, while the on on the right was made by scanning a file at a very low resolution — 200dpi and converted into a PDF format.</figcaption></figure><p>The 2 PDFs look very similar, yet the one on the left was generated through banking software, while the on on the right was made by scanning a file at a very low resolution — 200dpi and converted into a PDF format.</p><p>This is seen in the results:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/7452e92b30980bf9a7d44609888f0de6/href">https://medium.com/media/7452e92b30980bf9a7d44609888f0de6/href</a></iframe><h4><strong>Advanced Classification</strong></h4><p>The type of PDF document determines the processing library we use. Therefore, we will develop a method to measure the ratio of text to images within the PDF, enabling us to establish an appropriate threshold for this parameter</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/6d0212c7420898c46978d7d7aab3ba58/href">https://medium.com/media/6d0212c7420898c46978d7d7aab3ba58/href</a></iframe><p>Function description:</p><ol><li>Initialize Area Counters: It initializes variables to zero for tracking the total areas of images, text, and the overall page area within the entire document.</li><li>Page Area Calculation: It calculates the absolute area of the page (page.rect) and accumulates this into total_page_area.</li><li>Image Processing: Retrieves a list of all images on the page, extracts their cross-reference IDs (xref), and calculates the area of each image’s bounding rectangle. These areas are summed into total_image_area.</li><li>Text Block Processing: Gathers all text blocks on the page, computes the area of each block’s bounding rectangle, and sums these to total_text_area.</li><li>Calculate Coverage Percentages: After processing all pages, it calculates the percentage of the total page area covered by text and images respectively.</li><li>Compute Text-to-Image Ratio: It computes the ratio of the text coverage percentage to the image coverage percentage. If no images are present (to avoid division by zero), it sets the ratio to infinity, indicating that the text exclusively covers the page area.</li></ol><h4><strong>Advanced Classification Test</strong></h4><p>Coming back to the PDFs above, here is what we get for the scanned file:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/388/1*_h_xsuiamexNQHCbdjDeEw.png" /><figcaption>This is the result for the scanned, image file.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/372/1*JQQCKq55DwIt-ldfwdiepQ.png" /><figcaption>This is what we get for the embedded text PDF.</figcaption></figure><p>Now, our algorithm can tell us not only if the PDF has searchble text, but also let us know the ratio of this text to the image.</p><h3>Identifying Components</h3><p>Here are some components that can be in our PDF file that would need to be processed separately:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/988/0*3Lf5eWOMG2QAUQFI.jpeg" /><figcaption>Credits to: <a href="https://www.analyticsvidhya.com/blog/2021/06/data-extraction-from-unstructured-pdfs/">https://www.analyticsvidhya.com/blog/2021/06/data-extraction-from-unstructured-pdfs/</a></figcaption></figure><ol><li>Text: Characters and words that can be selected, searched, and copied. Text in PDFs can be stored as simple text or as text in drawing commands.</li><li>Images: Raster graphics (such as photographs) and vector graphics (such as logos and scalable illustrations).</li><li>Tables: Structured data presented in grid form. Although visually recognizable, tables aren’t always tagged as separate elements, which can make them difficult to extract.</li><li>Annotations: Comments, notes, highlights, or other types of marks that do not necessarily affect the underlying content.</li><li>Hyperlinks: Links within the text that can point to other pages within the document, to external documents, or to websites.</li><li>Digital Signatures: Security feature for authenticating the identity of the creator of the document and to ensure that the document has not been altered after it was signed.</li></ol><p>These are just some examples of elements that can be found in a PDF file. So let’s use and compare a few Python libraries to find and label up these elements.</p><h4>Layout Extraction</h4><p>We will try the following libraries for layout extraction:</p><p><em>Cool custom method: </em><a href="https://www.posos.co/blog-articles/how-to-extract-and-structure-text-from-pdf-files-with-python-and-machine-learning">https://www.posos.co/blog-articles/how-to-extract-and-structure-text-from-pdf-files-with-python-and-machine-learning</a></p><p>Let’s experiment with different library combinations:</p><ol><li>layoutparser</li><li>PDFQuery</li></ol><p>If you are on Windows <strong>read the part below </strong>(I am on Windows 11 Pro), otherwise it will not work<strong>.</strong> If you are on Linux, you can ignore it.</p><h4>layoutparser — IMPORTANT FOR WINDOWS Only!</h4><p>Most libraries on the list above depend on a utility called Poppler. When installing layoutparser on your local system, you are very likely to get the error:</p><p>The poppler is not installed or is not in PATH.</p><p>This is because poppler was primarily developed for Linux users and is not natively supported on Windows. However, there is a workaround — we will use WSL — Windows Subsystem Linux.</p><blockquote>Go check out Matthew’s comprehensive guide which I found super helpful in installing WSL on my local system: <a href="https://towardsdatascience.com/poppler-on-windows-179af0e50150">https://towardsdatascience.com/poppler-on-windows-179af0e50150</a>.</blockquote><p>Here is how WSL works:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/968/0*tvLvpmlDZJNPbO_P.png" /><figcaption>This is a visualization of how WSL works in Windows + VSCode, the same principles go for any IDE (integrated development environment), I am using Pycharm for example.</figcaption></figure><p>In simple terms:</p><ol><li>Windows and Linux store files in different ways, which is why we have a separate file system for each.</li><li><strong>Volume Mount (/mnt/c): </strong>This is a shared space that allows both Windows and WSL to access the same files. When you see /mnt/c, it usually means that WSL can access the <strong>C: drive on Windows</strong>, allowing you to work with the same files from both Windows and Linux.</li><li><strong>VS Code Server inside WSL: </strong>This is a special version of VS Code that runs inside the Linux environment provided by WSL. It’s like a helper for the main VS Code on Windows.</li><li><strong>Workspace Extension:</strong> These are extensions that run in the context of the project you’re working on (the workspace). They’re running inside WSL, so they can work closely with Linux tools and the Linux file system.</li><li><strong>Exposed Port:</strong> VS Code on Windows communicates with VS Code Server inside WSL over a network connection, similar to how websites communicate with your browser. An “exposed port” is like a door that allows this communication to happen securely.</li></ol><h3>Installation Instructions</h3><p>These intallation instructions are applicable to Windows only and are a workaround the lack of support for poppler. If you are on UNIX or Linux, you can check out this page: <a href="https://github.com/Layout-Parser/layout-parser/blob/main/installation.md">https://github.com/Layout-Parser/layout-parser/blob/main/installation.md</a> for an easy layoutparser installation.</p><p>Assuming that you have installed WSL, go to your command line, then go to Ubuntu and ‘Run as Administrator’</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gGmdbGi6ruKIuDJRGmkteQ.png" /><figcaption>Right click on ‘Ubuntu’ and run it as the admin. You should see a red terminal, like the one below.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eoQEAmZwy1KnJLXDNPJKLg.png" /><figcaption>If you have everything installed correctly, you will see this screen with your username.</figcaption></figure><h3>Setting Up Your Python Environment for the Project</h3><ol><li>Create a Virtual Environment (If you don’t have one) Open your terminal and navigate to your project directory:</li></ol><pre>cd /mnt/c/Users/sasha/projects<br>python3 -m venv venv</pre><p>2. Activate the Virtual Environment: If you already have a virtual environment, activate it:</p><pre>source venv/bin/activate</pre><p>Make sure that your resulting directory looks like:<strong> (venv) sasha@Sasha:~/.torch/iopath_cache/s/iopath$, </strong>with your username, project path and venv name in brackets.</p><p>3. If you have a script which requries poppler, try to run it here. If you don’t ignore this step:</p><pre>python3 /mnt/c/Users/sasha/projects/pdfTest/main.py</pre><p>4. <em>(Optional)</em> If you find that builder tools are missing and it is not a pip error:</p><pre>sudo apt install build-essential python3-dev</pre><p>5. Install detectron:</p><pre>python -m pip install &#39;git+https://github.com/facebookresearch/detectron2.git&#39;</pre><p>6. If you face the ‘model not found’ or ‘model checkpoint not found’ errors:</p><pre>git clone https://github.com/facebookresearch/iopath - single-branch - branch v0.1.8<br>cd common<br>sed -i &#39;s/filename = path.split(&quot;\/&quot;)\[-1\]/filename = parsed_url.path.split(&quot;\/&quot;)\[-1\]/&#39; file_io.py<br>cd ..<br>cd ..<br>pip install -e <br></pre><p>Everything should be installed! Try running the script:</p><pre>(venv) sasha@Sasha:~/.torch/iopath_cache/s/iopath$ python3 /mnt/c/Users/sasha/projects/pdfTest/main.py</pre><h3>Component Detection</h3><p>Most documents we process are financial, structured documents, here is a test document which looks like something we may process:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/672/1*2OgvZ5r1ANxKoCL5qlEOuQ.png" /><figcaption>An example shareholder document.</figcaption></figure><p>I will use the different models which layoutparser has to offer to see how each one works:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HSLE7NhZIkY8mFGlEC9qgw.png" /><figcaption>The models can be found here: <a href="https://layout-parser.readthedocs.io/en/latest/notes/modelzoo.html">https://layout-parser.readthedocs.io/en/latest/notes/modelzoo.html</a></figcaption></figure><p>A brief summary of each model:</p><ul><li><strong>HJDataset </strong>— <a href="https://dell-research-harvard.github.io/HJDataset/">https://dell-research-harvard.github.io/HJDataset/</a>. A huge database of documents in Asian languages.</li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/76a65cb1c8c428ecc73c2a9a06a5f834/href">https://medium.com/media/76a65cb1c8c428ecc73c2a9a06a5f834/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/680/1*tVPkqsDraFV-w6qFBWwBUw.png" /><figcaption>HJDataset 50 Result</figcaption></figure><ul><li><strong>PubLayNet </strong>— <a href="https://github.com/ibm-aur-nlp/PubLayNet">https://github.com/ibm-aur-nlp/PubLayNet</a>. PubLayNet is a large dataset of document images, of which the layout is annotated with both bounding boxes and polygonal segmentations. The source of the documents is <a href="https://www.ncbi.nlm.nih.gov/pmc/tools/openftlist/">PubMed Central Open Access Subset (commercial use collection)</a>.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/680/1*7HyWrXnmWxWoMaDGBc-O6w.png" /><figcaption>PubLayNet Result</figcaption></figure><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/b143290b0fd18eb10807895ceb7b13a5/href">https://medium.com/media/b143290b0fd18eb10807895ceb7b13a5/href</a></iframe><ul><li><strong>Prima — </strong><a href="https://www.primaresearch.org/datasets/Layout_Analysis">https://www.primaresearch.org/datasets/Layout_Analysis</a>. This dataset is designed for testing layout analysis tools, featuring a diverse collection of realistic documents and a user-friendly interface for detailed evaluations and customized searches.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/680/1*PIKewq10cMzRmJ7Y5dzkTg.png" /><figcaption>Prima Result</figcaption></figure><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/8427c2eebf09cc083b6d06cafbaf6b25/href">https://medium.com/media/8427c2eebf09cc083b6d06cafbaf6b25/href</a></iframe><ul><li><strong>TableBank — </strong>TableBank is a new image-based table detection and recognition dataset built with novel weak supervision from Word and Latex documents on the internet, contains 417K high-quality labeled tables.</li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/6ef6c4915b4aeba5cd8d6d19b501f5a7/href">https://medium.com/media/6ef6c4915b4aeba5cd8d6d19b501f5a7/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/680/1*tTAIMe8janwwWgFFVE5AOA.png" /><figcaption>Output from TableBank</figcaption></figure><p>Let’s compare all results side by side:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/680/1*7HyWrXnmWxWoMaDGBc-O6w.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/680/1*tVPkqsDraFV-w6qFBWwBUw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/680/1*PIKewq10cMzRmJ7Y5dzkTg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/680/1*tTAIMe8janwwWgFFVE5AOA.png" /><figcaption>Results from all 4 models compared side by side.</figcaption></figure><p>Looking at the results, Prima performed best for my purpose. I was looking for granular breakdown of the document, as small changes in the structure are significant in fianancial documents. However, TableBank performed well as well for a high level table identification. I will select these 2 models and train them more in the next article of this series.</p><h3>PDFQuery</h3><blockquote>PDFQuery is a light wrapper around pdfminer, lxml and pyquery. It’s designed to reliably extract data from sets of PDFs with as little code as possible.</blockquote><p>We will use PDF query to do the following:</p><ol><li><strong>Convert the PDF to a Structured XML File — </strong>We start by converting the PDF document into a structured XML file. This transformation is beneficial because XML files are easier to upload to databases or manipulate programmatically due to their structured nature.</li><li><strong>Extract Coordinates from the XML File — </strong>Once the PDF is converted into XML, we will extract the coordinates for each type of component documented in the XML file. This involves parsing the XML to identify and retrieve the positional data of each component.</li><li><strong>Visualize Components by Drawing Boxes — </strong>After extracting the coordinates, we will load these details into a Pandas DataFrame. This step facilitates the organization and manipulation of data. Using this structured data, we will programmatically draw boxes around the respective components, visually representing their positions and dimensions as specified in the original PDF.</li></ol><p>The code we use to convert any PDF to a structured</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/484bebc362585dc706277e1aa1ec947b/href">https://medium.com/media/484bebc362585dc706277e1aa1ec947b/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2bQLgiyK-oV_Xg7Mmxd6nQ.png" /><figcaption>Structured XML output.</figcaption></figure><p>We see that the PDF has been converted to a structured file, now let’s extract element co-ordinates from the XML.</p><p>This is the code we use to visualize the boxes around components in the newly generated XML file:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/78cafa8fe7f2b4160d76ad377e3f56ae/href">https://medium.com/media/78cafa8fe7f2b4160d76ad377e3f56ae/href</a></iframe><p>The output of the script above:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/714/1*Rl7T5G5TNe4tGv7ktpAVSg.png" /><figcaption>The annotated file following PDFQuery application.</figcaption></figure><p>This result is highly accurate, we can see that the XML structure has accurately found and labelled components within the PDF file and did it in a very detailed way.</p><h3>Conclusions</h3><p>If you are a Windows user and do not want to spend a lot of time setting up a Linux subsystem — use PDFQuery. It requires a much simpler algorithm to deploy and produces an immediate, structured result. I prefer pdfqQuery over layoutparser, due to the simplicity of use but also due to the comprehensive and accurate results. However, for the final version of this script, I believe that a combination of both would work best.</p><h3>Next Steps</h3><p>The next steps for this project would be:</p><ol><li>To combine the Prima layout and the Table Bank with the pdfQuery to achieve optimal PDF parsing results</li><li>Use OCR and text parsing algorithms to obtain the text from the PDF in a structured way</li><li>Process the text in a logical way to be uploaded to the Excel.</li></ol><p><em>Stay tuned for part 2!</em></p><h3>Bibliography</h3><ol><li><a href="https://www.aquaforest.com/files/searchable%20PDF.pdf">https://www.aquaforest.com/files/searchable%20PDF.pdf</a></li><li><a href="https://pypi.org/project/pdfquery/">https://pypi.org/project/pdfquery/</a></li><li><a href="https://www.duo.uio.no/bitstream/handle/10852/8893/Berg.pdf?sequence=1">https://www.duo.uio.no/bitstream/handle/10852/8893/Berg.pdf?sequence=1</a></li><li><a href="https://github.com/BobLd/DocumentLayoutAnalysis?tab=readme-ov-file">https://github.com/BobLd/DocumentLayoutAnalysis?tab=readme-ov-file</a></li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c6db2073eeee" width="1" height="1" alt=""><hr><p><a href="https://blog.devgenius.io/building-a-high-precision-financial-pdf-extraction-tool-part-1-c6db2073eeee">Building a High Precision Financial PDF Extraction Tool. Part 1.</a> was originally published in <a href="https://blog.devgenius.io/">Dev Genius</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Bauhaus as a Hardware Design Philosophy.]]></title>
            <link>https://medium.com/@sasha.korovkina2003/bauhaus-as-a-hardware-design-philosophy-ca4ff7484315?source=rss-af2804de7fa7------2</link>
            <guid isPermaLink="false">https://medium.com/p/ca4ff7484315</guid>
            <category><![CDATA[design]]></category>
            <category><![CDATA[art]]></category>
            <category><![CDATA[computer-science]]></category>
            <category><![CDATA[pc]]></category>
            <category><![CDATA[hardware]]></category>
            <dc:creator><![CDATA[Sasha Korovkina]]></dc:creator>
            <pubDate>Sun, 17 Mar 2024 23:35:23 GMT</pubDate>
            <atom:updated>2024-03-17T23:35:23.838Z</atom:updated>
            <content:encoded><![CDATA[<h4>Exploring the artistic movements which influence PC design.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/proxy/0*t4swTD29vzTCz8TD" /><figcaption>Photo by <a href="https://unsplash.com/@onurbinay?utm_source=medium&amp;utm_medium=referral">Onur Binay</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h4>Minimalist Builds &amp; Bauhaus Principles.</h4><blockquote><strong>“Form follows function” ideal, which is still popular today, was propagated by Bauhaus founder Walter Gropius. Crucially this philosophy was encouraged by mass production and not restricted by it.</strong></blockquote><p><em>Learn more about Bauhaus here:</em> <a href="https://www.youtube.com/watch?v=Y69wOKg6yp4">https://www.youtube.com/watch?v=Y69wOKg6yp4</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*nnWIg4CJRpzUZBjz.png" /><figcaption>The visual aesthetic of Bauhaus, as seen by: <a href="https://www.dezeen.com/2018/11/01/bauhaus-100-guide-architecture-design/">https://www.dezeen.com/2018/11/01/bauhaus-100-guide-architecture-design/</a></figcaption></figure><p>Minimalist PC builds resonate with the Bauhaus movement’s emphasis on simplicity, functionality, and the elimination of unnecessary details. Just as the Wassily Chair uses industrial materials (tubular steel) to create a functional yet visually striking form, minimalist builds leverage sleek, unadorned cases and components to achieve a clean and efficient aesthetic. Both prioritize the essence of functionality, where every element serves a purpose.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/960/0*YVPuBniQAiSd3bfi.png" /><figcaption>This PC build has inspired me to write this article, it is one of the most beautiful builds out there in my opinion. Check it out: <a href="https://pcpartpicker.com/b/8rcTwP">https://pcpartpicker.com/b/8rcTwP</a></figcaption></figure><p><strong>The core Bauhaus design rules are:</strong></p><ol><li>Function over form: prioritize usability and practicality.</li><li>Simplify shapes to their basic geometric forms.</li><li>Embrace modern materials: steel, glass, and concrete.</li><li>Use primary colors along with black and white.</li><li>Unify art, craft, and technology.</li><li>Design for mass production.</li><li>Eliminate unnecessary details.</li><li>Foster transparency and openness in design.</li><li>Adopt minimalism and clean lines.</li><li>Harmonize the relationship between objects and their environment.</li></ol><h3>Blacked-out Builds &amp; De Stijl</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*sntT60UeSI3XDnOd.png" /><figcaption>A De Stijl build example. Check out this build on PCPARTPICKER here: <a href="https://pcpartpicker.com/b/4DnnTW">https://pcpartpicker.com/b/4DnnTW</a></figcaption></figure><p>The Stealth or Blacked-out PC builds, with their emphasis on monochromatic color schemes and the interplay of forms, share similarities with the De Stijl movement, which focuses on reduction to essentials of form and color. Just as De Stijl artists and architects used primary colors, black, and white to explore the balance between space and form, stealth builds use a monochromatic palette to create a harmonious and unified presence.</p><figure><img alt="" src="https://cdn-images-1.medium.com/proxy/1*b31hiO4ynbDLRrXWEFF4aQ.png" /><figcaption>De Stijl art movement aesthetic as seen by: <a href="https://www.khanacademy.org/humanities/art-1010/cubism-early-abstraction/de-stijl/a/de-stijl-part-iii-the-total-de-stijl-environment">https://www.khanacademy.org/humanities/art-1010/cubism-early-abstraction/de-stijl/a/de-stijl-part-iii-the-total-de-stijl-environment</a></figcaption></figure><p>Here is what De Stijl is in a nutshell:</p><ol><li>Use primary colors: red, blue, and yellow.</li><li>Employ non-colors: black, white, and gray.</li><li>Embrace geometric shapes: squares, rectangles, and straight lines.</li><li>Apply asymmetrical balance.</li><li>Limit details and decoration.</li><li>Focus on horizontal and vertical lines, avoiding diagonals.</li><li>Emphasize flatness; avoid depth or perspective.</li><li>Maintain a clear, open composition.</li><li>Utilize grid-based design.</li><li>Strive for universal harmony in design.</li></ol><h3>RGB Maximalism &amp; Op Art</h3><p><strong>Op Art f</strong>ocuses on creating optical illusions using stark contrasts and vibrant colors, which, while not directly related to the digital RGB spectrum, shares a fascination with visual impact and viewer perception dynamics.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/350/0*d9jPr0ODwOQzDK0a.png" /><figcaption>Op Art Example as seen by: <a href="https://osnatfineart.com/articles/optical-art">https://osnatfineart.com/articles/optical-art</a></figcaption></figure><p>Here are some principles behind Op Art:</p><ol><li>Create visual illusions using precise, mathematical patterns.</li><li>Utilize black and white to emphasize contrast and movement.</li><li>Employ repetitive forms to generate visual vibrations.</li><li>Explore the subjective effects of color combinations.</li><li>Manipulate perspective to challenge the viewer’s perception.</li><li>Use geometric shapes for dynamic compositions.</li><li>Experiment with figure-ground relationships to blur spatial boundaries.</li><li>Integrate optical science principles into artistic designs.</li><li>Aim for viewer interaction through visual stimuli.</li><li>Seek to provoke a physical response, such as eye movement or disorientation.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/564/0*1RnkDePPbqKFQamw.png" /><figcaption>A great example of RGB maximalism from <a href="https://www.pinterest.co.uk/setupedia/">https://www.pinterest.co.uk/setupedia/</a></figcaption></figure><h3>Small Factor Form and Minimalism</h3><p>Emerging in the late 1950s and early 1960s in New York, Minimalism sought to reduce art to its most fundamental features, emphasizing simplicity, monochromatic color schemes, and geometric forms. This movement was a reaction against the subjective emotionalism of Abstract Expressionism, the dominant style of the time.</p><p>A great depiction of minimalist art in it’s earliest stages — Blue (1953), Ad Reinhardt</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/384/0*BbS95f4bOzBpKlBX.png" /><figcaption>A great depiction of minimalist art in it’s earliest stages — Blue (1953), Ad Reinhardt</figcaption></figure><p>The concept of “small factor form” in PC design shares a profound affinity with the principles of Minimalism, an artistic movement that emphasizes simplicity, functionality, and the use of minimal elements to achieve a maximal effect. This connection lies in their mutual commitment to reducing forms to their bare essentials, stripping away the superfluous to focus on what is truly necessary. In the realm of small form factor PCs, this translates to designing computers that are compact, efficient, and stripped of unnecessary components, mirroring Minimalism’s penchant for clean lines, geometric shapes, and avoidance of decorative embellishments.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/900/0*Twc8gq9A_HlXK3O7.png" /><figcaption>A great example of functional minimalist PC design: <a href="https://pcpartpicker.com/b/q2wzK8">https://pcpartpicker.com/b/q2wzK8</a></figcaption></figure><p>Though minimalist design principles can seem similar to Bauhaus and De Stijl, there are a few key differences:</p><ol><li>Prioritize the essence of the object or space, reducing it to its necessary elements.</li><li>Emphasize pure, unadorned forms, distinguishing from De Stijl’s focus on primary colors and grid structures, and Bauhaus’s integration of function and craftsmanship.</li><li>Adopt an extreme reduction in materials and color, aiming for an almost austere simplicity, beyond the functional simplicity of Bauhaus and the geometric abstraction of De Stijl.</li><li>Use industrial materials in their most basic form, showcasing their inherent beauty without the explicit functionalism of Bauhaus.</li><li>Focus on the physical space and the viewer’s experience of the space, rather than on creating a narrative or expressing social ideals.</li><li>Implement a strictly neutral color palette to emphasize form and volume over color theory and symbolism.</li><li>Reject ornamentation entirely, a step beyond Bauhaus’s reduction of decoration in favor of function and De Stijl’s abstract use of elements for balance.</li><li>Encourage reflection and contemplation through the art’s stark simplicity, setting it apart from the more dynamic and functional aims of Bauhaus and the expressive, idealistic goals of De Stijl.</li><li>Present objects and spaces as they are, without attempting to represent or symbolize other elements, differing from De Stijl’s symbolic use of color and form.</li><li>Cultivate anonymity in design, allowing the work to stand without the influence of the creator’s identity or the cultural context, unlike Bauhaus and De Stijl, which both have distinct aesthetic identities tied to broader cultural and philosophical goals.</li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ca4ff7484315" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[A Fast Way to Load Large CSV Files into Microsoft SQL Server]]></title>
            <link>https://medium.com/@sasha.korovkina2003/a-fast-way-to-load-large-csv-files-into-microsoft-sql-server-64f37b5eeab2?source=rss-af2804de7fa7------2</link>
            <guid isPermaLink="false">https://medium.com/p/64f37b5eeab2</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[csv]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Sasha Korovkina]]></dc:creator>
            <pubDate>Sun, 03 Mar 2024 21:28:51 GMT</pubDate>
            <atom:updated>2024-03-03T21:28:51.034Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*bb6eaialtSsC1bdD" /><figcaption>Photo by <a href="https://unsplash.com/@alinnnaaaa?utm_source=medium&amp;utm_medium=referral">Alina Grubnyak</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>So you have a bunch of huge CSV files (perhaps you have received them via an FTP). We are talking hundreds of megabytes (MB) to several gigabytes (GB). The problem? It takes ages.</p><p>The goal of this article is to find the <strong>fastest </strong><em>*proven*</em> way to bulk-load CSV files into Microsoft SQL server. Let’s get into it!</p><h4>Pre-Requisites</h4><p>To work on this, we need to understand the file size conversions. Here they are:</p><ul><li><strong>Kilobyte (KB): </strong>One kilobyte is made up of 1,024 bytes. It’s about the size of a very short piece of text (like a paragraph).</li><li><strong>Megabyte (MB):</strong> One megabyte is made up of 1,024 kilobytes. To give you a practical example, a medium-quality photo from a smartphone might be around 2 to 5 megabytes in size.</li><li><strong>Gigabyte (GB):</strong> One gigabyte is made up of 1,024 megabytes. A gigabyte can store about a few hundred photos (depending on their quality), or it’s roughly enough for a standard-definition movie.</li></ul><h4>Why Are CSVs Important?</h4><p>Transferring CSV files is really important because it helps different computer systems share information, even if they use different types of software or databases.</p><p>For example, imagine one system uses a NoSQL like PayPal’s, while another uses SQL Server. It’s like trying to talk to someone who speaks a completely different language! To solve this problem, we can turn the information from the database into a CSV file, which is a simple way to organize data that most systems can understand. Then, we can easily import that CSV file into the other system. This saves a lot of time and effort compared to creating new software or dealing with complicated ways of connecting the systems together.</p><h4>Now, to the Experiment!</h4><p>Here are the tools we will combine for the faster data loader possible:</p><ol><li>BULK INSERT command</li><li>BULK INSERT command tweaked from SQL</li><li>BULK INSERT command tweaked from C#</li></ol><p><strong>Why Is BULK INSERT The Fastest?</strong></p><p>Microsoft themselves say that it is the best insert options for CSVs with 1000+ rows. Here:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/903/1*JtqHDYTqenqGdo1WZ_9e5A.png" /><figcaption>Microsoft Docs: <a href="https://learn.microsoft.com/en-us/azure/azure-sql/performance-improve-use-batching?view=azuresql">https://learn.microsoft.com/en-us/azure/azure-sql/performance-improve-use-batching?view=azuresql</a></figcaption></figure><p>When you use BULK insert, it processes data in batches, which means it can insert many rows at once instead of one at a time. This reduces the overhead and processing time associated with each individual insert operation, making the overall process much faster. Additionally, BULK insert minimizes logging and locking, further improving performance.</p><p><strong>Experiment — BULK LOAD:</strong></p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/962f42731fd4784249c0b30e19f3dbf8/href">https://medium.com/media/962f42731fd4784249c0b30e19f3dbf8/href</a></iframe><p>This CSV took: <strong>CPU time = 46125 ms, elapsed time = 46773 ms. </strong>With the BULK load in place.</p><h4>BULK Insert and TABLOCK</h4><p>We will now add TABLOCK to the BULK INSERT. Here is what TABLOCK does:</p><ol><li><strong>Acquires Bulk Update (BU) Lock: </strong>When you use the TABLOCK hint in a query, SQL Server acquires a bulk update lock on the entire table affected by the operation.</li><li><strong>Locks Entire Table:</strong> Unlike other locking hints that might lock only specific rows or pages, TABLOCK locks the entire table. This means it prevents any other transactions from reading or modifying any part of the table until the operation is complete.</li><li><strong>Exclusive Access: </strong>TABLOCK ensures exclusive access to the table for the duration of the operation. No other transactions can perform operations on the locked table until the lock is released.</li><li><strong>Prevents Conflicts: </strong>By locking the entire table, TABLOCK helps prevent conflicts and maintain data integrity during bulk data operations, especially in scenarios where concurrent access might lead to inconsistencies or errors.</li><li><strong>Used for Bulk Operations: </strong>TABLOCK is often used in conjunction with bulk data operations, such as bulk inserts, updates, or deletes, where ensuring exclusive access to the entire table is necessary for the operation to succeed efficiently and without conflicts.</li></ol><p><strong>Experiment — BULK LOAD + TABLOCK:</strong></p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/882bdc1e9ae03439ca370f4cda1e5438/href">https://medium.com/media/882bdc1e9ae03439ca370f4cda1e5438/href</a></iframe><p>This CSV took: <strong>CPU time = 19312 ms, elapsed time = 19823ms. </strong>With the BULK load and TABLOCK in place. More than twice as fast!</p><h4>Disabling Triggers</h4><p>Triggers are special types of stored procedures in a database that automatically execute when certain events occur, such as inserting, updating, or deleting data in a table. While triggers can be useful for enforcing business rules or maintaining data integrity, they can also slow down bulk insert operations because they are executed for each row inserted.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/b27d0b83a78c6151fd6935a7eb54ffb9/href">https://medium.com/media/b27d0b83a78c6151fd6935a7eb54ffb9/href</a></iframe><p>This CSV took: <strong>CPU time = 19281 ms, elapsed time = 19809 ms.</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=64f37b5eeab2" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Efficient Development for Small Teams — GitHub x PyCharm. Chapter 1.]]></title>
            <link>https://medium.com/@sasha.korovkina2003/efficient-development-for-small-teams-github-x-pycharm-chapter-1-61f9e0483bb7?source=rss-af2804de7fa7------2</link>
            <guid isPermaLink="false">https://medium.com/p/61f9e0483bb7</guid>
            <category><![CDATA[teamwork]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[team]]></category>
            <category><![CDATA[coding]]></category>
            <category><![CDATA[development]]></category>
            <dc:creator><![CDATA[Sasha Korovkina]]></dc:creator>
            <pubDate>Thu, 15 Feb 2024 00:12:43 GMT</pubDate>
            <atom:updated>2024-03-03T09:53:24.099Z</atom:updated>
            <content:encoded><![CDATA[<h3>Efficient Development for Small Teams — GitHub x PyCharm</h3><p>At CMI2I, our mission is to streamline development, ensuring it’s efficient and time-effective. As a small team of only 2 full-stack developers, optimizing all of our processes is paramount to us.</p><p>I want to share some of our development experience, the challenges we have faced and get feedback from other developers working in small teams.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*tT-8ZwX-9E1jAgVU" /><figcaption>Photo by <a href="https://unsplash.com/@jefflssantos?utm_source=medium&amp;utm_medium=referral">Jefferson Santos</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h4>Our Workflow</h4><p>The workflow I will be focusing on in the next few chapters will be:</p><ol><li>Python (PyCharm)</li><li>Git and GitHub</li></ol><h4>Our Goal</h4><p>Our mission is to deploy new pythonic automations to production as quickly as possible. Our core processes are split up into several clusters:</p><ol><li><strong>Prototyping</strong>: where we quickly throw together an end-to-end process with little optimization, to visualize the end result for ourselves and show it to key stakeholders.</li><li><strong>Improvement</strong>: when we have proved to ourselves and our stakeholders that the prototype automation will work, we begin to optimize our code. This includes: structuring libraries and classes, defining key functions, making basic documentation, generalizing cases.</li><li><strong>Expansion: </strong>where we add more variables, process more files and cover a wider scope of functionality.</li></ol><p>We have quickly realized that working on expansion and improvement simultaneously will result in conflicts, which is why it is better to split. This means that one of us works on expansion, while the other works on improvement and visa versa, depending on preference.</p><h4>Using Git Productively</h4><p>When working in a small team, it can be very easy to over-click that ‘merge’ button without giving it much thought and over-estimate the ease of merging code.</p><p><em>For example — Melinda is developing a new fantastic way to optimize query handling </em><strong><em>(improvement)</em></strong><em>, while Bill is scaling the code to cover all clients and edge-cases </em><strong><em>(expansion)</em></strong><em>. Melinda may think that the best next step is to merge the file into the main branch and </em><strong><em>give Bill some time to figure out</em></strong><em> how to adjust his code accordingly and merge after. All on the main branch.</em></p><p>Melinda might explain her code to Bill and get him to correct a few variables here and there. Right? Well…not always.</p><p>There are a few important rules which we need to remember here:</p><ol><li>Never touch the old function until the new function is implement (and works) in the main branch.</li><li>Correct the <strong>improvement </strong>codebase by variables. In a biology experiment, you would not change the temperature of the room, the species of plant and the chemical you use at the same time. You won’t know where the unexpected growth of your plant came from (was it due to the warmer temperature or due to the chemical?)! The same applies to code. Apply changes on a variable-by-variable basis.</li><li>Communicate! You don’t need to have advanced project management pipelines if it just 1–2 developers, but you do need to make your plans and changes clear to the other person involved.</li></ol><h4><strong>The Ideal Collaboration</strong></h4><p>Continuing with the example of Bill and Melinda, here is the workflow that has worked for us:</p><ol><li>Melinda develops and tests a new querying function on the <em>Melinda branch. </em>Her function is called <strong>awesomeQuery </strong>and the old function is called <strong>deadQuery.</strong></li><li>When Melinda is happy with <strong>awesomeQuery</strong>, and she has unit-tested* it, she commits it to the main branch. Melinda does not touch the <strong>deadQuery.</strong></li><li>Bill now pulls the new changes in the main branch onto <em>Bill branch.</em></li><li>Bill alters and tests all of his variables against the new function. Resolves conflicts. Alters his code. Bill runs the process from his branch and is happy with it.</li><li>Bill commits the new process to the main branch. <strong>awesomeQuery </strong>now runs across all clients.</li></ol><blockquote>*Unit testing: running the smallest testable piece of code in isolation to check if it works</blockquote><p>In the next article, I will go over some practical examples of how this practice can be implemented and some problems which can arise.</p><p><em>Thank you for reading and I’d love to hear your thoughts on how you optimize your development pipelines! </em>❤️</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=61f9e0483bb7" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>