DEV Community: Roopkumar Das

Fast Data Entry: Automating Data Entry for Efficiency

Roopkumar Das — Mon, 17 Feb 2025 09:28:57 +0000

Introduction

Data entry is a crucial but often time-consuming task. Instead of manually entering data into Excel, we can use Python and Pandas to speed up the process significantly. While some basic Python knowledge is required, don't worry—most of it is just plain English reasoning.

In this blog, we'll explore different methods of data entry based on the format of the source data, such as physical documents, images, PDFs, and more.

Methods of Data Entry

There are two main approaches to data entry:

1. Automated Data Entry: Using OCR & Pandas in Python

If the data is in image or scanned document format, we can extract text using OCR (Optical Character Recognition) and process it with Pandas.

Example: Extracting Data from an Image

We'll use the EasyOCR library to extract text from an image file.

import easyocr

# Creating a reader that processes English text
reader = easyocr.Reader(['en'])

# Reading text from the image
result = reader.readtext('small_business_loan.png', detail=0)

for text in result:
    print(text)

Output (Example OCR Result):

Table B: Value of Small Business Loans Outstanding by Depository Lender Size, 2016 to 2020
Billions of Dollars, Nominal
Percentage Change,
Lenders by Total Asset Size
...

As you can see, OCR might introduce errors in the extracted text. Rather than relying entirely on AI to correct it, a manual review and correction step is often necessary. Once corrected, we can save the text in a structured format like a CSV file.

Converting OCR Output to a CSV File

Once we have a cleaned text file, we can structure it into a CSV format for easy import into Excel.

import pandas as pd
from io import StringIO

if __name__ == "__main__":
    with open("output.txt", "r") as f:
        data = f.read()

    data = data.strip()  # Remove trailing newlines
    data_lines = data.split("\n")

    new_text = []
    col_count = 0

    for text in data_lines:
        new_text.append(text)
        if col_count != 6:
            col_count += 1
            new_text.append(",")
        else:
            col_count = 0
            new_text.append("\n")

    csv_content = "".join(new_text)
    data_csv_file = StringIO(csv_content)
    df = pd.read_csv(data_csv_file)
    print(df)

Saving Data to Excel Format

To store this structured data in an Excel file, simply use:

df.to_excel("business_loans.xlsx", sheet_name="Business Loans", index=False)

2. Manual Data Entry: Why CSV is Faster Than Excel

Manual data entry in Excel requires frequent switching between cells using the mouse or arrow keys, which can slow you down. If you're entering data row-wise (e.g., filling out a loan application form), Excel forces you to press Tab or Arrow keys to navigate between columns.

A faster alternative is to enter data into a CSV file using only the keyboard. Commas (,) separate values, making it easier to stay focused without switching keys frequently.

Example: Small Business Loan Data in CSV Format

Lenders by Total Asset Size,2016,2017,2018,2019,2020,Percentage Change (2019-2020)
Less than $100 million,11.5,10.4,10,8.5,7.4,-13.4
$100 million to $499.9 million,96,90.5,86.4,82.2,87.4,6.3
$500 million to $999.9 million,52.8,55.6,54,51.9,70.7,36.1
$1 billion to $9.9 billion,139.5,133,127,129.9,215.4,65.8
$10 million to $49.9 billion,73.5,85.6,106.9,114.5,162,41.5
$50 billion or more,240.5,243.7,248.2,257.4,352.4,36.9

This structured format allows for quick keyboard entry without mouse interaction.

Converting CSV to Excel Using Python

Once the data is entered in CSV format, we can easily convert it into an Excel file using Pandas

import pandas as pd

df = pd.read_csv("small_business_loans.csv")
df.to_excel("small_business_loans.xlsx", index=False)

Conclusion: Choosing the Right Data Entry Method

To speed up data entry, choose the most efficient method based on your data format:

If the data is in an image/PDF → Use OCR + Python for automation.
If the data is already in text format → Convert it directly into CSV.
If manual entry is necessary → Use CSV format instead of Excel to speed up typing.

