Python Tutorial 46: Data Converters to convert CSV to SQL, SQL to CSV, Google Sheet to SQL

Data converters help convert data inventory between different formats into an expected format you like to use, such as SQL, CSV, JSON, XML, etc. If you are looking for ways to monetize data by selling contactable data like B2B prospects through an API or a SaaS, I believe this piece can help manage your data inventory in SQL and CSV. 

data converter

Data converters help convert data inventory between different formats into an expected format you like to use, such as SQL, CSV, JSON, XML, etc. If you are looking for ways to monetize data by selling contactable data like B2B prospects through an API or a SaaS. I believe this piece can help manage your data inventory in SQL and CSV. 

So in this Python Tutorial, I will walk you through how you can build a data converter convert CSV or Google Sheet data inventory into SQL. Reversely, it also instructs you how to turn SQL into either CSV or Google Sheet. This tutorial will use Sqlite3, which is typically suitable for developers to build applications. The methodology is shared with SQL.

Python Modules: sqlite3, pandas, CSV, gspread, oauth2client, googleapiclient discovery

Data Converter – CSV to SQL

There are 5 Sqlite3 classes and methods plus csv and pandas used in building a data converter.

Connect() – Creating a Connection to a SQLite Database

The sqlite3 connect() function returns a Connection object that we will use to interact with the SQLite database held in the file. For example, here we create a new DB file name in b2bcontacts.DB

connection = sqlite3.connect("ceInfluencer.db")

Cursor() and Execute() – Create a new data table in the DB file

The sqlite3. Cursor class is an instance using which you can invoke methods that execute SQLite statements, fetch data from the result sets of the queries. You can create a Cursor object using the cursor() method of the Connection object and class.

cursor = connection.cursor()

Then the sqlite3 execute is the command to run a code or a script to execute something you want. Here we need to create a new table named in prospect. Within the prospect table. Also we need to add the column name and categorize the data type, which is either text, integer, float, etc. And there is respectively a “ ‘’’ at the start and the end of the column naming.

cursor.execute('''CREATE TABLE prospect(Category TEXT, Quality_Score TEXT, Country TEXT, B2BLead_Name TEXT, Email Contact TEXT)''')

Plus CSV and Pandas to insert the data to the DB file

Now we need to generate the B2B contact data we already have in a CSV file and transform the format that can feed to SQL in a moment. Here we need to remove the header as above we have created a new header in the DB file which is used in the application. Last, we just need to append and data frame the data using Pandas

B2BData = []

with open("B2Bdata_Inventory.csv","r") as csv_file:

csv_reading = csv.reader(csv_file)

header = next(csv_reading)

if header != None:

for i in csv_reading:

column1 = i[0:]

B2BData.append(column1)

df = pd.DataFrame(influencerData)

For inserting each row of data into the DB file, we need to create a loop with the length of the df data. There are two places we need to notice here. One is the VALUES method where we need to add the question marks. The total amount of them is the same as the number of columns we created above.

for item in range(len(df)):

cursor.execute('''INSERT INTO influencer VALUES(?,?,?,?,?)''', df.iloc[item])

Second, with a list of integers assigned to the item variable, we can insert it to the db file using df.iloc[item]. It means it commands to insert each row of the df data. For example, if the df data has 100 rows, it would loop 100 times. 

Commit () and Close ()

Commit method is to ensure all the changes are saved to the database file, and then we can close the script after it.

data converter

Data Converter – Google Sheet to SQL

Compared to the CSV to SQL, the difference is to grab the Google Sheet first and data frame them using pandas. Rest of the steps are the same as above.

For fetching the Google Sheet data, we can use the gspread module

  • scope = ['https://www.googleapis.com/auth/spreadsheets','https://www.googleapis.com/auth/drive.file','https://www.googleapis.com/auth/drive']
  • creds = ServiceAccountCredentials.from_json_keyfile_name('theGoogleServiceAccountJsonFile.json', scope)
  • client = gspread.authorize(creds)
  • sh = client.open('theSheetName')
  • worksheet = sh.get_worksheet(theTabNumber)
  • vLookupValue = worksheet.get_values(theDataRange')
  • df = pd.DataFrame(vLookupValue)

Data Converter – SQL to CSV converter

Connecting to the db file you want to convert into a CSV file is the 1st step. Then, we need to create a loop to append db file data from each row. Last, we just need to data frame and save to csv file.

connection = sqlite3.connect('googleCompetitor.db')

googleData = []

data = connection.cursor()

for column in data.execute('SELECT * FROM influencer'):

    googleData.append(column)

df = pd.DataFrame(googleData)

print(df)

df.to_csv('competitorTesting.csv', encoding='utf-8-sig')

SQL to Google Sheet converter

Apart from connecting with the Google sheet and outputting the SQL data, we need to upload the converted data to the assigned spreadsheet. Here is the sample if we use gspread.

df = pd.DataFrame(googleData)

value_list = sh.values_update("testing!A1", params={'valueInputOption': 'USER_ENTERED'},body=dict(values=df.T.reset_index().T.values.tolist()))

Full Python Script of Data Converters to convert CSV to SQL, SQL to CSV, SQL to Google Sheet

If you are interested in the full Python script of Python Tutorial 46: Data Converters to convert CSV to SQL, SQL to CSV, Google Sheet to SQL, please subscribe to our newsletter by adding the message “Python Tutorial 46”. We would send you the script immediately to your mailbox.

I hope you enjoy reading Python Tutorial 46: Data Converters to convert CSV to SQL, SQL to CSV, Google Sheet to SQL. If you did, please support us by doing one of the things listed below, because it always helps out our channel.

  • Support my channel through PayPal (paypal.me/Easy2digital)
  • Subscribe to my channel and turn on the notification bell Easy2Digital Youtube channel.
  • Follow and like my page Easy2Digital Facebook page
  • Share the article to your social network with the hashtag #easy2digital
  • Buy products with Easy2Digital 10% OFF Discount code (Easy2DigitalNewBuyers2021)
  • You sign up for our weekly newsletter to receive Easy2Digital latest articles, videos, and discount code on Buyfromlo products and digital software
  • Subscribe to our monthly membership through Patreon to enjoy exclusive benefits (www.patreon.com/louisludigital)