Chapter 46: Data Converters to convert CSV to SQL, SQL to CSV, Google Sheets 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. 

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

Table of Contents: Data Converter

• CSV to SQL converter
• Google Sheets to SQL converter
• SQL to CSV converter
• SQL to Google Sheet converter
• Full Python Script of Data Converter to convert CSV to SQL, SQL to CSV, SQL to Google Sheet
• FAQ

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 an SQLite Database

The sqlite3 connect() the 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 into 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 – 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. The 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 it 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 Chapter 46: Data Converters to convert CSV to SQL, SQL to CSV, Google Sheets to SQL, please subscribe to our newsletter by adding the message “Chapter 46”. We would send you the script immediately to your mailbox.

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

• Support and donate to our 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 on 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 codes
• Subscribe to our monthly membership through Patreon to enjoy exclusive benefits (www.patreon.com/louisludigital)

FAQ:

Q1: What is a Data Converter?

A: A Data Converter is a tool or software that converts data from one format to another, allowing for seamless transfer and compatibility between different systems or platforms.

Q2: Why do I need a Data Converter?

A: You may need a Data Converter if you work with different types of data files or formats and need to convert them for analysis, integration, or compatibility purposes.

Q3: What types of data formats can the Data Converter handle?

A: Our Data Converter is designed to handle a wide range of data formats, including CSV, Excel, XML, JSON, SQL, and more.

Q4: Is the Data Converter easy to use?

A: Yes, our Data Converter is designed with user-friendliness in mind. It offers a simple and intuitive interface that allows users to easily convert their data files with just a few clicks.

Q5: Can the Data Converter handle large data files?

A: Yes, our Data Converter is built to handle large data files efficiently. It is optimized for performance and can handle even the largest datasets without compromising speed or quality.

Q6: Does the Data Converter support batch conversion?

A: Yes, our Data Converter allows for batch conversion, meaning you can convert multiple files at once, saving you time and effort.

Q7: Is the Data Converter compatible with different operating systems?

A: Yes, our Data Converter is compatible with Windows, Mac, and Linux operating systems, ensuring that users can easily access and use it regardless of their preferred platform.

Q8: Can the Data Converter preserve data integrity during conversion?

A: Yes, our Data Converter is designed to preserve data integrity during the conversion process. It ensures that the converted data maintains its accuracy and integrity, minimizing the risk of data loss or corruption.

Q9: Is the Data Converter customizable?

A: Yes, our Data Converter offers customization options, allowing users to specify conversion settings, mapping rules, and other parameters to tailor the conversion process to their specific needs.

Q10: Is there customer support available for the Data Converter?

A: Yes, we provide customer support for our Data Converter. Our team is available to assist you with any questions, issues, or concerns you may have during the conversion process.