By using these techniques, you can automate and streamline data entry, saving time and effort while ensuring accuracy. 🚀

How I Did My First Web Scraping and Scraped My Twitter/X Tweet

Roopkumar Das — Sun, 19 Nov 2023 09:14:45 +0000

Hey, everyone! I'm sharing this blog to document my journey of scraping my Twitter/X tweet. Another purpose of mine in writing this is to give another directions for begineer web scrapper about how could they scrape websites.

Now, since I want to scrape tweets, I have two ways to do this:

Using Puppeteer: Visit the tweet URL, scrape the information, and save it somewhere.
Making HTTP Requests to the Backend: Make an HTTP request to the server and retrieve the result.

To be honest, I wasn't inclined to use the first method with Puppeteer. I don't know why, but whenever I thought about scraping via Puppeteer, I didn't want to. It felt like adding unnecessary complexity, and there were more points where I could expose my identity through browser fingerprinting, etc. I thought, why go the other way when I can simply make a request to the server and get the result.

However, I couldn't proceed with the second option either, as I needed to read through network requests, and I was overwhelmed by it. Each time I tried to read those network requests made by the browser, I couldn't focus well and left it after a while.

This was my constant struggle, switching between methods. Yes, the Puppeteer way would be easier, but the feeling was dragging me down, and I procrastinated a lot because of it.

Break

After a few days, I got a huge breakthrough. I suddenly had this thought in mind: why not go to my tweet with my incognito browser?

Then, I could save all the requests my browser makes from the DevTools network tab and, lastly, use Python to search through the entire requests to find mentions of the word "introspecta." This way, I would get a filtered version of the network requests, which I hoped would be smaller and easier for me to go through.

So, I searched on DuckDuckGo about how I could do this and finally learned about HAR (HTTP Archive Record). This is a JSON file that contains all the network request information like headers, cookies, payload, response, etc. This was the perfect thing that could help me achieve the above. Luckily, I didn't have to involve extensions and such since Chrome itself gives an option to save all HAR details in the DevTools network tab. Thus, I saved it and ran this Python script.

import json

with open("./twitter.com.har") as file:
    json_data = json.load(file)
    for entry in json_data["log"]["entries"]:
        try:
            if (
                "Introspecta" in entry["response"]["content"]["text"]
                or "introspecta" in entry["response"]["content"]["text"]
            ):
                data = {}
                data["initiated-by"] = entry["_initiator"]["type"]
                data["request-method"] = entry["request"]["method"]
                data["request-url"] = entry["request"]["url"]
                data["request-headers"] = entry["request"]["headers"]
                data["response-headers"] = entry["response"]["headers"]
                data["response-cookies"] = entry["response"]["cookies"]
                data["payload"] = entry["response"]["content"]["text"]

                json_object = json.dumps(data)
                with open("content.json", "w") as file:
                    file.write(json_object)
        except:
            pass

As you can see, I loaded the JSON data and looped through each entry to find the occurrence of the word "introspecta". I also included a try-except block as some entries don't have those specific fields.

Fortunately, I obtained this result, and luckily, there was just one entry that provided all the information I needed.

{
  "initiated-by": "script",
  "request-method": "GET",
  "request-url": "https://api.twitter.com/graphql/5GOHgZe-8U2j5sVHQzEm9A/TweetResultByRestId?variables=....",
  "request-headers": [
    { "name": ":authority", "value": "api.twitter.com" },
    { "name": ":method", "value": "GET" },
    ....
}

I didn't show the whole result as this would make the blog unnecessarily long, but if you want to check it out, you can find the complete code in my GitHub repository.

Coming back, I noticed that Twitter is employing GraphQL, and by making a request to this URL, the server responds with tweet data. Another thing to note is the headers and cookies we are sending while making this request.

Request Time

I am using the Python requests library to make the entire HTTP request to the server.

First, I have to create a URL for the GET request. From examining the request URL in the HAR result, https://api.twitter.com/graphql/5GOHgZe-8U2j5sVHQzEm9A/TweetResultByRestId is the base, and the rest of it consists of two query parameters: variables and features.

After copying and formatting them, I understood that I only need to change one field of the variable, which is the tweet ID, and the rest isn't that important in relation to my tweet.

tweetUrl = "https://twitter.com/Roopkd_/status/1716456929164411113"
tweet_id = re.findall(r"(?<=status/)\d+", tweetUrl)

variables = {
    "tweetId": tweet_id[0],
    ...
}

features = {...}

finalUrl = f"https://api.twitter.com/graphql/5GOHgZe-8U2j5sVHQzEm9A/TweetResultByRestId?variables={urllib.parse.quote(json.dumps(variables))}&features={urllib.parse.quote(json.dumps(features))}"

Now that I have the URL, I need to think about how to create headers and cookies to send with the GET request, and this was the trickiest part.

I had to find ways to get the bearer token, guest token, and transaction ID.

For the first two, I didn't have to think much as someone had the code to get those values. A shoutout to the twitter_video_dl repo.

What it does is it basically makes a request to the tweet, and the server responds with cookies and the mainjs URL. Upon making another request to mainjs, it gives us the bearer token. Lastly, we make a POST request to client_json, which gives us our guest token.

This also solves our cookies issue as we can just copy the cookie value and use it for our GET request.

def get_tokens(tweet_url):
    html = requests.get(tweet_url)
    cookiesToSend = html.cookies
    ......
    return bearer_token, guest_token, cookies_header, cookiesToSend

For the transaction ID, I thought that Twitter creates it randomly, and I didn't find ways to generate it myself. Thus, I created the following transaction ID by copying the format in the HAR file.

Now, since we have all the values, let's create our header and make the request.

bearer_token, guest_token, header_cookies, cookiesToSend = get_tokens(tweetUrl)

id = "0Gq/4wkkphJSlDg3ZancYmmPJdaSslkjhZuibgL052lLK9zAP/0ru53eP3p7+ICASYsR1dHSh9zw8O9AcA2KtLPfeRFL0Q"

headers = {
    "Host": "api.twitter.com",
    "accept": "*/*",
    ....
}

val = requests.get(finalUrl, headers=headers, cookies=cookiesToSend)

print(val.text)

python3 make-request.py

And the result is, fingercross

{"data":{"tweetResult":{"result":{"__typename":"Tweet","rest_id":"1716456929164411113","core":{"user_results":{"result":{"__typename":"User", ....

Let's go, we got the result! For the final validation, I copied it into a JSON file and formatted it with the help of Prettier.

After examining the result, I noticed that Twitter not only provides information about the tweet but also about the author of the tweet itself, such as the author's followers count, etc. This can be especially helpful when I want both the tweet and author data at once.

But now, after going through it, it feels like the Puppeteer method is something I can see being used more professionally than this, as this method selectively requests a particular URL only. Thus, it stands out compared to the normal requests the browser makes.

Also, this method could have failed if the server tried to validate the request by talking to the client. Nevertheless, I am happy with the results, and I hope that you have learned something as well.

I know this is not the whole story, and there is also proxying involved, but I will leave it for now and will discuss these details later.

Thanks for reading, and I hope you have a great day! Sayonara.

GitHub Repo Link -> tweet-scrape

Level up your JavaScript debugging skills with these console APIs

Roopkumar Das — Mon, 06 Nov 2023 18:28:10 +0000

Hey bro, I know you are very good in debugging your buggy javascript code and you are comfortable using console.log for everything but i want to present some other console api's that may make process easy than just using console.log.

Here, I won't go into that deep into each api but will present some understanding of each that i think would be enough for you to start using it.

`console.count`

console.count mdn docs

console.count(label)

I like this API because it gives the count of how many times that line with console.count is executed. I would appreciate this more than using console.log and counting each console.log log.

For example:

function normalFn(num) {
  console.log(num);
  console.count("Time's Executed");
  // .... other stuff
}

for (let i = 0; i < 6; i++) {
  if (i % 2 === 0) {
    normalFn(i);
  }
}

will result in

0
Time's Executed: 1
2
Time's Executed: 2
4
Time's Executed: 3 -> thus total 3 times that line was executed

I know this example seems trivial, but when checking for how many times the line is executed in big loops, counting each console.log output is non-trivial.

Also, console.countReset(label) resets the count of that label.

`console.error` and `console.warn`

console.error mdn docs
console.warn mdn docs

console.error(msg)
console.warn(msg)

These APIs will be very helpful to you if you want to know where the error happened and why it happened. This is specifically better than using console.log because then you have to write a unique message for each line and then have to remember which message corresponds to which part of the code.

For example:

function divideByNum(num1, num2) {
  if (num2 === 0) {
    console.error("Can't divide a number by zero");
    return;
  }

  return num1/num2;
}

divideByNum(4,0)

This logs a message

Thus you will get to understand why the error happened and also get to know where the error happened with the help of the stack trace below, which tells that this error occurred in the divideByNum function at line 463:3 (For you, it will be your code line and position on that line; here, I am using dev tools console, thus that's why it is showing this).

But if you don't like the red warning sign, then you can use console.warn, which does the same thing as above but prints the message in a yellowish color.

`console.trace`

console.trace mdn docs

console.trace()

console.error and console.warn are awesome, but there is just a little problem; they don't give a stack trace when running JavaScript with Node, Deno, or even Bun. They just print the error message, and thus they won't have any difference with console.log in those environments.

But if you do want to print a stack trace in those environments, then console.trace is just the right tool for you.

For example:

function divideByNum(num1, num2) {
  if (num2 === 0) {
    console.log("Can't divide a number by zero");
    console.trace();
    return;
  }

  return num1 / num2;
}

divideByNum(4, 0);

Will print this result

Can't divide a number by zero
Trace
    at divideByNum (/home/roopkumar/.../console-err.js:4:13)
    at Object.<anonymous> (/home/roopkumar/.../console-err.js:11:1)
    at Module._compile (node:internal/modules/cjs/loader:1256:14)
    at Module._extensions..js (node:internal/modules/cjs/loader:1310:10)
    at Module.load (node:internal/modules/cjs/loader:1119:32)
    at Module._load (node:internal/modules/cjs/loader:960:12)
    at Function.executeUserEntryPoint [as runMain] (node:internal/modules/run_main:81:12)
    at node:internal/main/run_main_module:23:47

`console.assert`

console.assert mdn docs

console.assert(assertion, msg)

This API will only print the message that you give in the second argument when the assertion (which is just a boolean expression) is false.

Here is the little example that I wrote on the Node REPL,

> console.assert(true, "Hey I gave true as first arguement");
undefined
> console.assert(false, "Now it is false");
Assertion failed: Now it is false
undefined

See that it only prints the message when the assertion is false.

One of the ways you can use this API is when you are dealing with boolean expressions,

For example:

const age = 17;
console.assert(age >= 18, "Age must be 18 or older");

// If age is 17, this will log an error message.

Here, I would appreciate this more than creating an if loop for console.log.

These are the 5 console APIs that I use when I try to debug my JavaScript code, and yes, there are more console APIs like console.dir, console.info, console.group, etc. But I feel these don't provide as much value to use as an alternative to console.log.

But hey, if you do find those other APIs useful, then I would seriously appreciate it if you comment on their usage, both for me to know and consider and for other viewers as well.

These are the APIs I find helpful, and I hope that you will also try to adopt them in your debugging sessions. Sayonara!

DEV Community: Roopkumar Das

Fast Data Entry: Automating Data Entry for Efficiency

Introduction

Methods of Data Entry

1. Automated Data Entry: Using OCR & Pandas in Python

Converting OCR Output to a CSV File

Saving Data to Excel Format

2. Manual Data Entry: Why CSV is Faster Than Excel

Converting CSV to Excel Using Python

Conclusion: Choosing the Right Data Entry Method

How I Did My First Web Scraping and Scraped My Twitter/X Tweet

Break

Request Time

Level up your JavaScript debugging skills with these console APIs

console.count

console.error and console.warn

console.trace

console.assert

`console.count`

`console.error` and `console.warn`

`console.trace`

`console.assert`